sql server 文件备份恢复

数据库介绍



文件组 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]
GO



USE [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
GO


BACKUP 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
GO
 

BACKUP 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
GO



RESTORE LOG [lys] FROM  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\log02.trn' 
WITH  FILE = 1,  
NOUNLOAD, NORECOVERY,
STATS = 10
GO


RESTORE 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