Needless to mention, we come across instances when we want to automate certain SQL Tasks. We might be in need of the automation because we might want to accomplish some repeated work, or there is a requirement as part of the project.
Below are some of the tasks that I had to automate while working on a project. I am more onto SQL Server, so VBScripting is not my primary skill. I had to learn (ofcourse from the Internet - so you might find some examples here, elsewhere as well) and implement. I learnt that VBScript is a very powerful means of doing this. I hope once you have gone through this, you will agree with me.
' ------------------------------------------------------------------------------------------------------------
'1. Connect to SQL Server
' ------------------------------------------------------------------------------------------------------------
sServer = "."
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.LoginSecure = True
oSQLServer.Connect sServer
'/*********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'2. Back up Database
' ------------------------------------------------------------------------------------------------------------
sServer = "."
sDatabase = "Northwind"
rootDir = "D:\Backup\"
sDumpFile = "Northwind.bak"
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.LoginSecure = True
oSQLServer.Connect sServer
Set oBackup = CreateObject("SQLDMO.BackUp")
oBackup.Database = sDatabase
oBackup.Files = rootDir & sDumpFile
oBackUp.BackupSetDescription = "Backup - " & sDatabase
oBackup.Initialize = "TRUE"
oBackup.SQLBackup oSQLServer
'/*********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'3. Restore Database
' ------------------------------------------------------------------------------------------------------------
sServer = "."
sDatabase = "Northwind"
rootDir = "D:\Backup"
DataFileLoc = "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL"
LogFileLoc = "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL"
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.LoginSecure = True
oSQLServer.Connect sServer
Set oRestore = CreateObject("SQLDMO.Restore")
oRestore.Action = SQLDMORestore_Database
oRestore.Database = sDatabase
oRestore.ReplaceDatabase = True
oRestore.Files = rootDir & "\Northwind.bak"
oRestore.RelocateFiles = "[Northwind],[" & DataFileLoc &"\Data\Northwind.mdf], [Northwind_log],[" &LogFileLoc &"\Data\Northwind_1.ldf]"
oRestore.FileNumber = 1
oRestore.SQLRestore oSQLServer
'/********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'4. Run a .sql file
' ------------------------------------------------------------------------------------------------------------
sServer = "."
sScriptPath = "D:\SQLScripts"
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "sqlcmd -S " & sServer & " -d Northwind -E -i """& "" & sScriptPath&"\CreateTable.sql"""
'/********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'5. Set single user mode and Detach DB
' ------------------------------------------------------------------------------------------------------------
sServer = "."
sDatabase = "Northwind"
Set WshShell = WScript.CreateObject("WScript.Shell")
sAltDBCmd = "sqlcmd -S " & sServer & " -d master -E -Q""ALTER DATABASE " & sDatabase & " SET SINGLE_USER WITH ROLLBACK IMMEDIATE"""
sCmdDet1 = "sqlcmd -S " & sServer & " -E -Q""EXEC sp_detach_db '" & sDatabase & "','TRUE' "" "
ObjAltDBExcode1 = WshShell.Run(sAltDBCmd,5,True)
ObjDetachExcode1 = WshShell.Run(sCmdDet1,5,True)
'/********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'6. Attach a Database
' ------------------------------------------------------------------------------------------------------------
sServer = "."
sDatabase = "Northwind"
DataFileLoc = "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\"
LogFileLoc = "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\"
Set WshShell = WScript.CreateObject("WScript.Shell")
sCmd1 = "sqlcmd -S " & sServer & " -E -Q""EXEC sp_attach_db @dbname= '" & sDatabase & "', @filename1 = '"&DataFileLoc&"Northwind.mdf',@filename2 = '"&LogFileLoc&"Northwind_1.LDF'"" "
ObjAttachExcode1 = WshShell.Run(sCmd1,5,True)
'/*******************************************************************************--
'-----------------------------------------------------------------------------------------------------------
'7. Script out the SQL Agent Jobs
' ------------------------------------------------------------------------------------------------------------
sServer = "."
sbackupLoc = "D:\backup\"
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set fso = CreateObject("scripting.filesystemobject")
oSQLServer.LoginSecure = True
oSQLServer.Connect sServer
Set objDB = oSQLServer.Databases
Set objJob = oSQLServer.JobServer
If NOT(fso.FolderExists(sbackupLoc & "\Jobs\")) Then
fso.CreateFolder(sbackupLoc & "\Jobs\")
End If
For each job in objJob.Jobs
sFileName = sbackupLoc & "\" & "\Jobs\" & Replace(job.Name, "\", "-") & ".sql"
job.Script 327, sfileName
Next
'/********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'8. Change the Collation of the SQL Server Instance
' ------------------------------------------------------------------------------------------------------------
sServer = "."
Set oSQLServer2 = CreateObject("SQLDMO.SQLServer2")
Set WshShell = WScript.CreateObject("WScript.Shell")
oSQLServer2.LoginSecure = True
oSQLServer2.Connect sServer
If oSQLServer2.Collation = "SQL_Latin1_General_CP1_CI_AS" Then
MsgBox ("No action required; this is the required collation")
Else
MsgBox ("Changing the collation to SQL_Latin1_General_CP1_CI_AS")
sCommand = ""& Chr(34) & "C:\Program Files\MSSQLSOURCE\setup.exe" & Chr(34) & "/qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS ASCOLLATION=SQL_Latin1_General_CP1_CI_AS"
On Error Resume Next
ColSetExcode = WshShell.Run(sCommand,5,True) 'Executing the command
If ColSetExcode <> 0 Then
MsgBox ("Could not set the new collation " & ColSetExcode)
Else
MsgBox ("Collation set successfully to SQL_Latin1_General_CP1_CI_AS")
End If
End If
'/********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'9. Function to check the Successful Installation of SQL Server 2005
' ------------------------------------------------------------------------------------------------------------
CheckSQL2005Install()
Function CheckSQL2005Install()
On Error Resume Next
Set WshShell = WScript.CreateObject("WScript.Shell")
'***********************************************
' Check for the Registry key of the Data Folder
'***********************************************
WshShell.Popup "Checking the Registry key",5,"SQLUpgrade",64
DataRoot = WshShell.RegRead ("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\SQLDataRoot")
If Err.Number <> 0 Then
Installation = 1
Else
Installation = 0
End If
On Error Resume Next
Set fsoFileSystem = CreateObject("scripting.filesystemobject")
If Err.Number <> 0 Then
Wscript.Echo "Cannot create FileSystem Object... Hence Quitting"
Wscript.Quit()
End If
Set ofolder = fsoFileSystem.GetFolder(DataRoot)
If Err.Number <> 0 Then
Wscript.Echo "Invalid SQL Server folder location Specified.. Quitting.."
Installation = 1
End If
'*****************************************************************
' Check for the Data and Transaction log files in the data folder
'*****************************************************************
Set ofiles = ofolder.Files
For Each objFile In ofiles
sType = objFile.Type
If ( sType = "SQL Server Database Primary Data File" Or "SQL Server Database Transaction Log File") Then
Installation = 0
Else
Installation = 1
End If
Next
'***********************************************
' Check whether or not the services are running
'***********************************************
WshShell.Popup "Checking SQL Server Services..",5,"SQLUpgrade",64
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & sServer & "\root\cimv2")
Set colRunningServices = objWMIService.ExecQuery _
("Select * from Win32_Service where Name = 'MSSQLSERVER'")
For Each objService in colRunningServices
If objService.State = "Running" Then
Installation = 0
Set colRunningServices2 = objWMIService.ExecQuery _
("Select * from Win32_Service where Name = 'SQLSERVERAGENT'")
For Each objService2 in colRunningServices2
If objService2.State = "Running" Then
Installation = 0
Else
WshShell.Popup "SQL Server Agent Service not is Running, Starting the Service...",6,"SQLUpgrade",64
sCommand2 = objService2.StartService()
ObjExcode = WshShell.Run(sCommand2,5,WaitOnReturn) 'Executing the command
If ObjExcode <> 0 Then
Wscript.Echo "Could not start SQL Server Agent Service" & Err.Description
Else
WshShell.Popup "SQL Server Agent Service started successfully",4,"SQLUpgrade",64
End If
End If
Next
Else
WshShell.Popup "SQL Server Service not is Running, Starting the Service...",6,"SQLUpgrade",64
sCommand = objService.StartService()
ObjExcode = WshShell.Run(sCommand,5,WaitOnReturn) 'Executing the command
If ObjExcode <> 0 Then
Wscript.Echo "Could not start SQL Server Service" & Err.Description
Else
WshShell.Popup "SQL Server Service started sucessfully",4,"SQLUpgrade",64
End If
Set colRunningServices2 = objWMIService.ExecQuery _
("Select * from Win32_Service where Name = 'SQLSERVERAGENT'")
For Each objService2 in colRunningServices2
If objService2.State = "Running" Then
Installation = 0
Else
WshShell.Popup "SQL Server Agent Service not is Running, Starting the Service...",6,"SQLUpgrade",64
sCommand2 = objService2.StartService()
ObjExcode = WshShell.Run(sCommand2,5,WaitOnReturn) 'Executing the command
If ObjExcode <> 0 Then
Wscript.Echo "Could not start SQL Server Agent Service" & Err.Description
Else
WshShell.Popup "SQL Server Agent Service started successfully",4,"SQLUpgrade",64
End If
End If
Next
End If
Next
CheckSQL2005Install = Installation
If CheckSQL2005Install = 0 Then
MsgBox("SQL Server 2005 is installed Successfully")
Else
MsgBox("SQL Server 2005 not installed properly, or could not check")
End If
End Function
'/********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'10. Import SSIS packages into SSIS package Store - into Maintenance Plan - since the below is a maintenance plan
' ------------------------------------------------------------------------------------------------------------
sPackagePath = "D:\SQL\scripts" 'make sure this path has the package file ReIndexPlan.dtsx
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "dtutil /FILE " & sPackagePath& "\ReIndexPlan.dtsx /COPY DTS;""MSDB\Maintenance Plans\ReIndexPlan"""
' ------------------------------------------------------------------------------------------------------------
'1. Connect to SQL Server
' ------------------------------------------------------------------------------------------------------------
sServer = "."
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.LoginSecure = True
oSQLServer.Connect sServer
'/*********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'2. Back up Database
' ------------------------------------------------------------------------------------------------------------
sServer = "."
sDatabase = "Northwind"
rootDir = "D:\Backup\"
sDumpFile = "Northwind.bak"
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.LoginSecure = True
oSQLServer.Connect sServer
Set oBackup = CreateObject("SQLDMO.BackUp")
oBackup.Database = sDatabase
oBackup.Files = rootDir & sDumpFile
oBackUp.BackupSetDescription = "Backup - " & sDatabase
oBackup.Initialize = "TRUE"
oBackup.SQLBackup oSQLServer
'/*********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'3. Restore Database
' ------------------------------------------------------------------------------------------------------------
sServer = "."
sDatabase = "Northwind"
rootDir = "D:\Backup"
DataFileLoc = "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL"
LogFileLoc = "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL"
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.LoginSecure = True
oSQLServer.Connect sServer
Set oRestore = CreateObject("SQLDMO.Restore")
oRestore.Action = SQLDMORestore_Database
oRestore.Database = sDatabase
oRestore.ReplaceDatabase = True
oRestore.Files = rootDir & "\Northwind.bak"
oRestore.RelocateFiles = "[Northwind],[" & DataFileLoc &"\Data\Northwind.mdf], [Northwind_log],[" &LogFileLoc &"\Data\Northwind_1.ldf]"
oRestore.FileNumber = 1
oRestore.SQLRestore oSQLServer
'/********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'4. Run a .sql file
' ------------------------------------------------------------------------------------------------------------
sServer = "."
sScriptPath = "D:\SQLScripts"
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "sqlcmd -S " & sServer & " -d Northwind -E -i """& "" & sScriptPath&"\CreateTable.sql"""
'/********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'5. Set single user mode and Detach DB
' ------------------------------------------------------------------------------------------------------------
sServer = "."
sDatabase = "Northwind"
Set WshShell = WScript.CreateObject("WScript.Shell")
sAltDBCmd = "sqlcmd -S " & sServer & " -d master -E -Q""ALTER DATABASE " & sDatabase & " SET SINGLE_USER WITH ROLLBACK IMMEDIATE"""
sCmdDet1 = "sqlcmd -S " & sServer & " -E -Q""EXEC sp_detach_db '" & sDatabase & "','TRUE' "" "
ObjAltDBExcode1 = WshShell.Run(sAltDBCmd,5,True)
ObjDetachExcode1 = WshShell.Run(sCmdDet1,5,True)
'/********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'6. Attach a Database
' ------------------------------------------------------------------------------------------------------------
sServer = "."
sDatabase = "Northwind"
DataFileLoc = "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\"
LogFileLoc = "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\"
Set WshShell = WScript.CreateObject("WScript.Shell")
sCmd1 = "sqlcmd -S " & sServer & " -E -Q""EXEC sp_attach_db @dbname= '" & sDatabase & "', @filename1 = '"&DataFileLoc&"Northwind.mdf',@filename2 = '"&LogFileLoc&"Northwind_1.LDF'"" "
ObjAttachExcode1 = WshShell.Run(sCmd1,5,True)
'/*******************************************************************************--
'-----------------------------------------------------------------------------------------------------------
'7. Script out the SQL Agent Jobs
' ------------------------------------------------------------------------------------------------------------
sServer = "."
sbackupLoc = "D:\backup\"
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set fso = CreateObject("scripting.filesystemobject")
oSQLServer.LoginSecure = True
oSQLServer.Connect sServer
Set objDB = oSQLServer.Databases
Set objJob = oSQLServer.JobServer
If NOT(fso.FolderExists(sbackupLoc & "\Jobs\")) Then
fso.CreateFolder(sbackupLoc & "\Jobs\")
End If
For each job in objJob.Jobs
sFileName = sbackupLoc & "\" & "\Jobs\" & Replace(job.Name, "\", "-") & ".sql"
job.Script 327, sfileName
Next
'/********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'8. Change the Collation of the SQL Server Instance
' ------------------------------------------------------------------------------------------------------------
sServer = "."
Set oSQLServer2 = CreateObject("SQLDMO.SQLServer2")
Set WshShell = WScript.CreateObject("WScript.Shell")
oSQLServer2.LoginSecure = True
oSQLServer2.Connect sServer
If oSQLServer2.Collation = "SQL_Latin1_General_CP1_CI_AS" Then
MsgBox ("No action required; this is the required collation")
Else
MsgBox ("Changing the collation to SQL_Latin1_General_CP1_CI_AS")
sCommand = ""& Chr(34) & "C:\Program Files\MSSQLSOURCE\setup.exe" & Chr(34) & "/qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS ASCOLLATION=SQL_Latin1_General_CP1_CI_AS"
On Error Resume Next
ColSetExcode = WshShell.Run(sCommand,5,True) 'Executing the command
If ColSetExcode <> 0 Then
MsgBox ("Could not set the new collation " & ColSetExcode)
Else
MsgBox ("Collation set successfully to SQL_Latin1_General_CP1_CI_AS")
End If
End If
'/********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'9. Function to check the Successful Installation of SQL Server 2005
' ------------------------------------------------------------------------------------------------------------
CheckSQL2005Install()
Function CheckSQL2005Install()
On Error Resume Next
Set WshShell = WScript.CreateObject("WScript.Shell")
'***********************************************
' Check for the Registry key of the Data Folder
'***********************************************
WshShell.Popup "Checking the Registry key",5,"SQLUpgrade",64
DataRoot = WshShell.RegRead ("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\SQLDataRoot")
If Err.Number <> 0 Then
Installation = 1
Else
Installation = 0
End If
On Error Resume Next
Set fsoFileSystem = CreateObject("scripting.filesystemobject")
If Err.Number <> 0 Then
Wscript.Echo "Cannot create FileSystem Object... Hence Quitting"
Wscript.Quit()
End If
Set ofolder = fsoFileSystem.GetFolder(DataRoot)
If Err.Number <> 0 Then
Wscript.Echo "Invalid SQL Server folder location Specified.. Quitting.."
Installation = 1
End If
'*****************************************************************
' Check for the Data and Transaction log files in the data folder
'*****************************************************************
Set ofiles = ofolder.Files
For Each objFile In ofiles
sType = objFile.Type
If ( sType = "SQL Server Database Primary Data File" Or "SQL Server Database Transaction Log File") Then
Installation = 0
Else
Installation = 1
End If
Next
'***********************************************
' Check whether or not the services are running
'***********************************************
WshShell.Popup "Checking SQL Server Services..",5,"SQLUpgrade",64
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & sServer & "\root\cimv2")
Set colRunningServices = objWMIService.ExecQuery _
("Select * from Win32_Service where Name = 'MSSQLSERVER'")
For Each objService in colRunningServices
If objService.State = "Running" Then
Installation = 0
Set colRunningServices2 = objWMIService.ExecQuery _
("Select * from Win32_Service where Name = 'SQLSERVERAGENT'")
For Each objService2 in colRunningServices2
If objService2.State = "Running" Then
Installation = 0
Else
WshShell.Popup "SQL Server Agent Service not is Running, Starting the Service...",6,"SQLUpgrade",64
sCommand2 = objService2.StartService()
ObjExcode = WshShell.Run(sCommand2,5,WaitOnReturn) 'Executing the command
If ObjExcode <> 0 Then
Wscript.Echo "Could not start SQL Server Agent Service" & Err.Description
Else
WshShell.Popup "SQL Server Agent Service started successfully",4,"SQLUpgrade",64
End If
End If
Next
Else
WshShell.Popup "SQL Server Service not is Running, Starting the Service...",6,"SQLUpgrade",64
sCommand = objService.StartService()
ObjExcode = WshShell.Run(sCommand,5,WaitOnReturn) 'Executing the command
If ObjExcode <> 0 Then
Wscript.Echo "Could not start SQL Server Service" & Err.Description
Else
WshShell.Popup "SQL Server Service started sucessfully",4,"SQLUpgrade",64
End If
Set colRunningServices2 = objWMIService.ExecQuery _
("Select * from Win32_Service where Name = 'SQLSERVERAGENT'")
For Each objService2 in colRunningServices2
If objService2.State = "Running" Then
Installation = 0
Else
WshShell.Popup "SQL Server Agent Service not is Running, Starting the Service...",6,"SQLUpgrade",64
sCommand2 = objService2.StartService()
ObjExcode = WshShell.Run(sCommand2,5,WaitOnReturn) 'Executing the command
If ObjExcode <> 0 Then
Wscript.Echo "Could not start SQL Server Agent Service" & Err.Description
Else
WshShell.Popup "SQL Server Agent Service started successfully",4,"SQLUpgrade",64
End If
End If
Next
End If
Next
CheckSQL2005Install = Installation
If CheckSQL2005Install = 0 Then
MsgBox("SQL Server 2005 is installed Successfully")
Else
MsgBox("SQL Server 2005 not installed properly, or could not check")
End If
End Function
'/********************************************************************************
' ------------------------------------------------------------------------------------------------------------
'10. Import SSIS packages into SSIS package Store - into Maintenance Plan - since the below is a maintenance plan
' ------------------------------------------------------------------------------------------------------------
sPackagePath = "D:\SQL\scripts" 'make sure this path has the package file ReIndexPlan.dtsx
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "dtutil /FILE " & sPackagePath& "\ReIndexPlan.dtsx /COPY DTS;""MSDB\Maintenance Plans\ReIndexPlan"""
No comments:
Post a Comment