SQL Server Backup Script Example

Here is a small SQL Server backup script sample from some of my DBA work. These are usually run on-demand to create custom on-off backups before and after special software upgrades.

The first part is a simple batch file that uses the “sqlcmd” command to run your backup. This is intended to be run by hand to view any error output with the “pause” command. If you plan to schedule a task, you would need absolute path names and a way to automatically clear out old backup files to minimize space used by backup files.

REM ==== cmd batch file name "run_backup.bat" ====
sqlcmd -i backup_db.sql -o log_backup_db.txt
pause
REM ==== remove "pause" if you're scheduling this job ====

The second part is the actual T-SQL commands to backup the database. The batch file above depends on this SQL file to do the actual backup. Note that the date and time are used as a dynamic prefix for the file name along with the database name to keep the backup file-names unique. A “copy only” backup is used to prevent issues with automated full/differential sets.

-- SQL file name "backup_db.sql" --
DECLARE @myDate VARCHAR(100)
DECLARE @dbName VARCHAR(50)
DECLARE @fileName VARCHAR(MAX)
DECLARE @folder VARCHAR(MAX)

-- Set a location to store your database backup files
SELECT @folder = 'C:/SQL_Backup/'
-- Change Date-Time to filename-compatible format
SELECT @myDate = REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),' ','_'),':','');

PRINT 'Start at ' + CONVERT(VARCHAR,GETDATE(),120)
PRINT '' -- blank line

SELECT @dbName = 'Your_Database_Name'
SELECT @fileName = @folder + @myDate + '_' + @dbName + '_full.bak'
PRINT 'Backup Database "' + @dbName + '" to "' + @fileName + '"'

BACKUP DATABASE @dbName 
TO  DISK = @fileName 
WITH COPY_ONLY, NOFORMAT, NOINIT,  
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  
STATS = 10
;
PRINT '' -- blank line
PRINT 'Finish at ' + CONVERT(VARCHAR,GETDATE(),120)
Advertisements

About notesbytom

Keeping technology notes on WordPress.com to free up my mind to solve new problems rather than figuring out the same ones repeatedly :-).
This entry was posted in Database and tagged , , , . Bookmark the permalink.

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