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

• Download code - 2.56 KB

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

Hope to see you again with a new development.

Good luck! Two thumbs up!