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'
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