Restoring Datbase Using BatchFile(*.Bat)

• Download DBScripts - 37.05 KB

Introduction

This article describes how to build a database from scratch using BatchFile from an Existing Database. From this article you’ll can learn the following required to build a database:

1. How to create a Batch File
2. How To read the *.ini file Using iniTool.exe
3. Use of SqlCmd, a command in Batch File.

Using this method we can install or restore a Database in a few steps compare to normal method in production environment.

Disadvantage with a Old Method of Deploying a Database

In old method Deploye an existing Database to the Production Environment, we have to generate all the scripts (Tables, Storedprocedure, Views and Functions ) that need to be copied from the Existing Database (In case of predefined values in the table) to the Production Database.

In the Scripts we have to replace all the Schema Name by manually. This is an long steps can be avoided by the above context (method).

Advantage with New Method of Deploying a Database

1. To Install or Restoring of a Database with a minimal time (approximatelly 3-4 minutes).
2. There will be nothing do with the Scripts (like replacing of Schema Name manually)
3. When System Crash we lose .mdf and .ldf files, but we can restore this Files in different Directory in our Method.
4. We can specify a Roles to the New Database.
5. Re-useablity of same file with slight modification in the Batch File.

Basics - How To Create A Batch File?

An Batch File can be created by using Notepad or WordPad, by saving the file Extension with .BAT

What is an " .ini " File?

.ini files are plain-text files that contain configuration information. In our application(method) this File is used to store Database Name, Database Server Name, log file path, log file size, Data file path, Data file size etc.

Creating/Editing " .ini "Files

.ini file can be created by using Notepad or Wordpad, by saving the file Extension with " .ini ".

Format of " .ini " Files

.ini files contain one or more sections. Each section begins with a section name, which is followed by zero or more entries. An entry associates a key name with a value.

The general format is:

[section]
keyname = value

Comments can also be included in the file by prefacing the comment with a semicolon (;).

How To Read a " .ini " File?

we can use iniTool.exe for reading a ini file, using call method.

General Format (syntax)

CALL  iniTool.exe   /f:[path of conifg File]  [/s:section]   [/k:key]
 /f: Path to an .ini format file.
 /s: Section name
 /k: Key name

Example>CALL .\INITOOL /f:".\Config.ini" /s:"Setup database" /k:"DBSERVER

/f: Config.ini is the File name

/k: DBserver is the Key name within the section

/s: Setup database

What is an SQLCMD (Sqlcmd) ?

SQLCMD is the command-line utility in SQL 2005 Server although the OSQL utility can be used side by side with SQLCMD. SQLCMD can be run both in the DOS screen as well as in the MS SQL Server 2005 Management Studio's query editor in the SQLCMD mode. If you want to use scripting, which brings in features like executing scripts, and others, this must be enabled. Enabling SQLCMD allows you to write SQL Commands as well as T-SQL Statements

Some of the Tags of SqlCmd Command line Utility:

usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-w screen width]        [-b On error batch abort] 
 [-I Enable Quoted Identifiers]  [-v var = "value"...] 
  [-c cmdend]            
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-Z new password and exit]

Use of For Loop in Batch File

Storing of Config file values to variables that are in batch File using a this For Loop.

Example: reading Database Name from the Config File, then [Database Name] value will be stored in Batch File variable.

Syntax:
        FOR /F ["options"] %%parameter IN (filenameset) DO command 
      
        FOR /F ["options"] %%parameter IN ("Text string to process") DO command
        
Keys:   
   options:
      delims = xxx The delimiter character(s) (default = a space)

      skip = n        Number of lines to skip at the beginning of the file (default = 0).
 
      eol=;             Character to indicate a comment (end of line)

      tokens=n       Specifies  which numbered items to read from each line (default = 1).
          
      usebackq     Specify `back quotes`:                        
                   - Use double quotes to quote long file names in filenameset.
                   - Use single quotes for 'Text string to process'
                     (useful if the text string contains double quotes)

   Filenameset :  A set of one or more files. Wildcards may be used. 
                  If (filenameset) is a period character (.) then FOR will
                  loop through every file in the folder.

   command:  The command to carry out, including any  command-line parameters.
  
   %%parameter :  A replaceable parameter:  in a batch file use  %%G (on the command line %G)

