Implementing a Simple SQL Server Backup Script

sql server backup scriptI’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.

backup sql server command line

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.

Using DBCC SQLPERF

There are a number of uses for the DBCC SQLPERF command. I touched upon one of them in my last post on how you can use the command to obtain information about transaction log growth. This handy little DBCC command has been around for a while … [Continue reading]

Monitoring SQL Server Transaction Log Growth Using DBCC SQLPERF(logspace)

dbcc sqlperf(logspace)

For this article, I am using DBCC SQLPERF(logspace) to get size information about the transaction logs. I hope you will find this post useful in helping to monitor the growth of your transaction logs. The idea here is to capture and store the … [Continue reading]

An Introduction to SQL Server Backup

sql server backup database

Before I get into this post on SQL Server backup, I must make something really clear without sounding like I am stating the obvious. :| Having a fully tested backup and restore plan in place for your SQL Server databases is one of, if not, THE … [Continue reading]

Differences Between TRUNCATE TABLE and DELETE

If you want to remove data from a table, you can do this in a couple of ways using TRUNCATE TABLE or DELETE. A common approach is to use DELETE to remove all rows in the table but TRUNCATE TABLE offers an alternative and I will list what the … [Continue reading]

Using DBCC FREEPROCCACHE to Clear SQL Server Procedure Cache

dbcc freeproccache clear sql server plan cache

It is possible to clear out the entire SQL Server procedure cache using DBCC FREEPROCCACHE The procedure cache is where SQL Server will cache execution plans after they have been compiled. The benefit of this caching is that there is no need for … [Continue reading]

The OLEDB Wait Type and How to Reduce It

reduce oledb wait type

In this post, I look at the OLEDB wait type. Books Online lists this as Occurs when SQL Server calls the SQL Server Native Client OLEDB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the … [Continue reading]

Have You Set Yourself Any Goals for the New Year?

"New year, new start" - I've heard that saying several times and it could mean the start of many different things. A new diet, a new fitness regime, a new job or complete career change. Giving up a vice, a new qualification, a new life - there … [Continue reading]

Reducing SQL Server CXPACKET Wait Type

cxpacket wait type parallelism

In my last post, I wrote about how SQL Server schedules tasks to be executed. It's important to be  able to understand this when when trying to analyse wait types and statistics in SQL Server. For this post, I will be looking at the CXPACKET wait … [Continue reading]

An Overview of SQL Server Task Scheduling

In my previous post, I touched upon SQL Server wait types and in order to understand wait types in SQL Server, it's important to know first how the server schedules tasks. On each CPU core, only a single thread or "worker" can be running at any … [Continue reading]

An Introduction to SQL Server Wait Types and Stats

sql server wait types and stats

Ok, so in this post, I wanted to go over SQL Server wait types and statistics  - what they are and how to check them. My idea is then to start a series of posts which go into more detail about individual SQL Server wait types, their causes and how … [Continue reading]