Backup All Databases

Here is a handy, little TSQL script that can be used to backup all databases on a server. Why would you need such a script? How about when you have an instance of Express Edition, which does not include support for maintenance plans or scheduled jobs? This script can be scheduled though the task scheduler and SQL command line or through other means.

The script starts by creating a suffix for the backup file, in the format of _backup_yyyymmdd_hhmissmmm.bak. The suffix will be appended to the database name to create the full backup file name. As an example, if the “DB” database was backed up on June 17, 2012, at 1:26:15.123 PM, the full file name would be: DB_backup_20120617_132615123.bak. (Happy Father’s Day, by the way! :-))

Furthermore, the example below assumes we will save the backup file to the B: drive, in a folder with the database name. Thus, the full path for our example database would be: B:\DB\DB_backup_20120617_132615123.bak. Both the path and file name can be easily modified as needed, obviously.

The script makes use of the sp_MSforeachdb system stored procedure in order to execute the backup operation against every database. Note that the tempdb system database is excluded as backup and restore operations are explicitly disallowed on the tempdb database.

-- Create the backup file name suffix
DECLARE @suffix CHAR(30) =
    '_backup_'
    + CONVERT(CHAR(8), GETDATE(), 112)
    + '_'
    + REPLACE(CONVERT(CHAR(12), GETDATE(), 114), ':', '')
    + '.bak'
;

-- Create the SQL string to execute for each database on the server
DECLARE @strSQL VARCHAR(255) = 'IF ''?'' <> ''tempdb''
    BACKUP DATABASE [?]
    TO DISK = N''B:\?\?' + @suffix + '''
    WITH
        NOFORMAT
        , NOINIT
        , NAME = N''?-Full Database Backup''
        , SKIP
        , NOREWIND
        , NOUNLOAD
        , STATS = 10
;'
;

-- Execute the SQL string for each database on the server
EXECUTE sp_msforeachdb @strSQL
;

Advertisements
Post a comment or leave a trackback: Trackback URL.

Comments

  • rob  On 07 Jan 2014 at 1048

    genious!! your script was very useful..thanks much

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: