YogeshDesai
Posting Yak Master
India
136 Posts |
Posted - 11/21/2007 : 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 |
 |
|