Firstly, let's see the pre-defined sql script, which was used to create one database. For more detailed information about the script, you can read from here. Note the script has been changed to be run under sqlcmd as the script can accept 4 sqlcmd parameters named
$(varDropDB), $(varDBName), $(varDBFileDirectory), @DBBAKFileDirectory.
Note there are single quotas while referencing those 4 parameters. This is because the sqlcmd just simply replaces those 4 parameters with their values like windows command, if you missed single quota, executing the script with sqlcmd would result mistake.
DECLARE @DeleteExistingDB VARCHAR(10)
DECLARE @DBName VARCHAR(20)
DECLARE @DBFileDirectory VARCHAR(100)
DECLARE @DBBAKFileDirectory VARCHAR(100)
DECLARE @SQL_SCRIPT VARCHAR(MAX)
DECLARE @NewUser VARCHAR(20)
DECLARE @NewUserPassword VARCHAR(20)
SET @NewUser = '$(varNewUser)' -- 'test'
SET @NewUserPassword = '$(varNewUserPassword)' -- 'Test123'
SET @DeleteExistingDB = '$(varDropDB)' -- 'TRUE'
SET @DBName = '$(varDBName)' -- 'TestDB'
SET @DBFileDirectory = '$(varDBFileDirectory)' -- 'D:\Data'
SET @DBBAKFileDirectory = '$(varDBBAKFileDirectory)' -- 'D:\Data\Backup'
IF Exists(Select * From sys.databases Where name = @DBName) AND @DeleteExistingDB = 'TRUE'
BEGIN
USE [master]
/* BACKUP DATABASE */
SET @SQL_SCRIPT = N'BACKUP DATABASE {{DWDBNAME}} TO DISK = N' + CHAR(39) + @DBBAKFileDirectory
+ '\{{DWDBNAME}}_' + CONVERT(char(8), GetDate(),112) + '_bySSIS.bak' + CHAR(39)
+ ' WITH NOFORMAT, SKIP, NOREWIND, NOUNLOAD, STATS = 10' collate Latin1_General_CI_AS
SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS, @DBName)
-- SELECT @SQL_SCRIPT collate Latin1_General_CI_AS
EXECUTE (@SQL_SCRIPT)
/* Delete Database Backup and Restore History from MSDB System Database */
SET @SQL_SCRIPT = 'EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = ''' + @DBName + ''''
-- SELECT @SQL_SCRIPT collate Latin1_General_CI_AS
EXECUTE (@SQL_SCRIPT)
/* Query to Get Exclusive Access of SQL Server Database before Dropping the Database */
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
-- SELECT @SQL_SCRIPT collate Latin1_General_CI_AS
EXECUTE (@SQL_SCRIPT)
/*Drop the DB*/
SET @SQL_SCRIPT = 'DROP DATABASE ' + @DBName
-- SELECT @SQL_SCRIPT collate Latin1_General_CI_AS
EXECUTE (@SQL_SCRIPT)
END
IF NOT Exists(Select * From sys.databases Where name = @DBName)
BEGIN
/* Create New Genuina Data Mart DB */
SET @SQL_SCRIPT =
'CREATE DATABASE {{DWDBNAME}} CONTAINMENT = NONE ON PRIMARY
(
NAME = ' + CHAR(39) + '{{DWDBNAME}}' collate Latin1_General_CI_AS + CHAR(39) + ', FILENAME = N' + CHAR(39) + @DBFileDirectory
+ '\{{DWDBNAME}}.mdf' + CHAR(39) + ', SIZE = 262144KB , MAXSIZE = UNLIMITED, FILEGROWTH = 12800KB
)
LOG ON
(
NAME = N' + CHAR(39) + '{{DWDBNAME}}_log' + CHAR(39) + ', FILENAME = N' + CHAR(39) + @DBFileDirectory
+ '\{{DWDBNAME}}_log.ldf' + CHAR(39) + ', SIZE = 65536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%
)' collate Latin1_General_CI_AS
SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{{DWDBNAME}}' collate Latin1_General_CI_AS, @DBName)
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET COMPATIBILITY_LEVEL = 90;'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT =
'IF (1 = FULLTEXTSERVICEPROPERTY(' + CHAR(39) + 'IsFullTextInstalled' + CHAR(39) +'))
BEGIN
EXEC ' + @DBName + '.[dbo].[sp_fulltext_database] @action = ' + CHAR(39) + 'enable' + CHAR(39) + '
END'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET ANSI_NULL_DEFAULT OFF'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET ANSI_NULLS OFF'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET ANSI_PADDING OFF'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET ANSI_WARNINGS OFF'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET ARITHABORT OFF'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET AUTO_CLOSE OFF'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET AUTO_CREATE_STATISTICS ON'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET AUTO_SHRINK ON'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET AUTO_UPDATE_STATISTICS ON'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET CURSOR_CLOSE_ON_COMMIT OFF'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET CURSOR_DEFAULT GLOBAL'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET CONCAT_NULL_YIELDS_NULL OFF'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET NUMERIC_ROUNDABORT OFF'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET QUOTED_IDENTIFIER OFF'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET RECURSIVE_TRIGGERS OFF'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET DISABLE_BROKER'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET AUTO_UPDATE_STATISTICS_ASYNC ON'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET DATE_CORRELATION_OPTIMIZATION OFF'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET TRUSTWORTHY OFF'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET ALLOW_SNAPSHOT_ISOLATION OFF'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET PARAMETERIZATION SIMPLE'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET READ_COMMITTED_SNAPSHOT OFF'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET HONOR_BROKER_PRIORITY OFF'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY SIMPLE'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET MULTI_USER'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET PAGE_VERIFY TORN_PAGE_DETECTION'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET DB_CHAINING OFF'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET TARGET_RECOVERY_TIME = 0 SECONDS'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'ALTER DATABASE ' + @DBName + ' SET READ_WRITE'
-- SELECT @SQL_SCRIPT
EXECUTE (@SQL_SCRIPT)
END
-- Create a user with a password from parameter
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = @NewUser)
BEGIN
EXEC sp_dropuser @NewUser
END
IF Exists (SELECT loginname FROM master.dbo.syslogins WHERE name = @NewUser)
BEGIN
SET @SQL_SCRIPT = 'DROP LOGIN [' + @NewUser + ']'
-- SELECT @SQL_SCRIPT
EXEC (@SQL_SCRIPT)
END
SET @SQL_SCRIPT = 'CREATE LOGIN [' + @NewUser + '] WITH PASSWORD=''' + @NewUserPassword +''''
-- SELECT @SQL_SCRIPT
EXEC (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'USE ' + @DBName + '; CREATE USER [' + @NewUser + '] FOR LOGIN [' + @NewUser + '];'
-- SELECT @SQL_SCRIPT
EXEC (@SQL_SCRIPT)
SET @SQL_SCRIPT = 'USE ' + @DBName + '; GRANT SELECT TO [' + @NewUser +'];'
-- SELECT @SQL_SCRIPT
EXEC (@SQL_SCRIPT)
GO
Save above sql script as create_database.sql.Next let's check the windows command which would load sqlcmd with parameters.
We want our windows command accepting parameters as well to make it flexible. Here %1 to %7 referring windows command parameters.
Also 3 sqlcmd parameters are applied here: -v means defining parameters used for sql script, -S means to connect the database server with server name or IP address, -i means executing the sql script file we pre-defined.
@ECHO OFF
IF [%1]==[/?] GOTO HELP
IF [%1]=="" GOTO NOENOUGHPARAMETERS
IF [%2]=="" GOTO NOENOUGHPARAMETERS
IF [%3]=="" GOTO NOENOUGHPARAMETERS
IF [%4]=="" GOTO NOENOUGHPARAMETERS
IF [%5]=="" GOTO NOENOUGHPARAMETERS
IF [%6]=="" GOTO NOENOUGHPARAMETERS
SET db_name="%1"
ECHO db_name=%db_name%
SET db_file_directory="%2"
ECHO db_file_directory=%db_file_directory%
SET db_bakfile_directory="%3"
ECHO db_bakfile_directory=%db_bakfile_directory%
SET user_name="%4"
ECHO user_name=%user_name%
SET user_password="%5"
ECHO user_password=%user_password%
SET server_name=%6
ECHO server_name=%server_name%
IF "%7"=="" (
SET server_port=1433
) ELSE (
SET server_port=%7
)
SET server_name=%server_name%,%server_port%
ECHO server_name=%server_name%
SET PATH=%PATH%; C:\Program Files\Microsoft SQL Server\110\Tools\Binn
sqlcmd -v varDropDB=TRUE varDBName=%db_name% varDBFileDirectory=%db_file_directory% varDBBAKFileDirectory=%db_bakfile_directory% varNewUser=%user_name% varNewUserPassword=%user_password% -S %server_name% -i create_database.sql
ECHO Database %db_name% has created successfully.
GOTO DONE
:NOENOUGHPARAMETERS
ECHO You need at least 6 Parameters to finish the task
GOTO HELP
:HELP
ECHO This batch file is used to create Database, it applied sqlcmd which installed under the directory C:\Program Files\Microsoft SQL Server\110\Tools\Binn
ECHO This batch file required at least 6 parameters, if the 7th parameter, Server Port Number, is missing, a default port number 1433 would be used instead
ECHO Usage: install_dm_database DBName DBFileDirectory DBBakFileDirectory NewUsername NewUserPassword ServerName ServerPortNumber
ECHO Example: install_database.bat TestDB B:\Data B:\Data\Backup test Test123 192.168.99.22 1433
:DONE
No comments:
Post a Comment