（2.15）备份与还原--使用作业备份、清理过期备份、清理历史记录、事务日志是否备份过

一、建立作业备份数据库

　　打开SQL SERVER MANAGEMENT STUDIO，启动SQL SERVER代理服务（注意在“控制面板-管理工具-服务”中设置SQL SERVER AGENT的启动类型为自动）。启动后点击“作业-新建作业”，弹出一个作业属性的窗口，在“常规”栏目里可以先给作业命名，假设为“备份数据”。 一、备份数据的第一个步骤--“备份当日数据” 
　　在“步骤”栏目里新建一个步骤名为“备份当日数据”，类型为“T-SQL”，数据库选择你要操作的数据库（假设为“TESTDB”），命令窗口里填入备份的SQL语句。假设备份数据放在“D:\BACKUP”里，备份文件命名格式为“SQL-2009-3-26.BAK”，那么语句如下：

DECLARE @filename VARCHAR(255)
DECLARE @date DATETIME
SELECT @date=GETDATE()
SELECT @filename = 'D:\BACKUP\SQL-'+CAST(DATEPART(yyyy,@date) as varchar)+'-'+CAST(DATEPART(mm,@date) as varchar)+'-'+CAST(DATEPART(dd,@date) as varchar)+'.bak'
BACKUP DATABASE [TESTDB] TO  DISK = @filename WITH INIT
GO

在步骤属性的高级的“成功时要执行的操作”选择“转到下一步”即可，这样“备份当日数据”的步骤已经建立好。

二、备份数据的第二个步骤--“删除旧有备份”　　

我们可以设置只保留5天内的备份数据，那么必须删除5天前的数据备份文件。在“备份数据”作业属性窗口的步骤栏目里，建立第二个步骤命名为“删除旧有备份”。同样类型为“T-SQL”，命令窗口里填入一下SQL语句：

DECLARE @OLDDATE DATETIME
SELECT @OLDDATE=GETDATE()-5
EXECUTE master.dbo.xp_delete_file 0,N'D:\BACKUP',N'bak',@olddate,1

此命令会删除“D:\BACKUP”里5天前的.BAK或.TRN格式的文件，不用指定文件名是什么。因为SQL SERVER的备份文件里包含了时间属性在里面。在步骤属性的高级的“成功时要执行的操作”选择“退出报告成功的作业”即可，这样第二个步骤已经建立好。

三、备份数据作业属性的计划栏目里，设置作业的执行时间

新建一个作业计划，命名为“每日自动备份和删除”，再选择执行的周期，例如每天凌晨1点开始执行。
最后保存整个“备份数据”的作业，每日就会自动备份数据库和删除旧有数据了。

 

四、清理历史记录

1. 删除旧的历史记录　　

每次执行备份或还原操作后会向备份和还原历史记录表添加额外的行。因此，如果实例中的数据库备份非常频繁，建议定期执行 sp_delete_backuphistory，通过删除早于指定日期的备份集条目，减小备份和还原历史记录表的大小。

USE msdb;
GO
EXEC sp_delete_backuphistory @oldest_date = '2013-06-30';

2. 删除特定数据库的所有历史记录

sp_delete_database_backuphistory 将从备份和还原历史记录表中删除有关指定数据库的所有历史记录。
USE msdb;
GO
EXEC sp_delete_database_backuphistory @database_name = 'db01';

 五、查看事务日志文件是否做过备份

如果发现数据库db01的事务日志文件（LDF文件）变得非常大，那么首先运行以下语句，检查db01数据库是否做过事务日志备份。
select * from msdb.dbo.backupset where database_name='db01' and type='L'

 

六. 数据库备份还原历史记录

备份还原的记录都在msdb里。

1. 备份记录

SELECT 
     bs.backup_set_id,
     bs.database_name,
     bs.backup_start_date,
     bs.backup_finish_date,
     CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],
     CAST(DATEDIFF(second, bs.backup_start_date,
     bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],
     CASE bs.[type]
         WHEN 'D' THEN 'Full Backup'
         WHEN 'I' THEN 'Differential Backup'
         WHEN 'L' THEN 'TLog Backup'
         WHEN 'F' THEN 'File or filegroup'
         WHEN 'G' THEN 'Differential file'
         WHEN 'P' THEN 'Partial'
         WHEN 'Q' THEN 'Differential Partial'
     END AS BackupType,
     bmf.physical_device_name,
     CAST(bs.first_lsn AS VARCHAR(50)) AS first_lsn,
     CAST(bs.last_lsn AS VARCHAR(50)) AS last_lsn,
     bs.server_name,
     bs.recovery_model
 FROM msdb.dbo.backupset bs
 INNER JOIN msdb.dbo.backupmediafamily bmf 
 ON bs.media_set_id = bmf.media_set_id
 ORDER BY bs.server_name,bs.database_name,bs.backup_start_date;
GO

如果server_name是本机，那么备份是在本机生成的；

如果server_name是别的主机名，那么备份是被拿到本机做过数据库还原；

 

2. 还原纪录

SELECT 
     rs.[restore_history_id],
     rs.[restore_date],
     rs.[destination_database_name],
     bmf.physical_device_name,
     rs.[user_name],
     rs.[backup_set_id],
     CASE rs.[restore_type]
         WHEN 'D' THEN 'Database'
         WHEN 'I' THEN 'Differential'
         WHEN 'L' THEN 'Log'
         WHEN 'F' THEN 'File'
         WHEN 'G' THEN 'Filegroup'
         WHEN 'V' THEN 'Verifyonly'
     END AS RestoreType,
     rs.[replace],
     rs.[recovery],
     rs.[restart],
     rs.[stop_at],
     rs.[device_count],
     rs.[stop_at_mark_name],
     rs.[stop_before]
FROM [msdb].[dbo].[restorehistory] rs
INNER JOIN [msdb].[dbo].[backupset] bs
--on rs.backup_set_id = bs.media_set_id
ON rs.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf 
ON bs.media_set_id = bmf.media_set_id
GO

还原数据库的时候是会写backupset和backupmediafamily系统表的，用来记录还原所用到的备份文件信息。