LearnItFirst User Forum

/*
    Author: Scott Whigham from http://www.LearnSqlServer.com/

    Description: This script makes a backup and appends a Date + Time string at the end.
                            AdventureWorks_20060828_1101.bak

    Misc Notes: You could easily make this a stored procedure and pass in the type of
                            backup to take as well:

                            CREATE PROC sp_BackupDatabase (@DbName, @BackupFolder, @BackupType)
                            AS
                            .......
                            SELECT @BackupFileName = @DBName + '_' +     @BackupType + '_' + @DateString + '_' +
                                REPLACE ( @TimeString, ':', '' ) + '.' + @BackupExtension    

                            IF @BackupType = 'FULL'
                                EXEC ('BACKUP DATABASE ' + @DBName + ' TO DISK =''' + @BackupFolder + @BackupFileName + '''')
                            ELSE IF @BackupType = 'DIFF'
                                EXEC ('BACKUP DATABASE ' + @DBName + ' TO DISK =''' + @BackupFolder + @BackupFileName + ''' WITH DIFFERENTIAL')
                            ELSE
                                EXEC ('BACKUP LOG ' + @DBName + ' TO DISK =''' + @BackupFolder + @BackupFileName + '''')
                            GO
   
    Versions: SQL Server 2005, 2000, 7.0
   
    Creation Date: August 28, 2006

    For more scripts like this one, visit http://forums.learnsqlserver.com/codesamples.aspx
*/
DECLARE @DateTime DATETIME
    , @DateString VARCHAR(8)
    , @TimeString VARCHAR(12) -- easier to format final string this way
    , @DBName SYSNAME
    , @BackupExtension CHAR(3)
    , @BackupFileName VARCHAR(255)
    , @BackupFolder VARCHAR(255) -- Full path to backup folder (i.e., '\\MyServer\MyBackups\')

-- We could do all this in the EXEC statement but this is a bit "cleaner" and easier to manage
SELECT     @DateTime = GETDATE()
    , @DBName = 'master'
    , @BackupExtension = 'BAK'
    , @DateString = CONVERT(CHAR(8), @DateTime, 112)
    , @TimeString = CONVERT(CHAR(12), @DateTime, 14)
    , @BackupFolder = 'C:\'

SELECT @BackupFileName = @DBName + '_' +     @DateString + '_' + REPLACE ( @TimeString, ':', '' ) + '.' + @BackupExtension    

EXEC ('BACKUP DATABASE ' + @DBName + ' TO DISK ='''
            + @BackupFolder + @BackupFileName + '''')