database backup with date stamps

  • Hello All,

    I wonder if somebody could help me.

    I'd like to keep a database back up set for a week and backup set should expire after a week. meaning on day 8, day one is deleted so you always have 7 weeks database backup stamped with the date it was backed up.

    What I have done I wrote a T-SQL and it does the job but what I'd like more to do is to leave this job to SQL server 2005. Basicallly, SQL Srever 2005 does part of this job for you but it does not stamp the data with a particualr date and does not delete the previous backups.

    Thus, It will be appriciat if somebody has done this before using SQL server instead of writing the copd himself/herself. does the Maintenace Plan does this job.

    Looking forward to your valued responses guys,

    Awaiting....

  • We are using this to create a nice name for our backups, but I'm looking for code to delete files after a certain day too. Maintenance plans always seem to mess this up for us.

    DECLARE @DBNAME SYSNAME

    DECLARE @sql VARCHAR (250)

    DECLARE DB CURSOR FOR

    SELECT NAME FROM SYS.DATABASES WHERE NAME NOT IN ('TEMPDB')

    OPEN DB

    FETCH NEXT FROM DB INTO @DBNAME

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @sql = 'BACKUP DATABASE [' +

    @DBNAME +

    '] TO DISK = ''G:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\' +

    @DBNAME + '\' +

    @DBNAME + '_FULL_' + convert(varchar(8),getdate(),112) + '_'

    + replace(convert(varchar(3),getdate(),108),':','') +

    '.BAK'''

    EXEC (@SQL)

    FETCH NEXT FROM DB INTO @DBNAME

    END

    CLOSE DB

    DEALLOCATE DB

    Todd Carrier
    MCITP - Database Administrator (SQL 2008)
    MCSE: Data Platform (SQL 2012)

  • :hehe: I was more interested in knowing how can this job be done rather thatn carry on others problems :D. So proper helps will be appreciate.

  • Yes. Maintenance Plan does this job.

  • How mate a short clue would help?

    Thanks

  • Use "Maintenance Cleanup Task" to delete older .bak files.

    Maintenance Plan automatically adds date time stamp to the filename.

    e.g. master_backup_200711132245.bak

  • Thank you indeed Suresh.

    It worked perfectly mate.

    Cheers

  • Welcome.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply