Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / SQL
Tip/Trick

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.4K   737   3   3
Creating SQL database backups through batch scripts and SQL scripts.

Background

I wrote an article named “SQL Server database backup using a batch file”: http://www.codeproject.com/Tips/437177/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: http://notepad-plus-plus.org/) 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.

SQL
DECLARE 
@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' 
BACKUP DATABASE @databaseName TO DISK = @pathName WITH NOFORMAT, NOINIT, 
    NAME = N'TestDBScript-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

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

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

:: 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
:EndServerAuthentication

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!

License

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 17:59
Charitha Athukroala2-Sep-12 17:59 
GeneralMy vote of 5 Pin
magicpapacy26-Aug-12 22:40
magicpapacy26-Aug-12 22:40 
GeneralRe: My vote of 5 Pin
Charitha Athukroala28-Aug-12 0:08
Charitha Athukroala28-Aug-12 0:08 
Thanks Roll eyes | :rolleyes:

Charitha

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.