Differential Backup

Hello,
I need to script three differential backups over a course of one day and
keeping three days worth of backups.
So, nine differential backups altogether.
Here's the script and it works for one differential backup:
BACKUP DATABASE [SomeDB] TO DISK = N'\\SomeServer\Backup\SQL\Diff_Backup\DB_Diff.bak' WITH INIT ,
NOUNLOAD , DIFFERENTIAL , NAME = N'SomeDB backup', NOSKIP , STATS = 10, NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where
database_name='SomeDB'and type!='F' and backup_set_id=(select
max(backup_set_id) from msdb..backupset where database_name='SomeDB')
RESTORE VERIFYONLY FROM DISK = N'\\SomeServer\Backup\SQL\Diff_Backup\DB_Diff.bak' WITH FILE = @.i
This script only works if I need one differential backup. It is always
the same name and the next time it is scheduled ot run, it overwrites
what was there before. So no history.
What I need is a way to timestamp the backup with yyyymmddhhmm. This
way, it will probably not overwrite the previous ones.I still need to
know how to keep ONLY the last 9 differential backups.
Can someone help with the script?
*** Sent via Developersdex http://www.developersdex.com ***Hello Audrey!
In your script, you use WITH INIT which means overwrite the previous one.
Use WITH NOINIT to append.
You may find more information about this from the following link:
http://msdn2.microsoft.com/en-us/library/ms186865.aspx
Ekrem Önsoy
"Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
news:OcdbzKg%23HHA.5328@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I need to script three differential backups over a course of one day and
> keeping three days worth of backups.
> So, nine differential backups altogether.
> Here's the script and it works for one differential backup:
> BACKUP DATABASE [SomeDB] TO DISK => N'\\SomeServer\Backup\SQL\Diff_Backup\DB_Diff.bak' WITH INIT ,
> NOUNLOAD , DIFFERENTIAL , NAME = N'SomeDB backup', NOSKIP , STATS => 10, NOFORMAT DECLARE @.i INT
> select @.i = position from msdb..backupset where
> database_name='SomeDB'and type!='F' and backup_set_id=(select
> max(backup_set_id) from msdb..backupset where database_name='SomeDB')
> RESTORE VERIFYONLY FROM DISK => N'\\SomeServer\Backup\SQL\Diff_Backup\DB_Diff.bak' WITH FILE = @.i
> This script only works if I need one differential backup. It is always
> the same name and the next time it is scheduled ot run, it overwrites
> what was there before. So no history.
> What I need is a way to timestamp the backup with yyyymmddhhmm. This
> way, it will probably not overwrite the previous ones.I still need to
> know how to keep ONLY the last 9 differential backups.
> Can someone help with the script?
>
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Try this one and replace the Backup log statement by differential one:-
declare @.date nvarchar(128)
DECLARE @.FileName VARCHAR(150)
Set @.date=convert(nvarchar(128),getdate(),120)
Set @.date=REPLACE(convert(nvarchar(128),@.date,120),'-','')
Set @.date=REPLACE(convert(nvarchar(128),@.date,120),':','')
Set @.date=REPLACE(convert(nvarchar(128),@.date,120),' ','_')
SET @.FileName='C:\TxnLogbackup_'+@.Date+'.bak'
print @.filename
BACKUP LOG test
TO DISK=@.FileName WITH NOINIT
regarding deletion point plz specify whether its SQL 2k or 2K5
Thx.
Manu
"Audrey Ng" wrote:
> Hello,
> I need to script three differential backups over a course of one day and
> keeping three days worth of backups.
> So, nine differential backups altogether.
> Here's the script and it works for one differential backup:
> BACKUP DATABASE [SomeDB] TO DISK => N'\\SomeServer\Backup\SQL\Diff_Backup\DB_Diff.bak' WITH INIT ,
> NOUNLOAD , DIFFERENTIAL , NAME = N'SomeDB backup', NOSKIP , STATS => 10, NOFORMAT DECLARE @.i INT
> select @.i = position from msdb..backupset where
> database_name='SomeDB'and type!='F' and backup_set_id=(select
> max(backup_set_id) from msdb..backupset where database_name='SomeDB')
> RESTORE VERIFYONLY FROM DISK => N'\\SomeServer\Backup\SQL\Diff_Backup\DB_Diff.bak' WITH FILE = @.i
> This script only works if I need one differential backup. It is always
> the same name and the next time it is scheduled ot run, it overwrites
> what was there before. So no history.
> What I need is a way to timestamp the backup with yyyymmddhhmm. This
> way, it will probably not overwrite the previous ones.I still need to
> know how to keep ONLY the last 9 differential backups.
> Can someone help with the script?
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Some info
http://dimantdatabasesolutions.blogspot.com/2007/04/third-party-software-breaks.html
"Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
news:OcdbzKg%23HHA.5328@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I need to script three differential backups over a course of one day and
> keeping three days worth of backups.
> So, nine differential backups altogether.
> Here's the script and it works for one differential backup:
> BACKUP DATABASE [SomeDB] TO DISK => N'\\SomeServer\Backup\SQL\Diff_Backup\DB_Diff.bak' WITH INIT ,
> NOUNLOAD , DIFFERENTIAL , NAME = N'SomeDB backup', NOSKIP , STATS => 10, NOFORMAT DECLARE @.i INT
> select @.i = position from msdb..backupset where
> database_name='SomeDB'and type!='F' and backup_set_id=(select
> max(backup_set_id) from msdb..backupset where database_name='SomeDB')
> RESTORE VERIFYONLY FROM DISK => N'\\SomeServer\Backup\SQL\Diff_Backup\DB_Diff.bak' WITH FILE = @.i
> This script only works if I need one differential backup. It is always
> the same name and the next time it is scheduled ot run, it overwrites
> what was there before. So no history.
> What I need is a way to timestamp the backup with yyyymmddhhmm. This
> way, it will probably not overwrite the previous ones.I still need to
> know how to keep ONLY the last 9 differential backups.
> Can someone help with the script?
>
>
> *** Sent via Developersdex http://www.developersdex.com ***