SQL 数据库备份及管理

一、查询有那些数据库

SELECT name
FROM sys.databases WHERE database_id >4

 

二、拼凑数据库备份SQL

2.1直接上SQL

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL,'') + '
BACKUP DATABASE '+ QUOTENAME(name,'[]') 
+ ' TO DISK = ''E:\DBBackup\'+ name + '_' + CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR) + '.bak' 
+ ''' WITH NOINIT, NOUNLOAD, NAME = N''' + name + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
FROM sys.databases WHERE database_id >4 AND name like '%%' AND state =0

PRINT(@SQL)
EXECUTE(@SQL)

 

2.2拼凑列子

BACKUP DATABASE [Test] TO DISK = 'E:\DBBackup\Test_20150403116.bak' WITH NOINIT, NOUNLOAD, NAME = N'Test_backup', NOSKIP, STATS = 10, NOFORMAT

BACKUP DATABASE [RISDB] TO DISK = 'E:\DBBackup\RISDB_20150403116.bak' WITH NOINIT, NOUNLOAD, NAME = N'RISDB_backup', NOSKIP, STATS = 10, NOFORMAT

 

三、查询备份历史

SELECT
   CONVERT(CHAR(100),SERVERPROPERTY('Servername'))ASServer,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
   END ASbackup_type,
   msdb.dbo.backupset.backup_size,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name,  
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id=msdb.dbo.backupset.media_set_id
ORDER BY   msdb.dbo.backupset.database_name,  msdb.dbo.backupset.backup_finish_date

结果：

 

 

四、查询数据库还原历史

select bus.server_name as 'server',rh.restore_date,bus.database_name as 'database',
CAST(bus.first_lsn AS VARCHAR(50))as LSN_First,
CAST(bus.last_lsn AS VARCHAR(50))as LSN_Last,
CASE rh.[restore_type]
WHEN 'D'THEN'Database'
WHEN 'F'THEN'File'
WHEN 'G'THEN'Filegroup'
WHEN 'I'THEN'Differential'
WHEN 'L'THEN'Log'
WHEN 'V'THEN'Verifyonly'
END AS rhType
FROM msdb.dbo.backupset bus
INNER JOIN msdb.dbo.restorehistory rh ON rh.backup_set_id=bus.backup_set_id

结果：