sql-server – 如何在sql server中创建具有唯一名称的每日备份

发布时间：2020-12-12 16:37:51 所属栏目：MsSql教程来源：网络整理

导读：我想要使??用唯一的名称,每天对服务器的所有数据库进行完整的数据库备份.为此我有一个想法来保留时间戳,这将使数据库副本分开. 假设服务器上有一个名为ABCD的数据库,那么它应该被备份如下： ABCD_21_03_2013ABCD_22_03_2013 我该怎么做我不太了解这些类型的SQ

我想要使??用唯一的名称,每天对服务器的所有数据库进行完整的数据库备份.为此我有一个想法来保留时间戳,这将使数据库副本分开.
假设服务器上有一个名为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:BACKUPABCD_24_05_2013_AdventureWorks2008R2.bak'
    WITH FORMAT,COMPRESSION,INIT,STATS = 15;

BACKUP DATABASE [AdventureWorks2008R2_Live]
    TO DISK = N'D:BACKUPABCD_24_05_2013_AdventureWorks2008R2_Live.bak'
    WITH FORMAT,STATS = 15;

BACKUP DATABASE [AdventureWorks2012]
    TO DISK = N'D:BACKUPABCD_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).

.....

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!