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

SQL Server database backup using a Batch file

, 8 Aug 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
This article will help you make a Batch file which can easily backup SQL Server databases.

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 Wink | ;)

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’.
  2. 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"
  3. In here you need to enter the database name and the back up set will be generated with name+date+time of server.
  4. 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

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

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 8 Aug 2012
Article Copyright 2012 by Charitha Athukroala
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid