Implementing a Simple SQL Server Backup Script

I’ve used this simple SQL Server backup script a number of times to backup sql server to a file or network drive. It’s ideal if you are running SQL Server Express for example which does not come equipped with maintenance plans.  This script can be used in conjunction with SQLCMD to backup SQL Server from the command line.

So let’s take a look at this.

What we want to achieve here is a way of executing the same backup database command for each database in SQL Server. The “bare bones” T-SQL used might look like this. In this example, I am using my local drive as the backup destination.

BACKUP DATABASE dbname TO DISK =
'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\BACKUP\dbname.bak';

I say bare bones because the above command will backup a database to disk for you but there are a wide selection of different options which can be used to customize the backup. For more on that visit this link

We can query SQL Server for a list of the databases installed on the instance. You can filter out the databases which you are not interested in backing up.

SELECT Name FROM sys.databases WHERE Name NOT IN ('Model','TempDb');

So I get back this list..

Name
------------------------------
AdventureWorks2012
master
msdb
ReportServer$SQL2012
ReportServer$SQL2012TempDB
Test

I now want to write a simple loop to run through each of these names and execute the BACKUP DATABASE command producing a file on disk. For this, you could use a CURSOR or a WHILE LOOP. I’ll show you both ways.

Which one you choose is a matter of preference. Note that by default if you backup to the same file on disk, each backup is appended to the backup file. If you want to overwrite your backup files, use WITH INIT. Refer to the link above for more information on the syntax.

Backup SQL Server databases using a cursor

DECLARE @dbName VARCHAR(50)
DECLARE @backupPath VARCHAR(256)
DECLARE @backupFileName VARCHAR(256)

SET @backupPath =
'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\'

DECLARE backup_cursor CURSOR FOR
SELECT Name FROM sys.databases WHERE Name NOT IN ('Model','TempDB');
OPEN backup_cursor
FETCH NEXT FROM backup_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @backupFileName = @backupPath + @dbName
BACKUP DATABASE @dbName TO DISK = @backupFileName
FETCH NEXT FROM backup_cursor INTO @dbname
END
CLOSE backup_cursor
DEALLOCATE backup_cursor

Backup SQL Server databases using a while loop

DECLARE @dbName VARCHAR(50)
DECLARE @backupPath VARCHAR(256)
DECLARE @backupFileName VARCHAR(256)
DECLARE @id INT

SET @backupPath =
'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\'

SET @id = (SELECT MIN(database_id) FROM sys.databases WHERE Name NOT IN ('Model','TempDB'))
WHILE @id IS NOT NULL
BEGIN
SET @dbName = (SELECT Name FROM sys.databases WHERE database_id = @id)
SET @backupFileName = @backupPath + @dbName
BACKUP DATABASE @dbName TO DISK = @backupFileName
SET @id = (SELECT MIN(database_id) FROM sys.databases WHERE Name NOT IN ('Model','TempDB') AND database_id > @id)
END

(Apologies if this code looks a little messy. I am looking for a better code formatting plugin – any suggestions? 🙂 )

Once you have customized the script to your needs, wrap it inside of a stored procedure. Depending on what version of SQL Server you running, you can either schedule this using SQL Server Agent or via the windows task scheduler by calling SQLCMD.

Backing up SQL Server using SQLCMD at the command line

Start a command prompt and type SQLCMD /? to get the help options up.

Connect to your SQL Server instance using the options available and then run the backup to test that it works ok.

You can set up a windows batch file to call SQLCMD. I could add the following line to a batch file and I am now ready to schedule this using windows task scheduler.

SQLCMD -E -S MACHINE\INSTANCE -Q "exec master.dbo.usp_backupDatabases"

Next Steps

It’s important that you get the right plan in place for your server. What I have provided is just a basic script, call it a template if you will. You can customize it to include differential backups, transaction log backups, different file locations, multiple backup sets, multiple files etc.

Have a read of the BACKUP DATABASE documentation to get a idea for what is possible. Communicate with the business to agree requirements, write a backup plan and test whatever code you write thoroughly to ensure that it is meeting the backup plan requirements.

Finally

Backing up using scripts is very handy for editions of SQL Server which do not come equipped with maintenance plans and SQL Server Agent. If you are running one of those editions that does have those features, then you might decide to choose those tools instead. Using backup scripts however still remain highly viable and are preferable to some DBA’s.