SQLServer备份、还原

1.新建存储过程备份数据库

CREATE PROCEDURE DatabaseBackup
AS
BEGIN
DECLARE @BackupFileName NVARCHAR(255)
SET @BackupFileName = 'D:\data_bak\DatabaseBackup_' + CONVERT(nvarchar, GETDATE(), 112) + '.bak'--设置备份文件路径和名称

BACKUP DATABASE ecology2 -- 替换为你的数据库名称
TO DISK = @BackupFileName
WITH FORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
END
GO

drop PROCEDURE DatabaseBackup

exec DatabaseBackup --执行存储过程调试

2.新建作业调用存储过程执行定定时备份

 

3.选中数据库-任务-还原操作应用备份bak文件进行还原