Friday, October 9, 2015

Applying Windows command and SQLCMD to execute sql script

SQL Server has one very useful tool named sqlcmd which can execute pre-defined sql script file. You can use sqlcmd along with windows batch command to add parameters to your sql script so that it can be used more flexible.

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