Backup History

Last Post 09 Dec 2010 08:33 AM by gunneyk. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
river1
New Member
New Member

--
09 Dec 2010 05:48 AM
Hi Masters,

I have created a job that makes backups.

Code sample:

declare @date as varchar(25)
Declare @Nome as varchar(50)
DECLARE Backups CURSOR FOR
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

WHILE @@FETCH_STATUS = 0
BEGIN




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



FETCH NEXT FROM backups INTO @nome
END

CLOSE backups
DEALLOCATE 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
rm
New Member
New Member

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

--
09 Dec 2010 08: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.
You are not authorized to post a reply.

Acceptable Use Policy