MSSQL还原备份相关

创建备份设备 disk | tape | pipe 

EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname = N'CBM100', @physicalname = N'C:\CBM100.bak'

删除备份设备
EXEC master.dbo.sp_dropdevice  N'CBM100'
获取备份设备列表

select * From sys.backup_devices

完整备份，

BACKUP DATABASE [CBM100] TO  [CBM100] WITH NOFORMAT, NOINIT,  
NAME = N'完整备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

差异备份
BACKUP DATABASE [CBM100] TO  [CBM100] WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  
NAME = N'差异备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

如果不删除备份文件，会不断累加备份文件大小

--事务日志备份
BACKUP LOG [CBM100] TO  [CBM100] WITH NOFORMAT, NOINIT,  NAME = N'student-事务日志  备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--还原完整备份

RESTORE DATABASE [CBM100] FROM  [CBM100] WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
--还原到最近状态
RESTORE DATABASE [CBM100] FROM  [CBM100] WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE DATABASE [CBM100] FROM  [CBM100] WITH  FILE = 2,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [CBM100] FROM  [CBM100] WITH  FILE = 3,  NOUNLOAD,  STATS = 10
GO