1. Introduction
Database setup using a batch file is simple and efficient tool. Our project has monthly releases. This tool is in use since the day we started development. We are using this in two ways.
1. To create a fresh database with Master data.
2. To execute latest db objects on the existing database after restoring the database.
This Batch file can be used for Windows and Mixed mode Sql Server authentications as well.
Here I discussed about mixed mode authentications in this paper. It is so simple to use it for window authentication as well. You can find the differences in the section 5.
2. How to setup a New Database
The following are the steps, which you need to follow
1. Create all the required database object script files individually and save all the .sql files in the following folders.
a. Tables
b. Stored Procedures
c. Views
d. Functions
e. Triggers
f. Any other db object based on the requirement
2. Follow the below folder structure to save all the above sql files
3. Open SQL Server Management studio and Create a new Database or restore the database using the existing backup file (.bak file)
4. Refer to Section 5 and open “DBSetup.txt” file and rename that file to “DBSetup.bat” and save the same in the parent folder (Folder name is “Database” in this example)
5. Once all Db objects are ready in the above mentioned folders then Open command prompt and follow the following steps.
6. Go to Start Run then type “cmd”
7. Navigate to the “Database” folder , where the batch file “DBSetup.bat” exists
8. Type the following string in the command prompt window (see this in the below image)
In the above image, DBSetup.bat is the batch file name
a. DBServerName is the Database Server IP Address or the Server Name
b. UserName and Password are the credentials, which are used for logging into the Database
c. Database Name is the name of the Database, which you want to create or restore.
d. Result.txt is the file name where the results of the execution of this batch file is logged.
We have used the following String in the above Batch File for my project.
9. Press enter and open “Result.txt” text file after the execution is completed and make sure that there are no error messages displayed in the text file.
10. To check the error messages, Use Ctrl+ Find to search for “msg”. If you find no occurrences of “msg” in the Result.txt file then the execution of all DB Objects is successful. Now you can start using the New DB with all latest DB objects.
3. How to Update the Data Using Batch File
The following are the steps, which you need to follow
1. Create SQL script files in separate .sql Files and save all the .sql files in the following folder under “DataFixes” folder.
i. Fixes
2. Use the below folder structure to save the above sql files in the “Fixes” folder.
3. Refer to Section 5 and open “DataFixesetup.txt” file and rename that file to “DataFixesSetup.bat” and save the same in the parent folder(Folder name is “DataFixes” in this example)
4. Once all SQL files are ready in the above mentioned folders then Open command prompt and follow the below steps.
5. Go to Start Run then type “cmd”
6. Navigate to the “DataFixes” folder , where the batch file “DataFixesSetup.bat” exists
7. Type the following string in the command prompt window (see this in the below image as well)
(If you need any clarity on these parameters please refer to Section-2.)
8. Press enter and open “FixesResult.txt” text file after the execution is completed and make sure that there are no error messages displayed in the text file.
9. To check the error messages, Use Ctrl+ Find to search for “msg”. If you find no occurrences of “msg” in the FixesResult.txt file then the execution of all SQL scripts is successful. Now you can see the updated data in the tables.
4. Advantages
1. User can create a new database in very less time
2. Flexible and easily maintainable
3. User can create a fresh Database with Master data scripts to set up an environment for the testing team
4. Miscellaneous SQL scripts can also be executed using this batch file.
5. When the user wants to execute “n” number of SQL scripts then there is no need to execute them one by one. User can create a batch file with the order of the SQL scripts execution and run the batch file to execute all the SQL scripts in the specified order.
6. User doesn’t need to remember about the order of the execution of all objects. Once the order of the DB Objects Execution is set in the batch file then anyone can execute the batch file to update the Db objects or SQL scripts easily.
5. Code
The following command is used in all the places in this batch file to execute the script.
sqlcmd -S %DB_S% -d %InstallDB% -U %DB_U% -P %DB_P% -i ".\FolderName\FileName.sql"
if %ERRORLEVEL% NEQ 0 goto errors
In the above code,
%DB_S% represents the Database server name, which we supply as a parameter
%InstallDB% represents the Database name, which we supply as a parameter
%DB_U% represents the User name, which we supply as a parameter
%DB_P% represents the Password, which we supply as a parameter
If you don’t want to use mixed mode authentication then you don’t need to supply user Name and password to the batch files.
Use the following files to create new Database or to execute latest object on to the restored DB with windows Authentication.
Use the following files to create new Database or to execute latest object on to the restored DB with mixed mode Authentication.
In the above files you will find one entry for Grant Permissions script, which is nothing but another sql file, where you need to grant the permissions to the User. So create a Grant Permissions Script sql and place the same under “Grant Permissions” folder before execution. See the code below
GRANT EXEC ON usp_StoredProcedure1 TO sa
GRANT SELECT ON vw_view1 TO sa
6. Points to Remember
1. Make sure the permissions are available for the current db user
2. Make sure the DB name and the credentials are correct
3. Take care of the dependent objects. Usually some of the Stored Procedures are dependent on Views. So View need to get executed first. Also some of the Views are interdependent. So the main view should get executed first then the dependent views to be executed. In such cases the view should be referred first in the batch file then the views from the “Views” folder can be executed
7. References
We have used this extensively in our current Project “AIS”
8. About the Author
G. Murali Mohana Rao is Leading AIS project from Offshore.
9. About Wipro Technologies
Wipro is the first PCMM Level 5 and SEI CMMi Level 5 certified IT Services Company globally. Wipro provides comprehensive IT solutions and services (including systems integration, IS outsourcing, package implementation, software application development and maintenance) and Research & Development services (hardware and software design, development and implementation) to corporations globally.
Wipro's unique value proposition is further delivered through our pioneering Offshore Outsourcing Model and stringent Quality Processes of SEI and Six Sigma.
© Copyright 2002. Wipro Technologies. All rights reserved. No part of this document may be reproduced, stored in a retrieval system, transmitted in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without express written permission from Wipro Technologies. Specifications subject to change without notice. All other trademarks mentioned herein are the property of their respective owners. Specifications subject to change without notice.
No comments:
Post a Comment