For /f: processing of each text file consists of reading the file one line of text at a time and then breaking the line up into individual items of data or 'tokens'. The DO command is then executed with the parameter(s) set to the token(s) found.

By default, /f: breaks up the line at each blank space, and any blank lines are skipped. You can override this default parsing behavior by specifying the "options" parameter. The options must be contained within "quotes"

Explanation of Code

You can create the .ini file by saving the file with the .INI file extension and batch file with .BAT extension. In the .ini file I am declaring the keyname like [Database Name] that need to be created, current Db server name(existing Db Server Name), the Log file path and Data file path where the .mdf and .ldf file to be created. You can use the

SP_Attach_DB 'DatabaseName','.mdf file path' to restore your Database.

Here Setting a section name to a [Setup Database] in the Config.ini file.

i am using the initool.exe to read the key values from the .ini file and then the Key values will be stored in a variable in a Batch File.

CALL .\INITOOL /f:".\Config.ini" /s:"Setup database" /k:"DBSERVER

Note:( . Specifies the currentdiresctory /s is the section name , /k is the the keyvalue, /f is the file path where initool.exe is peresent ) In the Batch file looping will be done using FOR loop, for reading a key values from Config.ini file and storing them in a variables that are in Batch file.

Example: i am looping each variable in the config file by using the for /f loop, here tokens=* specified in Batch file, will cause all items on each line to be processed. Each variable can be accessable using %variable Name% in Batch file.

master.dbo.sysdatabases :-Contains The information of The datbases that exists
                          in the Curent Dbserver Each dtabase is identified by Unique DBID
master.dbo.sysprocesses :-Contains information about the active server
                          process IDs (SPIDs) that are running on SQL Server.

Step 1: In our first step we will take the Script of existing Database and appending each CREATE and ALTER statements with Sqlcmd Utility as showned in Batch File.

If you right click on your Database --> ScriptDatabase --> Create To -->New QueryEditor Window

figure 1 will shows how to take an Creating of Database Scripts from the existing Database.

Step 2: In this step we will generate the script file of all the Tables, Views, Storedprocedure and Functions from the Existing Database(current DB) and save the .sql file with name in a folder Scripts.( e.g: I have saved my file with the name AllScripts.sql which contains all the Storedprocedure and views in the Folder C:\DBscripts\Scripts. )

right click on your Database --> Task ---> generate Scripts

figure2 will shows how to take an all Scrpits of the Database.

You can use the Sqlcmd -i [input file] to execute the Allscripts.sql in command prompt(This step was showned in Batch File).

Step 3: Run the script DataBaseDiagramGenerator.sql (i.e., located in C:\DBScripts\Scripts) in the Database you need to Backup Diagrams, this will generate the result, Copy paste and save the result file with a name SQl DataBaseDiagram.sql in the same Directory(i.e., C:\DBScripts\Scripts).

in the above script i am reading each diagram that is Stored in the table Sysdiagram of My existing Database with a while Loop

Database Diagram is Stored in Sysdiagrams table in SQL2005 Where as in SQL2000 its stored in dtoProperties, i am reading each of the diagrams than need to be copied from the existing Database to the New Database.

You can refer the below link to get the details on how to copy the Database Diagram from the Current Database.

DataBasediagramGenerator

Step 4: Copy paste the Folder Dbscripts in to the Drive in which Operating system is Loaded, in my System C Drive is where i have loaded the operating system.hence the path is C:\DBscripts.

Step 5: Change the Dbserver name with your existing Server name in config.ini file and you can specify the data file path and log file path in the Directory you wish(I have specified my path as C:\DBscripts\Data_LogPath).

Change the Name of the database to the name you want to Create in Config.ini file (e.g: DATABASE=MyDataBase3 )

Step 6: Open the Command Prompt by typing Comand cmd in run mode

figure 3

Step 7: You will be prompted with the command Promt above type cd\ ,Go to the drive where U have copied and stored DBscripts folder.enter in to the folder Dbscripts, type SetupDb in the command prompt.

C -->Dbscripts -->SetupDb

figure4

then, Database will be restored.

Note:- You can replace Sqlcmd With OSql cmd Util -V option is Not Supported in Osql Instead of That Ucan User -r {0|1}option

Conclusion

Now from the concepts provided in this article, You Can Restore your existing Database Stored Procedure, View, DatabaseDiagrams, Tables to the ProductionServer, making your restoration of your Database more simpler Using The BatchFile .