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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
38200 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
38200 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  
 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.06 seconds. Powered By: Snitz Forums 2000