我想要使用唯一的名称,每天对服务器的所有数据库进行完整的数据库备份.为此我有一个想法来保留时间戳,这将使数据库副本分开.
假设服务器上有一个名为ABCD的数据库,那么它应该被备份如下：
假设服务器上有一个名为ABCD的数据库,那么它应该被备份如下：
ABCD_21_03_2013 ABCD_22_03_2013
我该怎么做我不太了解这些类型的sql Backup JOBS.
解决方法
自动备份服务器上的所有数据库.
关于工作：
http://msdn.microsoft.com/en-us/library/ms190268.aspx
查询：
SET NOCOUNT ON;
DECLARE
@FileName NVARCHAR(1024),@DBName NVARCHAR(256),@PathName NVARCHAR(256),@Message NVARCHAR(2048),@IsCompressed BIT
SELECT
@PathName = 'D:\BACKUP\',@IsCompressed = 1
DECLARE db CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
SELECT
sd.name,file_path = @PathName + FileDate + '_' + name + '.bak'
FROM sys.databases sd
CROSS JOIN (
SELECT FileDate = 'ABCD_' + REPLACE(CONVERT(VARCHAR(10),GETDATE(),103),'/','_')
) fd
WHERE sd.state_desc != 'OFFLINE'
AND sd.name NOT IN ('master','model','msdb','tempdb')
ORDER BY sd.name
OPEN db
FETCH NEXT FROM db INTO
@DBName,@FileName
WHILE @@FETCH_STATUS = 0 BEGIN
DECLARE @sql NVARCHAR(MAX)
SELECT @Message = REPLICATE('-',80) + CHAR(13) + CONVERT(VARCHAR(20),120) + N': ' + @DBName
RAISERROR (@Message,1) WITH NOWAIT
SELECT @sql =
'BACKUP DATABASE [' + @DBName + ']
TO DISK = N''' + @FileName + '''
WITH FORMAT,' + CASE WHEN @IsCompressed = 1 THEN N'COMPRESSION,' ELSE '' END + N'INIT,STATS = 15;'
EXEC sys.sp_executesql @sql
FETCH NEXT FROM db INTO
@DBName,@FileName
END
CLOSE db
DEALLOCATE db
输出：
BACKUP DATABASE [AdventureWorks2008R2]
TO DISK = N'D:\BACKUP\ABCD_24_05_2013_AdventureWorks2008R2.bak'
WITH FORMAT,COMPRESSION,INIT,STATS = 15;
BACKUP DATABASE [AdventureWorks2008R2_Live]
TO DISK = N'D:\BACKUP\ABCD_24_05_2013_AdventureWorks2008R2_Live.bak'
WITH FORMAT,STATS = 15;
BACKUP DATABASE [AdventureWorks2012]
TO DISK = N'D:\BACKUP\ABCD_24_05_2013_AdventureWorks2012.bak'
WITH FORMAT,STATS = 15;
结果：
2013-05-24 09:54:34: AdventureWorks2008R2 15 percent processed. 30 percent processed. 45 percent processed. 60 percent processed. 75 percent processed. 90 percent processed. Processed 23416 pages for database 'AdventureWorks2008R2',file 'AdventureWorks2008R2_Data' on file 1. Processed 1 pages for database 'AdventureWorks2008R2',file 'AdventureWorks2008R2_Log' on file 1. BACKUP DATABASE successfully processed 23417 pages in 4.052 seconds (45.148 MB/sec). .....
