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
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.
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
In here you need to write your server name, database name, user name, and password of the database server.
This code segment is used to set the server authentication
:: Set your server name eg:- ITSERVER
:: Set your backup database name - in this code my backup database name is TestDB
:: Set server authentication username & password of the database server
:: Write your database server user name eg:- sa
:: Write your database server password eg:- 123
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
Hope to see you again with a new development.
Good luck! Two thumbs up!