DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

Home All Articles Implementing a Simple SQL Server Backup Script

Implementing a Simple SQL Server Backup Script

Posted on October 21, 2013 Written by Andy Hayes 1 Comment

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.

Related Posts:

  • How to Setup MySQL Master Master ReplicationHow to Setup MySQL Master Master Replication

Filed Under: All Articles, SQL Server Administration Tagged With: backup and restore, sql server

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Trackbacks

  1. [Sql Serveur] Backup de bases de données SQL Serveur | Le Post de MCNEXT says:
    May 13, 2014 at 3:37 pm

    […] http://dbadiaries.com/implementing-a-simple-sql-server-backup-script Vidéo: How to Backup SQL Server Database using SQL Script Automatisation et planification de sauvegardes : […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CAPTCHA
Refresh

*

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • How to shrink tempdb
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright © ‘2021’ DBA Diaries built on the Genesis Framework

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close