Click here to Skip to main content
15,846,091 members
Articles / Programming Languages / SQL

SQL database backups by batch file (using SQL script and batch script)

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
21 Aug 2012CPOL1 min read 36.3K   736   3   3
Creating SQL database backups through batch scripts and SQL scripts.


I wrote an article named “SQL Server database backup using a batch file”:

That batch system has been depending on a SQL backup database script file (.sql). I needed to remove that dependency. I searched in Google and found a large number of articles, but again I failed to understand the theory, so I gained knowledge through those articles and made a batch program which does not depend on an external SQL script file.

Using the code

I have commented the batch file, therefore there is no need to comment this again. You can run this batch file any place in your computer (including USB drives).

Hint 01: This batch file has 227 lines, so you can see this code is very complex to understand, but don’t worry. Please download the Notepad ++ program (URL: then you can easily find the solution.

Hint 02: Don’t fear the GOTO command, I used that command to make comments in the batch program and :: also does the same thing. Please read all comments and get an idea about what I did here.

You can see this SQL file already attached with the batch file. I have used that script to make another database backup by calling the SQL backup script.

@pathName NVARCHAR(512),
@databaseName NVARCHAR(512) 
SET @databaseName = 'TestDB' 
SET @pathName = 'E:\TestDB_Backup\TestDB_Backup_Data\TestScript_' + 
    Convert(varchar(8), GETDATE(), 112) + '_' + 
    Replace((Convert(varchar(8), GETDATE(), 108)),':','-')+ '.bak' 
    NAME = N'TestDBScript-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

In here you need to write your server name, database name, user name, and password of the database server.

GOTO BeginServerAuthentication
            This code segment is used to set the server authentication

:: Set your server name eg:- ITSERVER
SET server=

:: Set your backup database name - in this code my backup database name is TestDB 
SET dataBase=TestDB

:: Set server authentication username & password of the database server

:: Write your database server user name eg:- sa
SET user=

:: Write your database server password eg:- 123
SET password=

GOTO EndServerAuthentication
            End of server authentication

Points of interest

Okay guys/ladies, make your own database backup script by using the batch file. Before I faced this challenge, I didn’t have any idea about batch codes, I think now I have some idea about how to use batch codes etc Wink | ;)

Hope to see you again with a new development.

Good luck! Two thumbs up!


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Systems / Hardware Administrator
Sri Lanka Sri Lanka
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

QuestionI forgot to mention.... Pin
Charitha Athukroala2-Sep-12 18:59
Charitha Athukroala2-Sep-12 18:59 
This backup code is not properly working when you are using incorrect date format. So please use the below date format for work this code properly.

Goto --->>> Start Menu --->>> Control Panel --->>> Regional and Language Options
--->>> Regional Option (Click Button "Customize...") --->>>(Select) Date --->>> (Goto) Short Date Format --->>> (Select) M/d/yyyy --->>> (Click) Apply

Thumbs Up | :thumbsup:

Thanks and Best Regards,
Charitha Athukroala
GeneralMy vote of 5 Pin
magicpapacy26-Aug-12 23:40
magicpapacy26-Aug-12 23:40 
GeneralRe: My vote of 5 Pin
Charitha Athukroala28-Aug-12 1:08
Charitha Athukroala28-Aug-12 1:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.