sql数据库备份(sqlserver备份还原)

在工作中，您需要创建SQL作业来定期备份数据库。现在，脚本记录如下。

1.完整备份:

-- FULLdeclare @filename varchar(1024), @file_dev varchar(300)declare @path varchar(1024)set @path = N'F:\Backup\Plan2\';declare @extension_name varchar(16)set @extension_name = N'bak';set @filename = convert(varchar(1024), getdate(), 120)set @filename = replace(@filename, ':', '')set @filename = replace(@filename, '-', '')set @filename = replace(@filename, ' ', '')set @filename = @filename + '_' + convert (varchar(3), datepart(ms, getdate())) + N'.' + @extension_name-- start backup, COMPRESSION is the parameterset @file_dev = @path + 'SmartDev_Full_' + @filenamebackup database [SmartDev] to disk = @file_dev with noformat, init, name = N'SmartDev-Database full backup', COMPRESSION-- delete the old backup file 1 days agodeclare @olddate datetimeselect @olddate=getdate()-1-- execute deleteselect @pathselect @extension_nameselect @olddateexecute master.dbo.xp_delete_file 0, @path, @extension_name, @olddate, 1go

-full declare @ filename varchar(1024)，@ file _ dev varchar(300)declare @ path varchar(1024)set @ path = N & # 39；f:\ Backup \ plan 2 \ & # 39；；declare @ extension _ name varchar(16)set @ extension _ name = N & # 39；bak & # 39；set @ filename = convert(varchar(1024)，getdate()，120)set @ filename = replace(@ filename，& # 39；:', '')set @filename = replace(@filename，& # 39；-', '')set @filename = replace(@filename，& # 39；', '')set @ filename = @ filename+& # 39；_'+ convert (varchar(3)，datepart(ms，getdate())+N & # 39；。'+ @extension_name -启动备份，压缩是参数set @ file _ dev = @ path+& # 39；SmartDev _ Full _ & # 39+@ filename backup database[smart dev]to disk = @ file _ dev with no format，init，name = N & # 39smart dev-数据库完整备份& # 39；，压缩-删除1天前的旧备份文件declare @olddate datetime select @ old date = getdate()-1-execute delete select @path select @extension_name select @ old date execute master . dbo . XP _ delete _ file 0，@ path，@ extension _ name，@ old date，1go

2.差异备份:

-- Differentialdeclare @filename varchar(1024), @file_dev varchar(300)declare @path varchar(1024)set @path = N'F:\Backup\Plan2\';declare @extension_name varchar(16)set @extension_name = N'bak';set @filename = convert(varchar(1024), getdate(), 120)set @filename = replace(@filename, ':', '')set @filename = replace(@filename, '-', '')set @filename = replace(@filename, ' ', '')set @filename = @filename + '_' + convert (varchar(3), datepart(ms, getdate())) + N'.' + @extension_name-- start backup, COMPRESSION is the parameterset @file_dev = @path + 'SmartDev_Differential_' + @filenamebackup database [SmartDev] to disk = @file_dev with differential, noformat, init, name = N'SmartDev-Database Differential backup'go

-differential declare @ filename varchar(1024)，@ file _ dev varchar(300)declare @ path varchar(1024)set @ path = N & # 39；f:\ Backup \ plan 2 \ & # 39；；declare @ extension _ name varchar(16)set @ extension _ name = N & # 39；bak & # 39；set @ filename = convert(varchar(1024)，getdate()，120)set @ filename = replace(@ filename，& # 39；:', '')set @filename = replace(@filename，& # 39；-', '')set @filename = replace(@filename，& # 39；', '')set @ filename = @ filename+& # 39；_'+ convert (varchar(3)，datepart(ms，getdate())+N & # 39；。'+ @extension_name -启动备份，压缩是参数set @ file _ dev = @ path+& # 39；SmartDev _ Differential _ & # 39+@ filename backup database[smart dev]to disk = @ file _ dev with differential，noformat，init，name = N & # 39smart dev-数据库差异备份& # 39；去

上面两段可以分别创建两个SQL作业。例如，可以将完整备份作业安排在每周日上午运行，成功执行后将删除以前的备份文件(代码中有已删除的语句)。差异备份作业将被安排在周一至周六的每天上午运行。