Click here to Skip to main content
15,113,295 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

SQL
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
Posted
Updated 7-Jun-10 19:53pm
v2

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
   
Comments
icestatue 10-Jun-10 11:35am
   
Change line
WHERE ( name NOT IN ( 'tempdb') )
to
WHERE ( name NOT IN ( 'tempdb', 'master') )

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900