SQL Server备份还原脚本

--完整备份

USE [master]

BACKUP DATABASE [Test] TO  DISK = N'D:\DB_Bak_Test\test.bak' WITH NOFORMAT, NOINIT,  NAME = N'Test-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

GO

--完整还原：

USE [master]

RESTORE DATABASE [Test] FILE = N'Test' FROM  DISK = N'D:\BAKFile\test.bak' WITH  FILE = 1,  MOVE N'Test' TO N'D:\DBFile\Test.mdf',  MOVE N'Test_log' TO N'D:\DBFile\Test_0.ldf',  NOUNLOAD,  REPLACE,  STATS = 10

GO

--差异备份

USE [master]

BACKUP DATABASE [Test] TO  DISK = N'D:\DB_Bak_Test\test.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'Test-差异 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

--差异还原

USE [master]

BACKUP LOG [Test] TO  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\Test_LogBackup_2020-05-18_17-31-33.bak' WITH NOFORMAT, NOINIT,  NAME = N'Test_LogBackup_2020-05-18_17-31-33', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5

RESTORE DATABASE [Test] FROM  DISK = N'D:\BAKFile\test.bak' WITH  FILE = 1,  MOVE N'Test' TO N'D:\DBFile\Test.mdf',  MOVE N'Test_log' TO N'D:\DBFile\Test_0.ldf',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5

GO

USE [master]

RESTORE DATABASE [Test] FILE = N'Test' FROM  DISK = N'D:\BAKFile\test_cy.bak' WITH  FILE = 1,  MOVE N'Test' TO N'D:\DBFile\Test.mdf',  MOVE N'Test_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Test.ldf',  NOUNLOAD,  REPLACE,  STATS = 10

GO