sqlserver用SQL脚本进行备份和还原操作

--1.1备份数据库脚本
USE [master]
GO
BACKUP DATABASE [Test] TO DISK = 'D:\\Test\\Test_20230419.bak'
GO

--1.2还原数据库,注意一定要用NORECOVERY还原备份
USE [master]
GO
RESTORE DATABASE [Test] FROM DISK=N'D:\\Test\\Test_20230419.bak' WITH FILE =1,
MOVE N'Test' TO N'D:\\Test\\Test.mdf',
MOVE N'Test_log' TO N'D:\\Test\\Test_log.ldf',
NOUNLOAD,NORECOVERY,REPLACE,STATS=5
GO

--2.1对数据库进行差异备份,注意：创建差异备份一定要先作完整备份
USE [master]
GO
BACKUP DATABASE [Test] TO DISK = 'D:\\Test\\Test_2023041901.bak'
WITH DIFFERENTIAL
GO

--2.2用差异备份还原数据库
USE [master]
GO
RESTORE DATABASE [Test] FROM DISK = 'D:\\Test\\Test_20230419.bak' WITH NORECOVERY
GO
RESTORE DATABASE [Test] FROM DISK = 'D:\\Test\\Test_2023041901.bak' WITH RECOVERY
GO

--3.1数据库的事务日志备份
USE [master]
GO
BACKUP LOG [Test] TO DISK = 'D:\\Test\\Test_20230419.trn'
GO

--4.1批量备份数据库
DECLARE @name VARCHAR(50) -- 数据库名
DECLARE @path VARCHAR(256) -- 备份文件路径
DECLARE @fileName VARCHAR(256) -- 备份文件名
DECLARE @fileDate VARCHAR(20) -- 用于文件名

--指定数据库备份目录 eg 'D:\\backup\\'
SET @path = 'D:\\Test'

-- 指定文件名格式
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT (VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB') -- 排除这些数据库
AND state = 0 -- 数据库在线
AND is_in_standby = 0 -- 数据库不是只读日志传送

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor