Check in New DDL to SourceSafe
Rather than invent (or learn) a whole new set of tools to preserve and version all DDL underneath our databases, we decided to use SourceSafe (something we're already using for all other source code). -- We've developed this VBscript to run nightly; it recreates object-level DDL in a given database and compares each object's script to its counterpart in a SourceSafe database. Should the two differ, this script checks in the database's (newer) version.
To prepare this script for execution:
- Set up a SourceSafe database, with a DATABASE project under the root, and subprojects underneath it for each server/database combination you want to preserve. (Example: "$/DATABASE/SERVER1_HR_Database".)
- Set up subprojects (Tables, Views, StoredProcedures, etc.) underneath each new server/database project built above.
- Replace all [square-bracketed] items in the script with your own server names, IDs and passwords.
'===============================================================================
'- generate scripts to rebuild all objects in [SQL server name]/[SQL database name]
'- check scripts into SourceSafe whenever current script is different than VSS's
'
'Revisions: When, Who, Why
' 03/20/2001Rich Cowley (cowley_rich@hotmail.com)Original Version
'===============================================================================
'General Constants
Const SCRIPT_DIRECTORY = "c:\WorkingDirectory\CheckInNewSQLDDL" 'working directory on local machine
'SourceSafe Constants
Const VSS_ROOTPROJECT_NAME = "$/"
Const VSS_SUBPROJECT_NAME = "DATABASE/[SQL server name]_[SQL database name]/"
Const VSS_INI_PATH = "\\[SourceSafe server name]\[SourceSafe path]\srcsafe.ini"
Const VSS_USERNAME = "[SourceSafe administrator name]"
Const VSS_PASSWORD = "[SourceSafe administrator password]"
'SQL DMO Constants
Const SQL_SERVERNAME = "[SQL server name]"
Const SQL_USERNAME = "[SQL administrator name]"
Const SQL_PASSWORD = "[SQL administrator password]"
Const SQL_DBNAME = "[SQL database name]"
Const SQLDMOScript_Default = 4
Const SQLDMOScript_Drops = 1
Const SQLDMOScript_Triggers = 16
Const SQLDMOScript_Indexes = 73736
'FileSystemObject constants
Const ForReading = 1
Const ForWriting = 2
Dim oSQLServer 'As SQLDMO.SQLServer2
Dim oDatabase 'As SQLDMO.Database2
Dim oDatabaseObject
Dim sScript 'As String
Dim oFSO 'As Scripting.FileSystemObject
Dim oFolder 'As Folder
Dim oFile 'As File
Dim oTS 'As Scripting.TextStream
Dim sCurrDirectory 'As String
Dim sFileName 'As String
Dim i 'As Integer
Dim iScriptOptions 'As Integer
Dim sObjectType 'As String
Dim oVSSDatabase 'As SourceSafeTypeLib.VSSDatabase
Dim oVSSItem 'As VSSItem
Dim sVSSItemPath 'As String
Dim sVSSLabel 'As String 'label used when checking items back in
Dim sCheckedScript 'As String
Dim sTempScript1 'As String
Dim sTempScript2 'As String
'=======================
'Script every object in the database (tables, views, SPs, etc.)
'If current script is different than SourceSafe's, check in new version.
'Add new SourceSafe item if current script was not on SourceSafe to begin with.
'=======================
'set up SourceSafe environment
Set oVSSDatabase = wScript.CreateObject("SourceSafe")
oVSSDatabase.Open VSS_INI_PATH, VSS_USERNAME, VSS_PASSWORD
'establish SQL Server and FSO environments
Set oSQLServer = wScript.CreateObject("SQLDMO.SQLServer2")
oSQLServer.Connect SQL_SERVERNAME, SQL_USERNAME, SQL_PASSWORD
Set oDatabase = oSQLServer.Databases(SQL_DBNAME)
Set oFSO = wScript.CreateObject("Scripting.FileSystemObject")
'--------
'tables
'--------
sObjectType = "Tables"
sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/"
Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
'set up clean working directory
sCurrDirectory = SCRIPT_DIRECTORY & "\" & sObjectType
On Error Resume Next
If oFSO.FolderExists(sCurrDirectory) Then
oFSO.DeleteFolder sCurrDirectory, True
End If
Set oFolder = oFSO.CreateFolder(sCurrDirectory)
'cycle through the objects
For Each oDatabaseObject In oDatabase.Tables
If oDatabaseObject.SystemObject Then
'do nothing (bypass system objects)
Else
iScriptOptions = SQLDMOScript_Default + SQLDMOScript_Drops + SQLDMOScript_Triggers + SQLDMOScript_Indexes
sScript = oDatabaseObject.Script(iScriptOptions)
sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/" & oDatabaseObject.Name & ".sql"
sFileName = sCurrDirectory & "\" & oDatabaseObject.Name & ".sql"
On Error Resume Next
Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
If Err = 0 Then 'item is already on SourceSafe
oVSSItem.Checkout "Checked out by automated process", sFileName
Set oTS = oFSO.OpenTextFile(sFileName, ForReading)
sCheckedScript = oTS.Read(1000000)
oTS.Close
'scrub the two scripts (remove any trailing CrLfs) and compare them
sTempScript1 = Trim(Replace(sCheckedScript, Chr(13) & Chr(10), " "))
sTempScript2 = Trim(Replace(sScript, Chr(13) & Chr(10), " "))
If sTempScript1 = sTempScript2 Then
'new script is identical to its last check-in version
oVSSItem.UndoCheckout
Else 'script has changed since its last check-in
'overlay file with new (unscrubbed) script and check back in
Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
oTS.WriteLine (sScript)
oTS.Close
oVSSItem.Checkin "Schema Altered", sFileName
End If
Else 'item does not yet exist on SourceSafe; add it
Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
oTS.Write (sScript)
oTS.Close
Set oVSSItem = oVSSDatabase.VSSItem(VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/")
oVSSItem.Add sFileName
End If
End If
Next
'--------
'views
'--------
sObjectType = "Views"
sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/"
Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
'set up clean working directory
sCurrDirectory = SCRIPT_DIRECTORY & "\" & sObjectType
If oFSO.FolderExists(sCurrDirectory) Then
oFSO.DeleteFolder sCurrDirectory, True
End If
Set oFolder = oFSO.CreateFolder(sCurrDirectory)
'cycle through the objects
For Each oDatabaseObject In oDatabase.Views
If oDatabaseObject.SystemObject Then
'do nothing (bypass system objects)
Else
iScriptOptions = SQLDMOScript_Default + SQLDMOScript_Drops + SQLDMOScript_Triggers + SQLDMOScript_Indexes
sScript = oDatabaseObject.Script(iScriptOptions)
sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/" & oDatabaseObject.Name & ".sql"
sFileName = sCurrDirectory & "\" & oDatabaseObject.Name & ".sql"
On Error Resume Next
Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
If Err = 0 Then 'item is already on SourceSafe
oVSSItem.Checkout "Checked out by automated process", sFileName
Set oTS = oFSO.OpenTextFile(sFileName, ForReading)
sCheckedScript = oTS.Read(1000000)
oTS.Close
'scrub the two scripts (remove any trailing CrLfs) and compare them
sTempScript1 = Trim(Replace(sCheckedScript, Chr(13) & Chr(10), " "))
sTempScript2 = Trim(Replace(sScript, Chr(13) & Chr(10), " "))
If sTempScript1 = sTempScript2 Then
'new script is identical to its last check-in version
oVSSItem.UndoCheckout
Else 'script has changed since its last check-in
'overlay file with new (unscrubbed) script and check back in
Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
oTS.WriteLine (sScript)
oTS.Close
oVSSItem.Checkin "Schema Altered", sFileName
End If
Else 'item does not yet exist on SourceSafe; add it
Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
oTS.Write (sScript)
oTS.Close
Set oVSSItem = oVSSDatabase.VSSItem(VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/")
oVSSItem.Add sFileName
End If
End If
Next
'--------
'stored procedures
'--------
sObjectType = "StoredProcedures"
sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/"
Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
'set up clean working directory
sCurrDirectory = SCRIPT_DIRECTORY & "\" & sObjectType
If oFSO.FolderExists(sCurrDirectory) Then
oFSO.DeleteFolder sCurrDirectory, True
End If
Set oFolder = oFSO.CreateFolder(sCurrDirectory)
'cycle through the objects
For Each oDatabaseObject In oDatabase.StoredProcedures
If oDatabaseObject.SystemObject Then
'do nothing (bypass system objects)
Else
iScriptOptions = SQLDMOScript_Default + SQLDMOScript_Drops
sScript = oDatabaseObject.Script(iScriptOptions)
sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/" & oDatabaseObject.Name & ".sql"
sFileName = sCurrDirectory & "\" & oDatabaseObject.Name & ".sql"
On Error Resume Next
Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
If Err = 0 Then 'item is already on SourceSafe
oVSSItem.Checkout "Checked out by automated process", sFileName
Set oTS = oFSO.OpenTextFile(sFileName, ForReading)
sCheckedScript = oTS.Read(1000000)
oTS.Close
'scrub the two scripts (remove any trailing CrLfs) and compare them
sTempScript1 = Trim(Replace(sCheckedScript, Chr(13) & Chr(10), " "))
sTempScript2 = Trim(Replace(sScript, Chr(13) & Chr(10), " "))
If sTempScript1 = sTempScript2 Then
'new script is identical to its last check-in version
oVSSItem.UndoCheckout
Else 'script has changed since its last check-in
'overlay file with new (unscrubbed) script and check back in
Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
oTS.WriteLine (sScript)
oTS.Close
oVSSItem.Checkin "Schema Altered", sFileName
End If
Else 'item does not yet exist on SourceSafe; add it
Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
oTS.Write (sScript)
oTS.Close
Set oVSSItem = oVSSDatabase.VSSItem(VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/")
oVSSItem.Add sFileName
End If
End If
Next
'--------
'rules
'--------
sObjectType = "Rules"
sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/"
Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
'set up clean working directory
sCurrDirectory = SCRIPT_DIRECTORY & "\" & sObjectType
If oFSO.FolderExists(sCurrDirectory) Then
oFSO.DeleteFolder sCurrDirectory, True
End If
Set oFolder = oFSO.CreateFolder(sCurrDirectory)
'cycle through the objects
For Each oDatabaseObject In oDatabase.Rules
If oDatabaseObject.SystemObject Then
'do nothing (bypass system objects)
Else
iScriptOptions = SQLDMOScript_Default + SQLDMOScript_Drops
sScript = oDatabaseObject.Script(iScriptOptions)
sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/" & oDatabaseObject.Name & ".sql"
sFileName = sCurrDirectory & "\" & oDatabaseObject.Name & ".sql"
On Error Resume Next
Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
If Err = 0 Then 'item is already on SourceSafe
oVSSItem.Checkout "Checked out by automated process", sFileName
Set oTS = oFSO.OpenTextFile(sFileName, ForReading)
sCheckedScript = oTS.Read(1000000)
oTS.Close
'scrub the two scripts (remove any trailing CrLfs) and compare them
sTempScript1 = Trim(Replace(sCheckedScript, Chr(13) & Chr(10), " "))
sTempScript2 = Trim(Replace(sScript, Chr(13) & Chr(10), " "))
If sTempScript1 = sTempScript2 Then
'new script is identical to its last check-in version
oVSSItem.UndoCheckout
Else 'script has changed since its last check-in
'overlay file with new (unscrubbed) script and check back in
Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
oTS.WriteLine (sScript)
oTS.Close
oVSSItem.Checkin "Schema Altered", sFileName
End If
Else 'item does not yet exist on SourceSafe; add it
Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
oTS.Write (sScript)
oTS.Close
Set oVSSItem = oVSSDatabase.VSSItem(VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/")
oVSSItem.Add sFileName
End If
End If
Next
'--------
'defaults
'--------
sObjectType = "Defaults"
sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/"
Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
'set up clean working directory
sCurrDirectory = SCRIPT_DIRECTORY & "\" & sObjectType
If oFSO.FolderExists(sCurrDirectory) Then
oFSO.DeleteFolder sCurrDirectory, True
End If
Set oFolder = oFSO.CreateFolder(sCurrDirectory)
'cycle through the objects
For Each oDatabaseObject In oDatabase.Defaults
If oDatabaseObject.SystemObject Then
'do nothing (bypass system objects)
Else
iScriptOptions = SQLDMOScript_Default + SQLDMOScript_Drops
sScript = oDatabaseObject.Script(iScriptOptions)
sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/" & oDatabaseObject.Name & ".sql"
sFileName = sCurrDirectory & "\" & oDatabaseObject.Name & ".sql"
On Error Resume Next
Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
If Err = 0 Then 'item is already on SourceSafe
oVSSItem.Checkout "Checked out by automated process", sFileName
Set oTS = oFSO.OpenTextFile(sFileName, ForReading)
sCheckedScript = oTS.Read(1000000)
oTS.Close
'scrub the two scripts (remove any trailing CrLfs) and compare them
sTempScript1 = Trim(Replace(sCheckedScript, Chr(13) & Chr(10), " "))
sTempScript2 = Trim(Replace(sScript, Chr(13) & Chr(10), " "))
If sTempScript1 = sTempScript2 Then
'new script is identical to its last check-in version
oVSSItem.UndoCheckout
Else 'script has changed since its last check-in
'overlay file with new (unscrubbed) script and check back in
Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
oTS.WriteLine (sScript)
oTS.Close
oVSSItem.Checkin "Schema Altered", sFileName
End If
Else 'item does not yet exist on SourceSafe; add it
Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
oTS.Write (sScript)
oTS.Close
Set oVSSItem = oVSSDatabase.VSSItem(VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/")
oVSSItem.Add sFileName
End If
End If
Next
'--------
'user-defined data types
'--------
sObjectType = "UserDefinedDatatypes"
sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/"
Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
'set up clean working directory
sCurrDirectory = SCRIPT_DIRECTORY & "\" & sObjectType
If oFSO.FolderExists(sCurrDirectory) Then
oFSO.DeleteFolder sCurrDirectory, True
End If
Set oFolder = oFSO.CreateFolder(sCurrDirectory)
'cycle through the objects
For Each oDatabaseObject In oDatabase.UserDefinedDataTypes
If oDatabaseObject.SystemObject Then
'do nothing (bypass system objects)
Else
iScriptOptions = SQLDMOScript_Default + SQLDMOScript_Drops
sScript = oDatabaseObject.Script(iScriptOptions)
sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/" & oDatabaseObject.Name & ".sql"
sFileName = sCurrDirectory & "\" & oDatabaseObject.Name & ".sql"
On Error Resume Next
Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
If Err = 0 Then 'item is already on SourceSafe
oVSSItem.Checkout "Checked out by automated process", sFileName
Set oTS = oFSO.OpenTextFile(sFileName, ForReading)
sCheckedScript = oTS.Read(1000000)
oTS.Close
'scrub the two scripts (remove any trailing CrLfs) and compare them
sTempScript1 = Trim(Replace(sCheckedScript, Chr(13) & Chr(10), " "))
sTempScript2 = Trim(Replace(sScript, Chr(13) & Chr(10), " "))
If sTempScript1 = sTempScript2 Then
'new script is identical to its last check-in version
oVSSItem.UndoCheckout
Else 'script has changed since its last check-in
'overlay file with new (unscrubbed) script and check back in
Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
oTS.WriteLine (sScript)
oTS.Close
oVSSItem.Checkin "Schema Altered", sFileName
End If
Else 'item does not yet exist on SourceSafe; add it
Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
oTS.Write (sScript)
oTS.Close
Set oVSSItem = oVSSDatabase.VSSItem(VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/")
oVSSItem.Add sFileName
End If
End If
Next
'--------
'user-defined functions
'--------
sObjectType = "UserDefinedFunctions"
sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/"
Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
'set up clean working directory
sCurrDirectory = SCRIPT_DIRECTORY & "\" & sObjectType
If oFSO.FolderExists(sCurrDirectory) Then
oFSO.DeleteFolder sCurrDirectory, True
End If
Set oFolder = oFSO.CreateFolder(sCurrDirectory)
'cycle through the objects
For Each oDatabaseObject In oDatabase.UserDefinedFunctions
If oDatabaseObject.SystemObject Then
'do nothing (bypass system objects)
Else
iScriptOptions = SQLDMOScript_Default + SQLDMOScript_Drops
sScript = oDatabaseObject.Script(iScriptOptions)
sVSSItemPath = VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/" & oDatabaseObject.Name & ".sql"
sFileName = sCurrDirectory & "\" & oDatabaseObject.Name & ".sql"
On Error Resume Next
Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
If Err = 0 Then 'item is already on SourceSafe
oVSSItem.Checkout "Checked out by automated process", sFileName
Set oTS = oFSO.OpenTextFile(sFileName, ForReading)
sCheckedScript = oTS.Read(1000000)
oTS.Close
'scrub the two scripts (remove any trailing CrLfs) and compare them
sTempScript1 = Trim(Replace(sCheckedScript, Chr(13) & Chr(10), " "))
sTempScript2 = Trim(Replace(sScript, Chr(13) & Chr(10), " "))
If sTempScript1 = sTempScript2 Then
'new script is identical to its last check-in version
oVSSItem.UndoCheckout
Else 'script has changed since its last check-in
'overlay file with new (unscrubbed) script and check back in
Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
oTS.WriteLine (sScript)
oTS.Close
oVSSItem.Checkin "Schema Altered", sFileName
End If
Else 'item does not yet exist on SourceSafe; add it
Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
oTS.Write (sScript)
oTS.Close
Set oVSSItem = oVSSDatabase.VSSItem(VSS_ROOTPROJECT_NAME & VSS_SUBPROJECT_NAME & sObjectType & "/")
oVSSItem.Add sFileName
End If
End If
Next
'--------
'clean up
'--------
oSQLServer.Close
Set oSQLServer = Nothing
Set oDatabase = Nothing
Set oVSSItem = Nothing
Set oVSSDatabase = Nothing
'toss the working folders
oFSO.DeleteFolder SCRIPT_DIRECTORY & "\Tables", True
oFSO.DeleteFolder SCRIPT_DIRECTORY & "\Views", True
oFSO.DeleteFolder SCRIPT_DIRECTORY & "\StoredProcedures", True
oFSO.DeleteFolder SCRIPT_DIRECTORY & "\Defaults", True
oFSO.DeleteFolder SCRIPT_DIRECTORY & "\Rules", True
oFSO.DeleteFolder SCRIPT_DIRECTORY & "\UserDefinedDataTypes", True
oFSO.DeleteFolder SCRIPT_DIRECTORY & "\UserDefinedFunctions", True
Set oFSO = Nothing
Set oFolder = Nothing
Set oFile = Nothing
Set oTS = Nothing
'process complete!
MsgBox "SQL Objects successfully rolled to SourceSafe."