SQLServer备份和还原脚本

SQL备份脚本

DECLARE @CurrentTime VARCHAR(50), @FileName VARCHAR(200), @DatabaseName VARCHAR(100)
SET @CurrentTime = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120 ),'-','_'),' ','_'),':','')
SET @DatabaseName = 'Prod_MF'

        

SET @FileName = 'C:\DB\backup\'+@DatabaseName+'_Db_' + @CurrentTime+'.bak'
BACKUP DATABASE @DatabaseName
TO DISK=@FileName WITH FORMAT ,COMPRESSION



SET @FileName = 'C:\DB\backup\'+@DatabaseName+'_Log_' + @CurrentTime+'.bak'
BACKUP log @DatabaseName
TO DISK=@FileName WITH FORMAT ,COMPRESSION

　　

SQL还原脚本

USE [master]
RESTORE DATABASE [Prod_MF] FROM  DISK = N'\\WIN-C65JQPR6T2H\backup\Prod_MF_Db_2022_09_16_134250.bak' WITH  FILE = 1, 
MOVE N'Prod_MF' TO N'C:\DB\Prod_MF.mdf', 
MOVE N'Prod_MF_log' TO N'C:\DB\Prod_MF_log.ldf', 
NOUNLOAD,NORECOVERY,  REPLACE,  STATS = 5

GO


--注意一定要用NORECOVERY来还原备份
USE [master]
RESTORE DATABASE [Prod_MF] FROM  DISK = N'\\WIN-C65JQPR6T2H\backup\Prod_MF_Log_2022_09_16_134250.bak' WITH  FILE = 1, 
NOUNLOAD,NORECOVERY,  REPLACE,  STATS = 5

GO