September 7, 2010 at 5:14 am
I'm using the following script to perform a full daily backup but I need the previous day Backup to be overwritten but it's not working. What would I need to do to make this happen please?
declare @CheckDate [nvarchar](50), @Title [nvarchar](100)
set @CheckDate = replace(replace(convert(nvarchar(50),getdate(),120),':',''), ' ', '_')
set @Title = N'E:\'Datbasename'_Full_'+@CheckDate+''+'.bak'
BACKUP DATABASE 'Datbasename'
TO DISK = @Title
WITH NOFORMAT,INIT, NAME = N''Datbasename'-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,STATS = 10;
Thank you
September 7, 2010 at 5:22 am
i think with this statement, everytime, you will get new value
set @CheckDate = replace(replace(convert(nvarchar(50),getdate(),120),':',''), ' ', '_')
----------
Ashish
September 7, 2010 at 8:01 am
This doesn't directly answer your question, but, you could use a Maintenance Plan to control your full and transaction log backups and then use the cleanup task to easily control how many days of backups you retain.
September 7, 2010 at 11:05 am
As replied earlier by Ashish, your code does not overwrite the existing backups. Modify it to something like this
BACKUP DATABASE 'Datbasename'
TO DISK = 'EnterAStaticName'
WITH NOFORMAT,INIT, NAME = N''Datbasename'-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,STATS = 10
Also maintenance plans are the easiest way to meet the backup requirements
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
September 7, 2010 at 1:23 pm
Also, are you sure you want to overwrite the previous day's backup ? I like to have several days backups available in case I discover a problem from a few days ago and need to recover some data.
Maybe you've already copied them to tape before overwriting so you can recover ?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply