Differential backup Sql script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Differential backup Sql script

Author

Topic

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-11-20 : 10:32:45

Hi Guys,
How create differential backup for all user database using transact- sql.
Please help.

SQL IN Minds

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-20 : 12:50:11

Loop through sysdatabases and run the BACKUP DATABASE WITH DIFFERENTIAL command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-11-20 : 12:54:46

Thanks for this but how and i want it just for user databases

SQL IN Minds

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-20 : 13:06:55

Well you can take a look at how I do it or you could just use my code:
http://weblogs.sqlteam.com/tarad/archive/2007/02/26/60120.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-11-21 : 09:17:10

Hi, Thank you very much for the help it was really useful for me,

I am using the following script for diff backup could you help to create log file in specific directory within this code

Set NoCount On

Declare
@vCount Int,
@vNumDBs Int,
@vDBName Varchar(255),
@vBackupPath Varchar(255),
@vFileName Varchar(100),
@vCreateString Varchar(1000),
@vBackupString Varchar(1000),
@vDeleteString Varchar(1000),
@vNewPath Varchar(1000),
@vErrorString Varchar(1000)

Set @vBackupPath = 'c:\backup\'--temp path to test

Declare
@vDBList Table(
DBID INT NOT NULL IDENTITY(1, 1),
DatabaseName Varchar(256)
)
Insert Into @vDBList
Select Name From master.dbo.SysDatabases Where Name Not IN ('TempDB','Master','MSDB','Model')
Set @vNumDBs = @@RowCount
Set @vCount = 1

While @vCount < @vNumDBs
Begin
Select @vDBName = DatabaseName From @vDBList Where DBID = @vCount
Set @vNewpath = @vBackupPath + '\' + @vDBName + '\'
Set @vDeleteString = 'Del "' + @vNewPath + @vDBName + ' ' + Convert(Varchar(12), DateAdd(Day, -3, GetDate()), 104) + '.bak"' + ' /F /Q'
Exec xp_CMDShell @vDeleteString, NO_OUTPUT
Set @vCreateString = 'MD ' + @vNewPath
Exec xp_CMDShell @vCreateString, NO_OUTPUT
Set @vFileName = @vDBName + 'diff ' + Convert(Varchar(12), GetDate(), 104) + '.bak'
Set @vBackupString = 'BACKUP DATABASE [' + @vDBName + '] TO DISK = ''' + @vNewPath + @vFileName + ''' WITH NOINIT , NOUNLOAD ,DIFFERENTIAL, NAME = N''' + @vDBName + ''', NOSKIP , STATS = 10, NOFORMAT'
Exec (@vBackupString)
If @@Error <> 0
Begin
Set @vCount = @vNumDBs
End
Set @vCount = @vCount + 1
End

SQL IN Minds

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources