[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


Batch File to Perform Backup of MSSQL Database

Published on
11,544 Points
Last Modified:
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and will then append that to the file name.
Microsoft SQL Server Express editions do not have a built-in mechanism for performing scheduled backups.  By using the command line utility "SqlCmd", it is possible to use this functionality in a Windows batch file.  

By default, the local SQL Server accounts do not have access to network shares.  Therefore, it is necessary to backup to a local folder and then move the backup file to a network share location.  This means that a folder on the local hard drive must exist that has write privileges granted to the user account that runs the SQL Server instance service.  This is usually the "Local System" account or another user account that has been manually configured to run the service.

You will need to know the following information before proceeding.
  • SQL Server name
  • Service instance name for SQL Server (usually MSSQLSERVER or SQLEXPRESS)
  • Name of the database to be backed up
  • Username and password with backup privileges in SQL Server
  • Path to an existing local folder to which SQL has write privileges
  • Path to an existing remote folder to which the file will be moved.

1. Open Notepad (or any text editor)

2. Paste the following code into the text editor

@echo off

rem Set these variables to the desired values

set SqlServer=SERVER_NAME (The name of your SQL server)
set InstanceName=MSSQLSERVER (The name of the SQL instance)
set Username=SQL_USERNAME (The username with backup rights)
set Password=SQL_PASSWORD (The password for the above user)
set Database=DATABASE_NAME (The database to be backed up)
set LocalFolder=C:\Temp (Temporary local folder to hold backup, no quotes)
set NetworkFolder="\\remotecomputer\folder" (URL to permanent backup location, include quotes)

rem ************************************
rem * Don't touch anything below here. *
rem ************************************

echo Getting current date and time...
for /f %%a in ('sqlcmd -S %SqlServer% -U %Username% -P %Password% -Q "SET NOCOUNT ON select ltrim(convert(date, getdate()))" -h -1') do set CurrentDate=%%a
for /f %%a in ('sqlcmd -S %SqlServer% -U %Username% -P %Password% -Q "SET NOCOUNT ON select right('00' + ltrim(datepart(hour, getdate())), 2)" -h -1') do set CurrentHour=%%a
for /f %%a in ('sqlcmd -S %SqlServer% -U %Username% -P %Password% -Q "SET NOCOUNT ON select right('00' + ltrim(datepart(minute, getdate())), 2)" -h -1') do set CurrentMinute=%%a
for /f %%a in ('sqlcmd -S %SqlServer% -U %Username% -P %Password% -Q "SET NOCOUNT ON select right('00' + ltrim(datepart(second, getdate())), 2)" -h -1') do set CurrentSecond=%%a

echo Backing up database to %LocalFolder%
SqlCmd -S %SqlServer% -U %Username% -P %Password% -Q "Backup Database %Database% To Disk='%LocalFolder%\%Database%-%CurrentDate%_%CurrentHour%%CurrentMinute%%CurrentSecond%.bak'"

echo Copying backup to %NetworkFolder%
move /Y %LocalFolder%\%Database%-*.bak %NetworkFolder%

Open in new window

3. Save the file with an extension of .bat to ensure that it is a batch file and not a plain text file. 

Note:  The icon should change to a command prompt.  If not, the file extension is probably .bat.txt, which means that you will need to display file extensions to remove the .txt extension.

4. To perform the backup, double-click on the new batch file.  You will see it perform some queries and then it will export the data to a file.  

Note:  The amount of time to perform the backup may take a while depending on the size of the database.

After making sure that the batch file works as expected, you can add it as a scheduled task in the Windows "Task Scheduler" to have it run daily at a desired time.

This procedure is complete.

Expert Comment

by:Shaad Parihar
The above code is for one database.
If i have multiple databases and i want to use a single batch file.how i add multiple database in the code.

Expert Comment

by:Sid bharadwaj
Nice and simple !
Keep up the good work.

Expert Comment

by:Yodhraj Patil
Not Working !!!!!
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Expert Comment

by:Siddhartha Chaurasia
I am facing following issue, What I am doing wrong?
LVL 16

Author Comment

by:Brian Pringle
@Siddhartha Chaurasia

It looks like you have two problems:

1. You cannot use a URL, so you would have to map a drive letter to that network share.
2. You are getting a login error.  Check your username and password.

Expert Comment

by:Siddhartha Chaurasia
Hi Brian Pringle

My UserName and Password is correct, I am able to backing up the Database through SQL Server Management Studio Even the ODBC connection is passed when I checked but Via batch file it is getting error.

Expert Comment

by:Mmavr Mmavr
Thank you very much.
Great Script.
Thank you

Featured Post

Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Join & Write a Comment

Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month