SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Differential backup Sql script
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

YogeshDesai
Posting Yak Master

India
136 Posts

Posted - 11/20/2007 :  10:32:45  Show Profile  Send YogeshDesai a Yahoo! Message  Reply with Quote
Hi Guys,
How create differential backup for all user database using transact- sql.
Please help.

SQL IN Minds

tkizer
Almighty SQL Goddess

USA
36636 Posts

Posted - 11/20/2007 :  12:50:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

YogeshDesai
Posting Yak Master

India
136 Posts

Posted - 11/20/2007 :  12:54:46  Show Profile  Send YogeshDesai a Yahoo! Message  Reply with Quote
Thanks for this but how and i want it just for user databases

SQL IN Minds
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36636 Posts

Posted - 11/20/2007 :  13:06:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

YogeshDesai
Posting Yak Master

India
136 Posts

Posted - 11/21/2007 :  09:17:10  Show Profile  Send YogeshDesai a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000