Click here to Skip to main content
Click here to Skip to main content

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

, 21 Aug 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

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.

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)

Share

About the Author

Charitha Athukroala
Systems / Hardware Administrator
Sri Lanka Sri Lanka
No Biography provided

Comments and Discussions

 
QuestionI forgot to mention.... PinmemberCharitha Athukroala2-Sep-12 18:59 
GeneralMy vote of 5 Pinmembermagicpapacy26-Aug-12 23:40 
GeneralRe: My vote of 5 PinmemberCharitha Athukroala28-Aug-12 1:08 
Thanks Roll eyes | :rolleyes:
 
Charitha

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141216.1 | Last Updated 21 Aug 2012
Article Copyright 2012 by Charitha Athukroala
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid