none
SQL Server 2005 backup command and timestamps

    Question

  •  

    How do you add a specific timestamp to a backup?  For example, if the backups are going to the same drive location on disk and you want to retain 3 days worth of backups online, how do you add the timestamp to the filename to make each backup unique?

     

    F:\MSSQL.1\MSSQL\Backup and your user database is <xyz>_<timestamp>.bak

    The user database dumps each night at 9 PM.

     

    You want to keep 3 days of online backups.

    Tuesday, April 01, 2008 4:51 PM

Answers

  • Clangev,

     

    Welcome to the forums.  There are actually a number of good ways to do what you want.  Have a look at this post which has a few methods to do this.

     

     

    Tuesday, April 01, 2008 10:17 PM
  • I have a stored procedure that could help you with this.

     

    EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = 'C:\Backup', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 24

     

    This means that it should do a full backup of all user databases. The root backup directory is C:\Backup. The backups are verified. Backup files that are older than 24 hours are deleted on success. The directories and files are created with the following naming.
    C:\Backup\Server$Instance\Database\FULL\Server$Instance_Database_FULL_20080407_000242.bak

     

    http://ola.hallengren.com/sql-server-backup.html

     

    Ola Hallengren
    http://ola.hallengren.com

     


    Sunday, April 06, 2008 10:10 PM
  • You can accomplish this by using dynamic sql to execute the backup command.  This is a simple layout where you can specify the database and file path.

     

    Note: you can convert the date to look like you want by using the convert function.

     

    Code Snippet

    DECLARE @Dt DATETIME,

       @FilePath VARCHAR(100),

       @FileName VARCHAR(200),

       @dbName VARCHAR(50),

       @sql NVARCHAR(1000)

     

    SET @Dt = GETDATE()

    SET @FilePath = 'F:\MSSQL.1\MSSQL\Backup\'

    SET @dbName = 'Test'

    --you can convert the date to what ever you like by using the convert function

    SET @FileName = @FilePath + @dbName + REPLACE(CONVERT(VARCHAR,@Dt,101),'/','') + '.bak'

     

    SET @sql = N'

    BACKUP DATABASE ['+@dbName+']

    TO DISK = '''+@FileName+'''

    WITH RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = N'''+@dbName+'-Full Database Backup'',

    SKIP, NOREWIND, NOUNLOAD, STATS = 10

    '

    EXECUTE sp_executesql @sql;

     

     

     

    I hope this helps.

     

     

    Tuesday, April 08, 2008 3:32 AM

All replies

  • Clangev,

     

    Welcome to the forums.  There are actually a number of good ways to do what you want.  Have a look at this post which has a few methods to do this.

     

     

    Tuesday, April 01, 2008 10:17 PM
  • You can also perform a backup using a fixed name and rename it after backup with a name which is suffixed by date (and time).

    Here is an example for that: http://www.sqlusa.com/bestpractices2005/renamebackupfile/



    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Tuesday, October 23, 2012 11:54 PM
    Wednesday, April 02, 2008 5:40 PM
  • Kalman,

     

    Keep in mind that xp_cmdshell is not enabled by default in SQL Server, and enabling it opens a security risk that would not exist otherwise.  It is generally considered a best practice to leave xp_cmdshell disabled unless it is absolutely necessary to enable it.  For repetative tasks similar to this one, it would be much better to run the name changes in the SQL Agent as an operating system cmdexec step, rather than perform such a task from inside of the databse engine itself.

     

    Wednesday, April 02, 2008 6:32 PM
  • Valid point about the security risk.

     

    It depends on the particular environment (e.g. SQL Server behind double firewall...) if xp_cmdshell can be operated safely.

     

    Here is a backup file datetime stamping for SQL Serve 2008 which is not using xp_cmdhell:

     

    http://www.sqlusa.com/bestpractices2008/administration/backupwithdatetimestamp/

     

     

    Wednesday, April 02, 2008 7:48 PM
  • I have a stored procedure that could help you with this.

     

    EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = 'C:\Backup', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 24

     

    This means that it should do a full backup of all user databases. The root backup directory is C:\Backup. The backups are verified. Backup files that are older than 24 hours are deleted on success. The directories and files are created with the following naming.
    C:\Backup\Server$Instance\Database\FULL\Server$Instance_Database_FULL_20080407_000242.bak

     

    http://ola.hallengren.com/sql-server-backup.html

     

    Ola Hallengren
    http://ola.hallengren.com

     


    Sunday, April 06, 2008 10:10 PM
  • You can accomplish this by using dynamic sql to execute the backup command.  This is a simple layout where you can specify the database and file path.

     

    Note: you can convert the date to look like you want by using the convert function.

     

    Code Snippet

    DECLARE @Dt DATETIME,

       @FilePath VARCHAR(100),

       @FileName VARCHAR(200),

       @dbName VARCHAR(50),

       @sql NVARCHAR(1000)

     

    SET @Dt = GETDATE()

    SET @FilePath = 'F:\MSSQL.1\MSSQL\Backup\'

    SET @dbName = 'Test'

    --you can convert the date to what ever you like by using the convert function

    SET @FileName = @FilePath + @dbName + REPLACE(CONVERT(VARCHAR,@Dt,101),'/','') + '.bak'

     

    SET @sql = N'

    BACKUP DATABASE ['+@dbName+']

    TO DISK = '''+@FileName+'''

    WITH RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = N'''+@dbName+'-Full Database Backup'',

    SKIP, NOREWIND, NOUNLOAD, STATS = 10

    '

    EXECUTE sp_executesql @sql;

     

     

     

    I hope this helps.

     

     

    Tuesday, April 08, 2008 3:32 AM