Backup history

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Backup history

Backup history

Rate Topic

Display Mode

Topic Options

Author

Message

river1

Posted Thursday, December 09, 2010 4:52 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

Hi,

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 name 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?

Post #1032369

george sibbald

Posted Thursday, December 09, 2010 5:25 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:09 AM
Points: 5,269, Visits: 11,209

select count(*) from msdb..backupset where database_name = 'yourdb' and type = 'D'

However older backup history can be purged from this table by cleanup operations so unless you never run this purge and just allow the table to grow ad infinitum the info could be wrong. This table has an identity column backup_set_id you could use as an ever increasing number, but it would not reflect the actual number of backups for that database.

---------------------------------------------------------------------

Post #1032797

river1

Posted Friday, December 10, 2010 2:36 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

What did you meant buy "cleanup operations" ?

Thank you

Post #1032890

John Mitchell-245523

Posted Friday, December 10, 2010 2:46 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:55 AM
Points: 4,434, Visits: 7,216

There's a stored procedure sp_delete_backuphistory that you can use to purge old backup records from the msdb database.

John

Post #1032895

river1

Posted Friday, December 10, 2010 2:51 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

we don't use that.

Thanks for the tip.

Post #1032897

george sibbald

Posted Friday, December 10, 2010 3:47 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:09 AM
Points: 5,269, Visits: 11,209

The above proc would also be run if you use the history cleanup task in a maintenance plan and select Backup and restore history

---------------------------------------------------------------------

Post #1032917

« Prev Topic | Next Topic »

Permissions