SQL Server database backup using a Batch file

• Download source code - 1 KB

Introduction

This article will help you make a Batch file which can easily backup SQL Server databases.

Background

Today (08-08-2012) I got this problem so I had to refer to 20-100 articles to solve this issue. I think those articles were very complex to understand. So I tried it myself, and finally I did it

Using the code

1. In here you can find two files, and those are .bat and .sql files. The Batch file is used to make the backup folder structure. This code creates those backup folders in the “E:” drive of your computer. This batch file makes a TEST folder (directory) and that folder (directory) has two subfolders (directories). Those are named ScriptTEST and DataTEST. Now look at the Test.bat file. You are needed to put those two files in the same folder and run the Test.bat file. Then you can see the backup folder generated on ‘E:\TEST’.

Test.bat file
SET curr_dir=%cd%
cd /D E:
IF EXIST TEST ( 
cd TEST
mkdir ScriptTEST DataTEST
cd ScriptTEST
) ELSE ( 
mkdir TEST
cd TEST
mkdir ScriptTEST DataTEST
cd ScriptTEST
)
SET SRC="%~dp0TestBackUpSQL.sql"
SET DEST="TestBackUpSQL.sql"
if not exist %DEST% copy /V %SRC% %DEST%
sqlcmd -i "E:\TEST\ScriptTEST\TestBackUpSQL.sql"

2. In here you need to enter the database name and the back up set will be generated with name+date+time of server.
TestBackUpSQL.sql file

DECLARE 
@pathName NVARCHAR(512),
@databaseName NVARCHAR(512) 
SET @databaseName = 'Enter Your DataBase Name Here' 
SET @pathName = 'E:\TEST\DataTEST\Enter Your DB Back Up Name Here_' + 
    Convert(varchar(8), GETDATE(), 112) + '_' + 
    Replace((Convert(varchar(8), GETDATE(), 108)),':','-')+ '.bak' 
BACKUP DATABASE @databaseName TO  DISK = @pathName WITH NOFORMAT, 
    NOINIT,  NAME = N'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO