SEARCH

How-To Geek

Batch Script to Backup All Your SQL Server Databases

We have already covered how to backup a SQL Server database from the command line, so what if you want to backup all your databases at once? You could create a batch script which runs the backup command for each database, but this script would have to be updated each time a database is added or removed. Additionally, the database backups will all be appended to one file which will grow by the size of the new backup each time it is run. Instead, in true “set it and forget it” fashion, we will create a batch script which will adapt to your SQL Server as new databases are added and removed.

To get right to the point, this is the backup script:

@ECHO OFF
SETLOCAL

REM Get date in format YYYY-MM-DD (assumes the locale is the United States)
FOR /F “tokens=1,2,3,4 delims=/ ” %%A IN (‘Date /T’) DO SET NowDate=%%D-%%B-%%C

REM Build a list of databases to backup
SET DBList=%SystemDrive%SQLDBList.txt
SqlCmd -E -S MyServer -h-1 -W -Q “SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN (‘master’,’model’,’msdb’,’tempdb’)” > “%DBList%”

REM Backup each database, prepending the date to the filename
FOR /F “tokens=*” %%I IN (%DBList%) DO (
ECHO Backing up database: %%I
SqlCmd -E -S MyServer -Q “BACKUP DATABASE [%%I] TO Disk=’D:Backup%NowDate%_%%I.bak'”
ECHO.
)

REM Clean up the temp file
IF EXIST “%DBList%” DEL /F /Q “%DBList%”

ENDLOCAL

Assuming the date is 1/13/2009 and you have 3 databases named ‘MyDB’, ‘AnotherDB’ and ‘DB Name with Spaces’, the script will produce 3 files in the backup location specified:

  • 2009-01-13_AnotherDB.bak
  • 2009-01-13_DB Name with Spaces.bak
  • 2009-01-13_MyDB.bak

Customizing and Running the Batch Script

Of course, you will want to customize the script to your environment so here is what you need to do:

  • If your machine’s locale is not set to the US, the command ‘Date /T’ may not return the date in the format “Tue 01/13/2009”. If this is case, the NowDate variable will not produce the desired format and should be adjusted. (1 place)
  • Change ‘MyServer’ to be the name of your SQL Server (add the instance name if applicable). (2 places)
  • The databases named ‘master’, ‘model’, ‘msdb’ and ‘tempdb’ are databases which ship with SQL Server. You can add additional database names to this list if you do not want them to be backed up. (1 place)
  • Change the backup location from ‘D:Backup’ to the location where you want the database backup files stored.

Once you have customized the batch script, schedule it to run via Windows Task Scheduler as a user with Administrator rights and you are all set.

Jason Faulkner is a developer and IT professional who never has a hot cup of coffee far away. Interact with him on Google+

  • Published 08/2/10

Comments (8)

  1. Lery

    I am pretty sure that if you use this method of backing up your database, you are not going to clear our tempdb. Microsoft’s built in wizard for backing up the database is all you really need. You schedule it to run and its done. This also guarantees your tempdb is getting cleaned out.

  2. Atiq Ur Rahman Chaudhary

    Wonderful,

    I need restore script too to restore all backup databases to new clean instance of SQL Server. The explanation is as below:

    Question:
    How to backup SQL Server 2005 user databases in SQL script with all data (.sql) and restore this script of all databases on SQL server 2008

    OR

    How to restore all databases backed up through this script into new clean instance of SQL Server (backed up in SQL Server 2005 standard and restore to SQL Server 2008 web edition).

    Can someone help me to backup all MS SQL Server 2005 standard user databases into SQL script (single file) and restore all databases with data from single backup script file to SQL server 2008 web edition.

    Symptom:
    Upgrade option for SQL Server standard 2005 to SQL SQL Server 2008 web edition is not supported. I’ve planned to upgrade SQL Server 2005 standard instance to SQL server 2008 web edition that is not supported and I’m thinking to backup all databases on SQL server 2005 standard into single script file (.sql) install new instance of SQL Server 2008 web edition and restore single script file into new instance. Please help me backup all databases into single script file.

    OR

    Alternative option is to generate script to restore all databases through your script in new installation of SQL Server 2008 web edition.

    Help me please.

  3. Gonzalo

    Thanks alot Jason. I needed to backup the DBs, compress them, send the archive to another server and delete the uncompressed BAKs.
    Your script was the exact piece I was lacking of (with a slight customizing as mentioned and in characters used).

  4. Hayden

    This was a very useful script. Is there anyway to automatically run batch files at a given time during the day?

  5. Hayden

    To answer my own question, I used the Windows built-in system tool, Task Manager. I created a task that runs my batch files at a particular time. It’s a pretty useful tool and keeps me from having to actually run the batch files myself every day.

  6. JB

    @Hayden: There is also a command line interface to Task Scheduler, type AT /? for details. The AT command schedules the script to run with LocalSystem privileges, while the GUI schedules it to run as whatever user account set up the task. Thus a script scheduled with AT will continue to run after the original users Admin account is deleted or disabled, GUI scheduled jobs will not.

    Now my main comments for this Howto:

    1. On any Windows version still supported by MS, in place of ‘DATE /T’, you can just use “%DATE%” because DATE by default is a magic variable whose value is whatever DATE /T would output.

    2. While *blank* versions of the 4 system databases (master etc.) are included in a fresh install of SQL server, those do not contain all the changes you (indirectly) made to those databases by configuring your server. So they need to be backed up and restored too. Unfortunately, Microsoft has made this extra hard by enacting special rules for how to back them up.

    I will use your script as a rough starting point as I create a new backup system for my SQL servers.

  7. satuday

    hi, i cant get the script to work, it said “1 was unexpected at this time”. any body know what can be the problem.

  8. sitlink

    thank .

Enter Your Email Here to Get Access for Free:

Go check your email!