Backup History

Last Post 09 Dec 2010 09:33 AM by gunneyk. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

09 Dec 2010 06:48 AM
Hi Masters,

I have created a job that makes backups.

Code sample:

declare @date as varchar(25)
Declare @Nome as varchar(50)
SELECT name from sys.databases where state =0
order by name
set @date = convert(varchar,getdate(),120)
set @date = replace(replace(@date,'-',''),':','')
OPEN backups

FETCH NEXT FROM backups INTO @nome


exec('backup database '+@nome+' to disk =''c:\database_backups\'+@nome+'\'+@nome+'_'+@date+'.BAK''')

FETCH NEXT FROM backups INTO @nome

CLOSE backups

The backups that are created by this job are named as:

e.g: SGI_20101209 111200.BAK

Now, i would like to add to the name of the backup, the number of backup.

If the backup is the 3 backup of this database, i would like that the name become like this:

SGI_3_20101209 111200.BAK

Where can i get the information of the number of backup already made to this database?

Thank you
New Member
New Member

09 Dec 2010 08:47 AM
Don't think sql keeps this info, you may need record it somewhere yourself.
New Member
New Member

09 Dec 2010 09:33 AM
Well msdb has the backup history that is kept for x many days according to the cleanup settings. You could potentially see what the last backup doen was named and extrapolate the value from that and increment it for your next one. But I fail to see any real benefit from doing this and it would easily get out of sync if you ran a manual backup for instance that didn't follow this convention.

Acceptable Use Policy