差异备份 [英] differential backup

问题描述

如何使用脚本在SQL中创建差异备份?我正在使用以下脚本，但未执行差异备份.
我需要备份服务器中的所有数据库，但是如果我在sql server的主数据库中的新查询中运行此脚本，则会出现错误:

Hi,
How to create differential backup in SQL using script? I am using the below script but it is not doing a differential backup.
I need to backup all database in the server but if I run this script in new query in master database in sql server, it gives an error:

Msg 3024, Level 16, State 0, Line 51<br />
You can only perform a full backup of the master database. Use BACKUP DATABASE to back up the entire master database.<br />
Msg 3013, Level 16, State 1, Line 51<br />
BACKUP DATABASE is terminating abnormally.

请帮帮我.

please help me.

DECLARE @CFG_BACKUP_PATH NVARCHAR(256)
DECLARE @CFG_DAYS_DELETE INT
SET @CFG_BACKUP_PATH = 'E:\BackUp_diffrentioal\step_2'
SET @CFG_DAYS_DELETE = 30
DECLARE @Today DATETIME
DECLARE @TodayName CHAR(14)
DECLARE @TodayDir CHAR(8)
SET @Today = GETDATE()
SET @TodayName = CONVERT( CHAR(8), @Today,  112)+ '-'+replace(CONVERT( CHAR(8), @Today,  108),':','')
SET @TodayDir = CONVERT( CHAR(8), @Today,  112)
DECLARE @id INT
DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(256)
DECLARE @cmd VARCHAR(256)
DECLARE @TempDir VARCHAR(256)
SET @TempDir = @CFG_BACKUP_PATH + '\'+ CONVERT(VARCHAR(256), NEWID())
SET @cmd = 'md ' + @TempDir
EXEC xp_cmdshell @cmd, no_output
SET @cmd = 'md ' + @TodayDir
EXEC xp_cmdshell @cmd, no_output
DECLARE @dbList TABLE
    (
      dbno INT IDENTITY,
      dbname NVARCHAR(256)
    )
INSERT  INTO @dbList ( dbname )
        SELECT  name
        FROM    master.dbo.sysdatabases
        WHERE   ( name NOT IN ( 'tempdb' ) )
                AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'

------ Starting backup, one by one
SELECT  @id = dbno,
        @name = dbname
FROM    @dbList
WHERE   dbno = 1
WHILE @@ROWCOUNT = 1
    BEGIN
        PRINT N'++ Backup: ' + @name
        SET @path = @TempDir + '\' + @name + '.bak'
        BACKUP DATABASE @name TO DISK = @path WITH  DIFFERENTIAL
       

        SELECT  @id = dbno,
                @name = dbname
        FROM    @dbList
        WHERE   dbno = @id + 1
    END

SET @cmd = 'rd /s /q ' + @TempDir
EXEC xp_cmdshell @cmd, no_output

推荐答案

您不能在主数据库上执行差异备份，只能进行完整备份，故事结束.

You can''t do a differential backup on the master Database, only full back up, end of story.

嗨 如果我想通过脚本对服务器中的所有数据库进行差异备份，该怎么办
呢?
如果有人对此脚本进行了更正.....请帮助我....非常感谢

hi if i want do differetial backup all databese in server by script what i
do?
if any one correction this script.....please help me....thanks alot

这篇关于差异备份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！