当前位置：首页 > news >正文

sql server 文件备份恢复

news 2025/7/22 20:29:44

数据库介绍文件组 PRIMARY 文件
lys	D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\lys.mdf
lys_02	D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\lys_02.ndf文件组 sec 有2个表（sec_1,sec_2）	文件 
lys_sec	D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\lys_sec.ndf一，  创建文件组USE [master]
GO
ALTER DATABASE [lys] ADD FILEGROUP [sec]
GOUSE [master]
GO
ALTER DATABASE [lys] ADD FILE 
( NAME = N'lys_sec', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\lys_sec.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
TO FILEGROUP [sec]
GO使sec为默认文件组USE [lys]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'sec') ALTER DATABASE [lys] MODIFY FILEGROUP [sec] DEFAULT
GO创建表，文件组在sec上create table sec_01 ( a int);
create table sec_02( a  int);insert into sec_01 select 1;二 备份---在做文件组之前做 事务日志的作用  ，生成检查点，和其他文件组保持一致等。。。恢复时候并不会使用到这个日志文件
备份事务日志
BACKUP LOG [lys] TO 
DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\log01.trn'
WITH NOFORMAT, NOINIT,  
NAME = N'lys-log01', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GOBACKUP DATABASE [lys] 
FILEGROUP = N'sec' TO  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\file_sec.bak'
WITH NOFORMAT, NOINIT,  
NAME = N'lys-secfile', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GOBACKUP LOG [lys] TO 
DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\log02.trn'
WITH NOFORMAT, NOINIT,  
NAME = N'lys-log02', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO----备份，并且备份之后是数据库处于restoring 状态 （通过增加关键字 NORECOVERY）
use master
go
BACKUP LOG [lys] TO 
DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\tail.trn'
WITH NOFORMAT, NOINIT, NORECOVERY, 
NAME = N'lys-tail', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO三 恢复联机恢复RESTORE DATABASE [lys] 
FILE = N'lys_sec' FROM  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\file_sec.bak'
WITH  FILE = 1, 
NORECOVERY,  
NOUNLOAD,  
REPLACE,  
STATS = 10
GORESTORE LOG [lys] FROM  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\log02.trn' 
WITH  FILE = 1,  
NOUNLOAD, NORECOVERY,
STATS = 10
GORESTORE LOG [lys] FROM  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\tail.trn' 
WITH  FILE = 1,  
NOUNLOAD, RECOVERY,
STATS = 10
GO

文件或文件组恢复的时候，从备份文件之后的事务日志开始恢复，最后恢复截断的事务日志备份，使用关键字 recover，使数据库可以打开的状态

在文件组备份之后的操作，都会记录到事务日志文件中，recover 事务日志，就是恢复最新的数据到数据库。

参考：

文件还原（完整恢复模式） - SQL Server | Microsoft Learn

查看全文

http://www.lryc.cn/news/479595.html