February 20, 2008 at 2:44 pm
Hi,
I am tasked with creating a nightly backup of the database STRUCTURE, without copying the data.
I know how to use Management Studio to generate SQL Scripts..
I know how to use Management Studio to generate a .bak file..
I know how to use T-SQL to generate a .bak file..
I need to know how to use T-SQL to generate SQL Scripts.
This includes everything - tables, keys, check constraints, indexes, full-text catalogs, views, stored procedures, each of the 4 kinds of functions, triggers, rules, defaults, synonyms, schemas, logins, permissions, etc.
We might be able to get away with ignoring some of the Notification SErvices stuff (queues, etc.) and the advanced security stuff (Asynchronous connections, certificates, etc). But all of the basic objects HAVE to be copied.
Again.. no data can be copied. We want all of the structure with none of the data.
Also it's necessary to have fixed database role permissions for each user - sp_datareader, etc.
Any thoughts or ideas?
Thanks
Jason
February 20, 2008 at 4:00 pm
Start by running SQL Profiler & doing it through the GUI. That will show you what is happening behind the scenes.
Then start hacking away. 🙂
You can use sp_helptext to get quite a bit of stuff. See BOL for details. Investigate things like the sys.objects view.
Try this code for database users (from part of a doco script I use):
[font="Courier New"]DECLARE @name sysname,
@sql nvarchar(4000),
@maxlen1 smallint,
@maxlen2 smallint,
@maxlen3 smallint
IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmpTable%')
DROP TABLE #tmpTable
CREATE TABLE #tmpTable
(
DBName sysname NOT NULL ,
UserName sysname NOT NULL,
RoleName sysname NOT NULL
)
DECLARE c1 CURSOR for
SELECT name FROM master.sys.databases
OPEN c1
FETCH c1 INTO @name
WHILE @@FETCH_STATUS >= 0
BEGIN
SELECT @sql =
'INSERT INTO #tmpTable
SELECT N'''+ @name + ''', a.name, c.name
FROM [' + @name + '].sys.database_principals a
JOIN [' + @name + '].sys.database_role_members b ON b.member_principal_id = a.principal_id
JOIN [' + @name + '].sys.database_principals c ON c.principal_id = b.role_principal_id
WHERE a.name != ''dbo'''
EXECUTE (@sql)
FETCH c1 INTO @name
END
CLOSE c1
DEALLOCATE c1
SELECT @maxlen1 = (MAX(LEN(COALESCE(DBName, 'NULL'))) + 2)
FROM #tmpTable
SELECT @maxlen2 = (MAX(LEN(COALESCE(UserName, 'NULL'))) + 2)
FROM #tmpTable
SELECT @maxlen3 = (MAX(LEN(COALESCE(RoleName, 'NULL'))) + 2)
FROM #tmpTable
SET @sql = 'SELECT LEFT(DBName, ' + LTRIM(STR(@maxlen1)) + ') AS ''DB Name'', '
SET @sql = @sql + 'LEFT(UserName, ' + LTRIM(STR(@maxlen2)) + ') AS ''User Name'', '
SET @sql = @sql + 'LEFT(RoleName, ' + LTRIM(STR(@maxlen3)) + ') AS ''Role Name'' '
SET @sql = @sql + 'FROM #tmpTable '
SET @sql = @sql + 'ORDER BY DBName, UserName'
EXEC(@sql)
[/font]
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 20, 2008 at 4:25 pm
May we ask why you're looking at doing this? Is your DB structure and security changing that frequently that you need to essentially "backup" the structure on a regular basis?
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
February 20, 2008 at 10:45 pm
Timothy Ford (2/20/2008)
May we ask why you're looking at doing this? Is your DB structure and security changing that frequently that you need to essentially "backup" the structure on a regular basis?
The Database Structure is changing.
Security, not so much.
Basically we want to have a "testdb" where the lab can throw new data on there, which is certain to be wiped away (think Etch a Sketch) every night.
But structural changes that are made to the Main DB need to be copied over.. for example, there is a daily program that dynamically generates tables, views and stored procedures. This is growing to be a 300 GB database and some of these objects have 400+ columns so it's a little much to manually re-generate scripts every day - the goal is to not be dependant on a DBA to keep up, but rather to automate things so lab users can use a GUI to do specific DBA-like things - including create a cloned structure database.
Yes, the data structure does get complicated.. welcome to the world of scientific lab testing.
Jason
February 21, 2008 at 9:47 am
Red Gate has a product that will do this very efficiently. It's called SQL Compare. Using this software, you compare your production database against an empty database and the product will produce a script for synchronizing the structure of the empty database with that of the production database. That script, if run on the development server, will create an empty structure that is identical to your production database. If you wanted to, you could keep this script in a source repository and overtime, you would have a record of the changes that were made to the structure of the database. This would allow you to revert to an earlier version of the database if you needed or wanted to.
February 21, 2008 at 1:22 pm
Willem Tilstra (2/21/2008)
Red Gate has a product that will do this very efficiently. It's called SQL Compare.
Thank you Willem.
Can this tool be set up to run AUTOMATICALLY, say at midnight of each day?
(Drop an existing database,
create a new database w/ the same name,
diff-compare vs. a Production database,
script & create all missing objects)
I am not interested in storing a historical archive of past database changes.. I just want the CURRENT production database, dynamically generated each day and with no data, to be cloned to a new, daily mess-around database?
Thanks again.
Jason
February 21, 2008 at 1:25 pm
You can use sp_helptext to get quite a bit of stuff. See BOL for details. Investigate things like the sys.objects view.
Thank you for your response.. honestly, this seems like a lot of time, and we're under a time crunch.
I cannot be the first person who has ever wanted to do this.
Your script for tables.. do you have a similar script for stored procs, views, schemas, synonyms, table-valued functions, aggregate functions, fixed database role assignments... etc?
I am looking to script EVERYTHING.
Can't it be that simple???
February 21, 2008 at 1:46 pm
I believe that there is a Command Line option. However, I have never used it since I've only used the product via the GUI up to now. If I remember correctly, the Command Line option is an additional purchase but don't quote me on this - I'd contact Red Gate. But I do love the product - it's great.
February 21, 2008 at 2:19 pm
--Here's a VB Script I found a long time ago on net and have since modified and corrected. It works with SQL Server 2000/2005. It also checks everything in/out of vss. (you could just comment out those sections if you don't want VSS control. This runs nightly for me. The vb script source code should be saved with a .vbs extension. Mine is named scontrol.vbs (as was posted by someone else). I'd like to send you the original SCode , but I don't know where the link is. I also had to make 2 days worth of changes to get things working. After it runs, I have everything scripted to the filesystem in .sql files and vss.
Here are steps:
Create a batch file with entries for each database server.
scontrol.vbs DB1
scontrol.vbs DB2
scontrol.vbs DB3
scontrol.vbs DB4
Schedule this OS batch file using the OS system scheduler. Use NT authentication to allow the process to gain access to each SQL Server. Don't use sqlserver to schedule. I tried. it has issues with this.
I run my jobs from a 2005 server. This process scripts from 2000 and 2005 servers.
the scripting job runs about 10pm nightly. In the morning, I run a job in SQL Server to check for existence of the log files for the prior evening. If the logs don't exist, I send myself an email that something erred.
--SQL Job to check existence of scripting job logs --this is step 1 of job. --Create a step 2 on job failure to generate an email.
declare @filedate varchar(50)
declare @filename varchar(100)
set @filedate = CAST(MONTH(getdate()) AS VARCHAR(2)) +
CAST(DAY(getdate()) AS VARCHAR(2)) +
CAST(YEAR(getdate()) AS VARCHAR(4))
--print @FileName
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTempTable]') AND type in (N'U'))
DROP TABLE [dbo].[MyTempTable]
--insert into #temp
create table mit.dbo.MyTempTable ([path] varchar(100), ShortPath varchar(100), [type] varchar(20), datecreated datetime,
datelastaccessed datetime, datelastmodified datetime, attributes varchar(100), size int)
Set @filename = '\\db3\SQLVSSShare\SQLCode_Text\RunLog' + 'DB1' + @filedate + '.log'
insert into mit.dbo.MyTempTable exec spFileDetails @FileName
Set @filename = '\\db3\SQLVSSShare\SQLCode_Text\RunLog' + 'DB2' + @filedate + '.log'
insert into mit.dbo.MyTempTable exec spFileDetails @FileName
Set @filename = '\\db3\SQLVSSShare\SQLCode_Text\RunLog' + 'DB3' + @filedate + '.log'
insert into mit.dbo.MyTempTable exec spFileDetails @FileName
Set @filename = '\\db3\SQLVSSShare\SQLCode_Text\RunLog' + 'Spare2' + @filedate + '.log'
insert into mit.dbo.MyTempTable exec spFileDetails @FileName
step 2 of job:
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'jkeith@yourname@home.com'
@query = 'SELECT [path] from mit.dbo.MyTempTable',
@subject = 'Visual Source Safe SQL Server Sync' ,
@attach_query_result_as_file = 0
drop table mit.dbo.MyTempTable
Here's the vb source code. Find your environment settings to change near the top of code. I have left a lot of msgbox code commented that I use for testing.
Good luck.
' VBScript source code
Option Explicit
'constants
Const adOpenStatic = 3
Const ForReading = 1
Const adLockReadOnly = 1
Const adCmdStoredProc = 4
Const adVarChar = 200
Const adParamInput = 1
Const SQLDMOScript2_ExtendedProperty = 4194304
Const SQLDMOScript2_Default = 0
Const SQLDMOScript_Default = 4
Const SQLDMOScript_Drops = 1
Const SQLDMOScript_ObjectPermissions = 2
Const SQLDMOScript_Indexes = 73736
Const SQLDMOScript_DRI_All = 532676608
Const SQLDMOScript_Triggers = 16
Const VSSFLAG_BINTEXT = 3145728
Const ForAppending = 8
Const ForWriting = 2
'Const VSSPath = "\\db3\vss$\srcsafe.ini"
Const VSSPath = "\\db3\vss\srcsafe.ini"
Const VSSRoot = "$/SQLCode" ' note if you are going to use the root of your Source Safe Database use $, not $/
Const VSSAdminAccount = "admin"
Const VSSAdminAccountPassword = ""
Const FileRepositoryPath = "F:\VSS\SQLCode_Text"
'variables
Dim path, ProjectPath, versionNumber, ConnectionString, VSSDate, VSSComment
Dim VSSLabelComment, VSSUserName, VSSVersionNumber, ServerName, RecordCount, objTS, DatabaseName
Dim MyArray, arraymember, totalpath, objSQLServer, objFso, objVSS, database, SQLServerVersion, NormalizedSQLServerName
Dim VSSItem, Item, flag, StoredProcedure, VSSProjectPath, objCommand, objConnection, objRecordSet, ConnectionString1, strSelect
Dim ErrorNumber, ErrorDescription, version, LogFileName, LogFile, objFile, DifferenceLogFileName, DifferenceLogFile, Table, ObjectType, View
Dim UserDefinedFunction
Dim strFuncName
Dim strTableName
'On Error Resume Next
'object creation
Set objSQLServer = CreateObject("SQLDMO.SQLServer")
Set objFso = CreateObject("Scripting.FileSystemObject")
set objVSS = CreateObject("SourceSafe.8.0")
'retreiving the Server Name
ServerName = wscript.arguments(0)
'fixing SQL Server Instance Names - our project path in VSS and the file system replaces the instance name with an underscore
NormalizedSQLServerName = replace(ServerName,"\","_")
'wscript.echo "NormalizedSQLServerName: " + NormalizedSQLServerName
CreateFileRepositoryPath FileRepositoryPath + "\" + NormalizedSQLServerName
'Creating Run Log
CreateRunLogFile
'Creating Difference Log
CreateDifferenceLogFile
'connecting to Visual Source Safe
ConnectToVSS
flag=0
'Connecting To SQL Server
ConnecttoSQL
if flag=0 then
CreateFileRepositoryServerAndDatabasesPaths
if ErrorNumber=0 then
on error goto 0
'determining SQL Server version
SQLServerVersion = objSQLServer.VersionMajor
'creating the project paths for the databases
set VSSItem = objVSS.VSSItem("$/")
flag = 0
for each item in VSSItem.Items
'wscript.echo lcase("$/" + item.Name) + " " + lcase(VSSRoot)
if lcase("$/" + item.Name) = lcase(VSSRoot) then
flag = 1
exit for
end if
next
if flag = 0 then
'this means the SQL Server VSS code repository folder has not been created yet
'creating the visual source safe path
'wscript.echo "Creating the VSS Project Path " + VSSRoot + " for SQL Server code"
WriteLog "Creating the VSS Project Path " + VSSRoot + " for SQL Server code"
objVSS.VSSItem("$/").NewSubProject (VSSRoot)
if err.number<> 0 then
WriteLog "ERROR: " + Err.description
else
WriteLog "new project for " + VSSRoot + " created"
end if
end if
set VSSItem = objVSS.VSSItem(VSSRoot)
flag = 0
for each item in VSSItem.Items
if lcase(item.Name) = lcase(NormalizedSQLServerName) then
flag = 1
exit for
end if
next
if flag = 0 then
'this means the SQL Server is not yet under version control
'wscript.echo "adding " + NormalizedSQLServerName + " to version control"
WriteLog "adding " + NormalizedSQLServerName + " to version control"
objVSS.VSSItem(VSSRoot).NewSubProject (NormalizedSQLServerName)
if err.number<> 0 then
WriteLog "ERROR: " + Err.description
else
WriteLog "new project for " + NormalizedSQLServerName + " created"
end if
end if
for each database in objSQLServer.Databases
DatabaseName = Database.Name
If not database.systemobject and database.Status = 0 then
Set VSSItem = objVSS.VSSItem(VSSRoot + "/" + NormalizedSQLServerName )
flag = 0
path = FileRepositoryPath + "\" + NormalizedSQLServerName + "\" + DatabaseName
for each item in VSSItem.Items
if lcase(item.Name) = lcase(DatabaseName) then
'wscript.echo "database " + DatabaseName + " already under version control"
flag = 1
exit for
end if
next
if flag = 0 then
'this means the SQL Server database is not yet under version control
'creating the visual source safe path
'wscript.echo "adding " + DatabaseName + " to version control"
WriteLog "adding " + DatabaseName + " to version control"
objVSS.VSSItem(VSSRoot).NewSubProject (NormalizedSQLServerName + "/" + DatabaseName)
if err.number<> 0 then
WriteLog "ERROR: " + Err.description
else
WriteLog "new sub project for " + NormalizedSQLServerName + "/" + DatabaseName + " created"
end if
end if
StoredProcedures
Tables
Views
if objSQLServer.VersionMajor > 7 then
UserDefinedFunctions
end if
end if
next
end if
end if
Sub StoredProcedures()
ObjectType = "Procedure"
''wscript.echo("Checking in Stored Procedures")
WriteLog("Checking in Stored Procedures")
'wscript.echo( VSSRoot + "/" + NormalizedSQLServerName + "/" + DatabaseName )
VSSProjectPath = VSSRoot & "/" & NormalizedSQLServerName & "/" & DatabaseName
'WriteLog("path is : " + path)
'WriteLog("VSSProjectPath is : " + VSSProjectPath)
For Each StoredProcedure In database.StoredProcedures
If StoredProcedure.SystemObject = False Then
'existence check
'Wscript.echo("Proc Name: " + StoredProcedure.Name)
'WriteLog("Proc Name: " + StoredProcedure.Name)
dim strSPName
strSPName = StoredProcedure.Name
strSPName = Replace(strSPName,":","X")
'check to see if the proc is under source control - if not add it
'check to see if the proc is checked out - if so bail
'if proc is checked in, compare to see if it is different from what is in the database
'if the same, bail
'if so check in
'checking to see if proc is under source control
Set VSSItem = objVSS.VSSItem(VSSRoot + "/" + NormalizedSQLServerName + "/" + DatabaseName)
flag = 0
For Each Item In VSSItem.Items
If LCase(Item.Name) = LCase(StoredProcedure.Owner + "." + strSPName + ".PRC") Then
'WriteLog("1.4 proc " + StoredProcedure.Owner + "." + strSPName + ".PRC" + " already under version control")
flag = 1
Exit For
End If
Next
Set VSSItem = Nothing
If flag = 1 Then
'stored procedure is already under source control
'checking to see if it is checked out
If objVSS.VSSItem(VSSProjectPath).Items(False).Item(StoredProcedure.Owner + "." + strSPName + ".PRC").IsCheckedOut = False Then
'WriteLog("1.9 proc " + StoredProcedure.Owner + "." + strSPName + ".PRC" + "is under version control already and is not checked out")
' wscript.echo(DatabaseName + "\" + StoredProcedure.Owner + "." + StoredProcedure.Name + " is in VSS and not checked out ")
' WriteLog(DatabaseName + "\" + StoredProcedure.Owner + "." + StoredProcedure.Name + " is in VSS and not checked out")
'check it out. You must check the proc out to release read only lock
' WriteLog("1.10 Checking " + DatabaseName + "\" + StoredProcedure.Owner + "." + StoredProcedure.Name + " out of VSS")
objVSS.VSSItem(VSSProjectPath).Items(False).Item(StoredProcedure.Owner + "." + strSPName + ".PRC").Checkout "Versioning System", path + "\" + StoredProcedure.Owner + "." + strSPName + ".PRC"
'script it out
StoredProcedure.Script SQLDMOScript_Default + SQLDMOScript_Drops + SQLDMOScript_ObjectPermissions, path + "\" + StoredProcedure.Owner + "." + strSPName + ".PRC1", SQLDMOScript2_ExtendedProperty
'check for differences, if so check in
'Wscript.echo("Checking in " + StoredProcedure.Owner + "." + StoredProcedure.Name)
'WriteLog("1.0 Checking in " + StoredProcedure.Owner + "." + StoredProcedure.Name + " to check for differences")
If objVSS.VSSItem(VSSProjectPath).Items(False).Item(StoredProcedure.Owner + "." + strSPName + ".PRC").IsDifferent(path + "\" + StoredProcedure.Owner + "." + strSPName + ".PRC1") = True Then
'before we script this proc out, let's make sure it has versioning info on it.
'SQLDMO does not support versioning so we have to use ADO to connect and add the versioning
For Each version In objVSS.VSSItem(VSSProjectPath).Items(False).Item(StoredProcedure.Owner + "." + strSPName + ".PRC").Versions
VSSVersionNumber = version.versionNumber
Next
' WriteLog("1.11 proc " + StoredProcedure.Owner + "." + strSPName + ".PRC" + "is different, updating VSS")
If objSQLServer.VersionMajor > 7 Then
'now we have the latest extended properties let's update the proc with them.
Call ExtendedProperties("VSSDate", Date, StoredProcedure.Owner, StoredProcedure.Name)
Call ExtendedProperties("VSSComment", "This is the " & (VSSVersionNumber + 1) & " version of this proc", StoredProcedure.Owner, StoredProcedure.Name)
Call ExtendedProperties("VSSUserName", objVSS.UserName, StoredProcedure.Owner, StoredProcedure.Name)
Call ExtendedProperties("VSSVersionNumber", VSSVersionNumber + 1, StoredProcedure.Owner, StoredProcedure.Name)
End If
'we have to script it out again to get the version info correct
'first we delete the old script
On Error GoTo 0
objFso.DeleteFile(path + "\" + StoredProcedure.Owner + "." + strSPName + ".PRC1")
objFso.DeleteFile(path + "\" + StoredProcedure.Owner + "." + strSPName + ".PRC")
StoredProcedure.Script SQLDMOScript_Default + SQLDMOScript_Drops + SQLDMOScript_ObjectPermissions, path + "\" + StoredProcedure.Owner + "." + strSPName + ".PRC", SQLDMOScript2_ExtendedProperty
objVSS.VSSItem(VSSProjectPath).Items(False).Item(StoredProcedure.Owner + "." + strSPName + ".PRC").Checkin "Versioning System", path + "\" + StoredProcedure.Owner + "." + strSPName + ".PRC"
WriteDifferenceLog("2.0 Proc " + StoredProcedure.Owner + "." + strSPName + ".PRC in Database " + DatabaseName + " on Server " + ServerName + " has changed")
Else
'proc is the same, check it back in
objVSS.VSSItem(VSSProjectPath).Items(False).Item(StoredProcedure.Owner + "." + strSPName + ".PRC").UndoCheckout(path + "\" + StoredProcedure.Owner + "." + strSPName + ".PRC")
objFso.DeleteFile(path + "\" + StoredProcedure.Owner + "." + strSPName + ".PRC1")
End If
Else
'WriteLog("1.3 " + StoredProcedure.Owner + "." + strSPName + ".PRC is checked out")
'objFso.DeleteFile(path + "\" + StoredProcedure.Owner + "." + strSPName + ".PRC1")
End If
Else
' wscript.echo(DatabaseName + "\" + StoredProcedure.Owner + "." + StoredProcedure.Name + " is not in VSS")
' Wscript.echo("Proc does not exist, lets add it")
If objSQLServer.VersionMajor > 7 Then
' Wscript.echo("Before we do that, let's tag it with extended properties")
'before we add it we must add the extended properties to the proc
' Wscript.echo("adding Extended Properties to " + StoredProcedure.Owner + "." + StoredProcedure.Name)
Call ExtendedProperties("VSSDate", Date, StoredProcedure.Owner, StoredProcedure.Name)
Call ExtendedProperties("VSSComment", "This is the initial sync of the proc", StoredProcedure.Owner, StoredProcedure.Name)
Call ExtendedProperties("VSSUserName", objVSS.UserName, StoredProcedure.Owner, StoredProcedure.Name)
Call ExtendedProperties("VSSVersionNumber", "1", StoredProcedure.Owner, StoredProcedure.Name)
End If
'Wscript.echo("Checking in " + StoredProcedure.Owner + "." + StoredProcedure.Name)
'wscript.echo("path is " + path)
StoredProcedure.Script SQLDMOScript_Default + SQLDMOScript_Drops + SQLDMOScript_ObjectPermissions, path + "\" + StoredProcedure.Owner + "." + strSPName + ".PRC", SQLDMOScript2_ExtendedProperty
'wscript.echo(VSSProjectPath)
objVSS.VSSItem(VSSProjectPath).Add path + "\" + StoredProcedure.Owner + "." + strSPName + ".PRC", VSSFLAG_BINTEXT
End If
End If
Next
End Sub
Sub ExtendedProperties(ByVal PropertyName, ByVal PropertyValue, ByVal OwnerName, ByVal ProcedureName)
Dim RecordCount
Set objCommand = CreateObject("ADODB.Command")
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
' wscript.echo(ConnectionString)
' wscript.echo(DatabaseName)
ConnectionString = "Provider = SQLOLEDB.1;Integrated Security = SSPI;Persist Security Info = False;Data1 Source = .;Initial Catalog = ;"
ConnectionString1 = Replace(ConnectionString, "Catalog = ;", "Catalog = " + DatabaseName + ";")
ConnectionString = Replace(ConnectionString1, ";Data1 Source = .;", ";Data Source = " + ServerName + ";")
'wscript.echo(ConnectionString)
objConnection.ConnectionString = ConnectionString
objConnection.Open()
'lets check to see if the functions exist
strSelect = "SELECT name FROM ::fn_listextendedproperty ('" + PropertyName + "', 'user', '" + OwnerName + "', '" + ObjectType + "', '" + ProcedureName + "', NULL, default)"
' wscript.echo(strSelect)
objRecordSet.Open strSelect, objConnection, adOpenStatic, adLockReadOnly
RecordCount = objRecordSet.RecordCount
' wscript.echo(RecordCount)
objRecordSet.Close()
objCommand.ActiveConnection = objConnection
objCommand.CommandType = adCmdStoredProc
If RecordCount = 0 Then
'wscript.echo("Adding")
objCommand.CommandText = "sp_addextendedproperty"
Else
objCommand.CommandText = "sp_updateextendedproperty"
'wscript.echo("updating")
End If
objCommand.Parameters.Append(objCommand.CreateParameter("@name", adVarChar, adParamInput, 256, PropertyName))
objCommand.Parameters.Append(objCommand.CreateParameter("@value", adVarChar, adParamInput, 128, PropertyValue))
objCommand.Parameters.Append(objCommand.CreateParameter("@level0type", adVarChar, adParamInput, 128, "user"))
objCommand.Parameters.Append(objCommand.CreateParameter("@level0name", adVarChar, adParamInput, 256, OwnerName))
objCommand.Parameters.Append(objCommand.CreateParameter("@level1type", adVarChar, adParamInput, 128, ObjectType))
objCommand.Parameters.Append(objCommand.CreateParameter("@level1name", adVarChar, adParamInput, 256, ProcedureName))
objCommand.Execute()
Set objCommand = Nothing
objConnection.Close()
End Sub
Sub CreateFileRepositoryPath(ByVal path)
'this function creates the necessary file structure for the file repository
' wscript.echo("Path is : " + path)
totalpath = ""
If objFso.FolderExists(path) = False Then
' wscript.echo("Path111 is : " + path)
MyArray = Split(path, "\")
' wscript.echo ( MyArray(1) )
For arraymember = 0 To UBound(MyArray)
' wscript.echo("0.1 is : " + UBound(MyArray) )
If objFso.FolderExists(totalpath + MyArray(arraymember)) Then
totalpath = totalpath + MyArray(arraymember) + "\"
' wscript.echo("1 is : " + totalpath)
Else
' wscript.echo("1.5 is : " + totalpath)
' wscript.echo( "array is : " + MyArray(arraymember) )
totalpath = totalpath + MyArray(arraymember) + "\"
' wscript.echo("2 is : " + totalpath)
objFso.CreateFolder(totalpath)
ErrorNumber = Err.Number
ErrorDescription = Err.Description
If ErrorNumber <> 0 Then
WriteLog("Error Creating Path " + path + " Error description " + ErrorDescription)
Else
WriteLog("Path " + path + " Created")
End If
End If
Next
End If
End Sub
Sub WriteLog(ByVal Message)
LogFile.WriteLine(Message)
End Sub
Sub WriteDifferenceLog(ByVal Message)
DifferenceLogFile.WriteLine(Message)
End Sub
Sub CreateRunLogFile()
LogFileName = FileRepositoryPath + "\RunLog"+ServerName +replace(cstr(date),"/","")+ ".log"
' wscript.echo(LogFileName)
If objFso.FileExists(LogFileName) = True Then
Set objFile = objFso.GetFile(LogFileName)
Set LogFile = objFile.OpenAsTextStream(ForAppending)
WriteLog("Hello from " + ServerName)
Else
Set LogFile = objFso.CreateTextFile(LogFileName)
WriteLog("Hello from " + ServerName)
End If
End Sub
Sub ConnectToVSS()
WriteLog("connecting to Visual Source Safe")
objVSS.Open VSSPath, VSSAdminAccount, VSSAdminAccountPassword
If Err.Number <> 0 Then
WriteLog("ERROR: " + Err.Description)
Else
WriteLog("Connected to VSS")
End If
End Sub
Sub ConnectToSQL()
WriteLog("Connecting to SQL Server " + ServerName)
objSQLServer.LoginSecure = True
On Error Resume Next
objSQLServer.Connect(ServerName)
ErrorNumber = Err.Number
ErrorDescription = Err.Description
If ErrorNumber = -2147467259 Then
WriteLog("ERROR: can't connect to " + ServerName)
flag = 1
ElseIf ErrorNumber <> 0 Then
WriteLog("ERROR: " + ErrorDescription)
Else
WriteLog("Successfully connected to " + ServerName)
End If
On Error GoTo 0
End Sub
Sub CreateDifferenceLogFile()
DifferenceLogFileName = FileRepositoryPath + "\DifferenceLog"+replace(cstr(date),"/","")+ ".log"
'wscript.echo(DifferenceLogFileName)
If objFso.FileExists(DifferenceLogFileName) = True Then
Set objFile = objFso.GetFile(DifferenceLogFileName)
Set DifferenceLogFile = objFile.OpenAsTextStream(ForAppending)
WriteDifferenceLog("Hello from " + ServerName)
Else
Set DifferenceLogFile = objFso.CreateTextFile(DifferenceLogFileName)
WriteDifferenceLog("Hello from " + ServerName)
End If
End Sub
Sub Tables()
ObjectType = "Table"
' wscript.echo("Checking in Tables")
WriteLog("Checking in Tables")
VSSProjectPath = VSSRoot + "/" + NormalizedSQLServerName + "/" + DatabaseName
WriteLog("path is : " + path)
WriteLog("VSSProjectPath is : " + VSSProjectPath)
For Each Table In database.Tables
If Table.SystemObject = False Then
'existence check
'Wscript.echo("Proc Name: " + Table.Name)
WriteLog("Proc Name: " + Table.Name)
dim tpos
strTableName = Table.Name
tpos=InStr(strTableName,"/")
strTableName = Replace(strTableName,"/","X")
strTableName = Replace(strTableName,"$","X")
'check to see if the Table is under source control - if not add it
'check to see if the Table is checked out - if so bail
'if Table is checked in, compare to see if it is different from what is in the database
'if the same, bail
'if so check in
'checking to see if Table is under source control
set VSSItem = objVSS.VSSItem(VSSRoot + "/" + NormalizedSQLServerName + "/" + DatabaseName)
flag = 0
For Each Item In VSSItem.Items
If LCase(Item.Name) = LCase(Table.Owner + "." + strTableName + ".TAB") Then
'wscript.echo("proc " + Table.Owner + "." + strTableName + ".TAB" + " already under version control")
flag = 1
Exit For
End If
Next
Set VSSItem = Nothing
If flag = 1 Then
'stored procedure is already under source control
'checking to see if it is checked out
If objVSS.VSSItem(VSSProjectPath).Items(False).Item(Table.Owner + "." + strTableName + ".TAB").IsCheckedOut = False Then
WriteLog("proc " + Table.Owner + "." + strTableName + ".TAB" + "is under version control already and is not checked out")
'wscript.echo(DatabaseName + "\" + Table.Owner + "." + strTableName + " is in VSS and not checked out ")
WriteLog(DatabaseName + "\" + Table.Owner + "." + strTableName + " is in VSS and not checked out")
'check it out. You must check the Table out to release read only lock
WriteLog("Checking " + DatabaseName + "\" + Table.Owner + "." + Table.Name + " out of VSS")
objVSS.VSSItem(VSSProjectPath).Items(False).Item(Table.Owner + "." + strTableName + ".TAB").Checkout "Versioning System", path + "\" + Table.Owner + "." + strTableName + ".TAB"
'script it out
Table.Script SQLDMOScript_Default + SQLDMOScript_Drops + SQLDMOScript_ObjectPermissions + SQLDMOScript_Triggers + SQLDMOScript_Indexes + SQLDMOScript_DRI_All, path + "\" + Table.Owner + "." + strTableName + ".TAB1", , SQLDMOScript2_ExtendedProperty
'check for differences, if so check in
'Wscript.echo("Checking in " + Table.Owner + "." + Table.Name)
WriteLog("Checking in " + Table.Owner + "." + Table.Name + " to check for differences")
If objVSS.VSSItem(VSSProjectPath).Items(False).Item(Table.Owner + "." + strTableName + ".TAB").IsDifferent(path + "\" + Table.Owner + "." + strTableName + ".TAB1") = True Then
If objSQLServer.VersionMajor > 7 Then
'before we script this Table out, let's make sure it has versioning info on it.
'SQLDMO does not support versioning so we have to use ADO to connect and add the versioning
For Each version In objVSS.VSSItem(VSSProjectPath).Items(False).Item(Table.Owner + "." + strTableName + ".TAB").Versions
VSSVersionNumber = version.versionNumber
Next
'wscript.echo("proc " + Table.Owner + "." + Table.Name + ".TAB" + "is different, updating VSS")
'now we have the latest extended properties let's update the Table with them.
Call ExtendedProperties("VSSDate", Date, Table.Owner, Table.Name)
Call ExtendedProperties("VSSComment", "This is the " & (VSSVersionNumber + 1) & " version of this proc", Table.Owner, Table.Name)
Call ExtendedProperties("VSSUserName", objVSS.UserName, Table.Owner, Table.Name)
Call ExtendedProperties("VSSVersionNumber", VSSVersionNumber + 1, Table.Owner, Table.Name)
End If
'we have to script it out again to get the version info correct
'first we delete the old script
On Error GoTo 0
objFso.DeleteFile(path + "\" + Table.Owner + "." + Table.Name + ".TAB1")
objFso.DeleteFile(path + "\" + Table.Owner + "." + Table.Name + ".TAB")
Table.Script SQLDMOScript_Default + SQLDMOScript_Triggers + SQLDMOScript_Drops + SQLDMOScript_ObjectPermissions + SQLDMOScript_Indexes + SQLDMOScript_DRI_All, path + "\" + Table.Owner + "." + strTableName + ".TAB", , SQLDMOScript2_ExtendedProperty
objVSS.VSSItem(VSSProjectPath).Items(False).Item(Table.Owner + "." + strTableName + ".TAB").Checkin "Versioining System", path + "\" + Table.Owner + "." + strTableName + ".TAB"
WriteDifferenceLog("Proc " + Table.Owner + "." + Table.Name + ".TAB in Database " + DatabaseName + " on Server " + ServerName + " has changed")
Else
'proc is the same, check it back in
objVSS.VSSItem(VSSProjectPath).Items(False).Item(Table.Owner + "." + strTableName + ".TAB").UndoCheckout(path + "\" + Table.Owner + "." + strTableName + ".TAB")
objFso.DeleteFile(path + "\" + Table.Owner + "." + strTableName + ".TAB1")
End If
Else
WriteLog(Table.Owner + "." + strTableName + ".TAB is checked out")
'objFso.DeleteFile(path + "\" + Table.Owner + "." + strTableName + ".TAB1")
End If
Else
'wscript.echo(DatabaseName + "\" + Table.Owner + "." + strTableName + " is not in VSS")
'Wscript.echo("Proc does not exist, lets add it")
If objSQLServer.VersionMajor > 7 Then
'Wscript.echo("Before we do that, let's tag it with extended properties")
'before we add it we must add the extended properties to the proc
'Wscript.echo("adding Extended Properties to " + Table.Owner + "." + Table.Name)
Call ExtendedProperties("VSSDate", Date, Table.Owner, Table.Name)
Call ExtendedProperties("VSSComment", "This is the initial sync of the proc", Table.Owner, Table.Name)
Call ExtendedProperties("VSSUserName", objVSS.UserName, Table.Owner, Table.Name)
Call ExtendedProperties("VSSVersionNumber", "1", Table.Owner, Table.Name)
End If
'Wscript.echo("Checking in " + Table.Owner + "." + Table.Name)
'wscript.echo("path is " + path)
Table.Script SQLDMOScript_Default + SQLDMOScript_Triggers + SQLDMOScript_Drops + SQLDMOScript_ObjectPermissions + SQLDMOScript_Indexes + SQLDMOScript_DRI_All, path + "\" + Table.Owner + "." + strTableName + ".TAB", , SQLDMOScript2_ExtendedProperty
objVSS.VSSItem(VSSProjectPath).Add path + "\" + Table.Owner + "." + strTableName + ".TAB", VSSFLAG_BINTEXT
End If
End If
Next
End Sub
Sub Views()
ObjectType = "View"
'wscript.echo("Checking in Views")
' WriteLog("Checking in Views")
VSSProjectPath = VSSRoot + "/" + NormalizedSQLServerName + "/" + DatabaseName
'WriteLog("path is : " + path)
'WriteLog("VSSProjectPath is : " + VSSProjectPath)
For Each View In database.Views
If View.SystemObject = False Then
'existence check
'Wscript.echo("View Name: " + View.Name)
'WriteLog("View Name: " + View.Name)
dim strViewName
strViewName = View.Name
strViewName = Replace(strViewName,";","X")
'check to see if the View is under source control - if not add it
'check to see if the View is checked out - if so bail
'if View is checked in, compare to see if it is different from what is in the database
'if the same, bail
'if so check in
'checking to see if View is under source control
set VSSItem = objVSS.VSSItem(VSSRoot + "/" + NormalizedSQLServerName + "/" + DatabaseName)
flag = 0
For Each Item In VSSItem.Items
If LCase(Item.Name) = LCase(View.Owner + "." + strViewName + ".VIEW") Then
'wscript.echo("proc " + View.Owner + "." + strViewName + ".VIEW" + " already under version control")
flag = 1
Exit For
End If
Next
Set VSSItem = Nothing
If flag = 1 Then
'stored procedure is already under source control
'checking to see if it is checked out
If objVSS.VSSItem(VSSProjectPath).Items(False).Item(View.Owner + "." + strViewName + ".VIEW").IsCheckedOut = False Then
' WriteLog("proc " + View.Owner + "." + strViewName + ".VIEW" + "is under version control already and is not checked out")
'wscript.echo(DatabaseName + "\" + View.Owner + "." + strViewName + " is in VSS and not checked out ")
' WriteLog(DatabaseName + "\" + View.Owner + "." + strViewName + " is in VSS and not checked out")
'check it out. You must check the View out to release read only lock
' WriteLog("Checking " + DatabaseName + "\" + View.Owner + "." + strViewName + " out of VSS")
objVSS.VSSItem(VSSProjectPath).Items(False).Item(View.Owner + "." + strViewName + ".VIEW").Checkout "Versioning System", path + "\" + View.Owner + "." + strViewName + ".VIEW"
'script it out
View.Script SQLDMOScript_Default + SQLDMOScript_Drops + SQLDMOScript_ObjectPermissions, path + "\" + View.Owner + "." + strViewName + ".VIEW1", SQLDMOScript2_ExtendedProperty
'check for differences, if so check in
'Wscript.echo("Checking in " + View.Owner + "." + View.Name)
'WriteLog("Checking in " + View.Owner + "." + View.Name + " to check for differences")
If objVSS.VSSItem(VSSProjectPath).Items(False).Item(View.Owner + "." + strViewName + ".VIEW").IsDifferent(path + "\" + View.Owner + "." + strViewName + ".VIEW1") = True Then
If objSQLServer.VersionMajor > 7 Then
'before we script this View out, let's make sure it has versioning info on it.
'SQLDMO does not support versioning so we have to use ADO to connect and add the versioning
For Each version In objVSS.VSSItem(VSSProjectPath).Items(False).Item(View.Owner + "." + strViewName + ".VIEW").Versions
VSSVersionNumber = version.versionNumber
Next
'wscript.echo("proc " + View.Owner + "." + strViewName + ".VIEW" + "is different, updating VSS")
'now we have the latest extended properties let's update the View with them.
Call ExtendedProperties("VSSDate", Date, View.Owner, View.Name)
Call ExtendedProperties("VSSComment", "This is the " & (VSSVersionNumber + 1) & " version of this proc", View.Owner, View.Name)
Call ExtendedProperties("VSSUserName", objVSS.UserName, View.Owner, View.Name)
Call ExtendedProperties("VSSVersionNumber", VSSVersionNumber + 1, View.Owner, View.Name)
End If
'we have to script it out again to get the version info correct
'first we delete the old script
On Error GoTo 0
objFso.DeleteFile(path + "\" + View.Owner + "." + strViewName + ".VIEW1")
objFso.DeleteFile(path + "\" + View.Owner + "." + strViewName + ".VIEW")
View.Script SQLDMOScript_Default + SQLDMOScript_Drops + SQLDMOScript_ObjectPermissions, path + "\" + View.Owner + "." + strViewName + ".VIEW", SQLDMOScript2_ExtendedProperty
objVSS.VSSItem(VSSProjectPath).Items(False).Item(View.Owner + "." + strViewName + ".VIEW").Checkin "Versioining System", path + "\" + View.Owner + "." + strViewName + ".VIEW"
WriteDifferenceLog("Proc " + View.Owner + "." + strViewName + ".VIEW in Database " + DatabaseName + " on Server " + ServerName + " has changed")
Else
'proc is the same, check it back in
objVSS.VSSItem(VSSProjectPath).Items(False).Item(View.Owner + "." + strViewName + ".VIEW").UndoCheckout(path + "\" + View.Owner + "." + strViewName + ".VIEW")
objFso.DeleteFile(path + "\" + View.Owner + "." + strViewName + ".VIEW1")
End If
Else
'WriteLog(View.Owner + "." + strViewName + ".VIEW is checked out")
'objFso.DeleteFile(path + "\" + View.Owner + "." + strViewName + ".VIEW1")
End If
Else
'Wscript.echo(DatabaseName + "\" + View.Owner + "." + strViewName + " is not in VSS")
'Wscript.echo("Proc does not exist, lets add it")
If objSQLServer.VersionMajor > 7 Then
'Wscript.echo("Before we do that, let's tag it with extended properties")
'before we add it we must add the extended properties to the proc
'Wscript.echo("adding Extended Properties to " + View.Owner + "." + View.Name)
Call ExtendedProperties("VSSDate", Date, View.Owner, View.Name)
Call ExtendedProperties("VSSComment", "This is the initial sync of the proc", View.Owner, View.Name)
Call ExtendedProperties("VSSUserName", objVSS.UserName, View.Owner, View.Name)
Call ExtendedProperties("VSSVersionNumber", "1", View.Owner, View.Name)
End If
'Wscript.echo("Checking in " + View.Owner + "." + View.Name)
'Wscript.echo( "path is " + path)
View.Script SQLDMOScript_Default + SQLDMOScript_Drops + SQLDMOScript_ObjectPermissions, path + "\" + View.Owner + "." + strViewName + ".VIEW", SQLDMOScript2_ExtendedProperty
objVSS.VSSItem(VSSProjectPath).Add path + "\" + View.Owner + "." + strViewName + ".VIEW", VSSFLAG_BINTEXT
End If
End If
Next
End Sub
Sub CreateFileRepositoryServerAndDatabasesPaths()
'wscript.echo("creating file paths for server and databases")
'wscript.echo(path)
For Each database In objSQLServer.Databases
DatabaseName = database.Name
If Not database.systemobject Then
path = FileRepositoryPath + "\" + NormalizedSQLServerName + "\" + DatabaseName
CreateFileRepositoryPath(path)
End If
Next
End Sub
Sub UserDefinedFunctions()
ObjectType = "Function"
'Wscript.echo("Checking in UserDefinedFunctions")
'WriteLog("Checking in UserDefinedFunctions")
VSSProjectPath = VSSRoot + "/" + NormalizedSQLServerName + "/" + DatabaseName
'WriteLog("path is : " + path)
'WriteLog("VSSProjectPath is : " + VSSProjectPath)
For Each UserDefinedFunction In database.UserDefinedFunctions
If UserDefinedFunction.SystemObject = False Then
'existence check
dim pos
strFuncName = UserDefinedFunction.Name
pos=InStr(strFuncName,"$")
strFuncName = Replace(strFuncName,"$","X")
'wscript.echo( strFuncName)
'Wscript.echo("Proc Name: " + UserDefinedFunction.Name)
' WriteLog("Proc Name: " + UserDefinedFunction.Name)
'check to see if the UserDefinedFunction is under source control - if not add it
'check to see if the UserDefinedFunction is checked out - if so bail
'if UserDefinedFunction is checked in, compare to see if it is different from what is in the database
'if the same, bail
'if so check in
'checking to see if UserDefinedFunction is under source control
Set VSSItem = objVSS.VSSItem(VSSRoot + "/" + NormalizedSQLServerName + "/" + DatabaseName)
flag = 0
For Each Item In VSSItem.Items
If LCase(Item.Name) = LCase(UserDefinedFunction.Owner + "." + strFuncName + ".FUNC") Then
'Wscript.echo("proc " + UserDefinedFunction.Owner + "." + strFuncName + ".FUNC" + " already under version control")
flag = 1
Exit For
End If
Next
Set VSSItem = Nothing
If flag = 1 Then
'stored procedure is already under source control
'checking to see if it is checked out
If objVSS.VSSItem(VSSProjectPath).Items(False).Item(UserDefinedFunction.Owner + "." + strFuncName + ".FUNC").IsCheckedOut = False Then
' WriteLog("proc " + UserDefinedFunction.Owner + "." + strFuncName + ".FUNC" + "is under version control already and is not checked out")
'Wscript.echo(DatabaseName + "\" + UserDefinedFunction.Owner + "." + UserDefinedFunction.Name + " is in VSS and not checked out ")
' WriteLog(DatabaseName + "\" + UserDefinedFunction.Owner + "." + strFuncName + " is in VSS and not checked out")
'check it out. You must check the UserDefinedFunction out to release read only lock
' WriteLog("Checking " + DatabaseName + "\" + UserDefinedFunction.Owner + "." + strFuncName + " out of VSS")
' objVSS.VSSItem(VSSProjectPath).Items(False).Item(UserDefinedFunction.Owner + "." + strFuncName + ".FUNC").Checkout("Versioning System", path + "\" + UserDefinedFunction.Owner + "." + strFuncName + ".FUNC"
objVSS.VSSItem(VSSProjectPath).Items(False).Item(UserDefinedFunction.Owner + "." + strFuncName + ".FUNC").Checkout "Versioning System", path + "\" + UserDefinedFunction.Owner + "." + strFuncName + ".FUNC"
'script it out
UserDefinedFunction.Script SQLDMOScript_Default + SQLDMOScript_Drops + SQLDMOScript_ObjectPermissions, path + "\" + UserDefinedFunction.Owner + "." + strFuncName + ".FUNC1", SQLDMOScript2_ExtendedProperty
'check for differences, if so check in
'Wscript.echo("Checking in " + UserDefinedFunction.Owner + "." + UserDefinedFunction.Name)
' WriteLog("Checking in " + UserDefinedFunction.Owner + "." + strFuncName + " to check for differences")
If objVSS.VSSItem(VSSProjectPath).Items(False).Item(UserDefinedFunction.Owner + "." + strFuncName + ".FUNC").IsDifferent(path + "\" + UserDefinedFunction.Owner + "." + strFuncName + ".FUNC1") = True Then
'before we script this UserDefinedFunction out, let's make sure it has versioning info on it.
'SQLDMO does not support versioning so we have to use ADO to connect and add the versioning
For Each version In objVSS.VSSItem(VSSProjectPath).Items(False).Item(UserDefinedFunction.Owner + "." + strFuncName + ".FUNC").Versions
VSSVersionNumber = version.versionNumber
Next
'Wscript.echo("proc " + UserDefinedFunction.Owner + "." + UserDefinedFunction.Name + ".FUNC" + "is different, updating VSS")
'now we have the latest extended properties let's update the UserDefinedFunction with them.
Call ExtendedProperties("VSSDate", Date, UserDefinedFunction.Owner, UserDefinedFunction.Name)
Call ExtendedProperties("VSSComment", "This is the " & (VSSVersionNumber + 1) & " version of this proc", UserDefinedFunction.Owner, UserDefinedFunction.Name)
Call ExtendedProperties("VSSUserName", objVSS.UserName, UserDefinedFunction.Owner, UserDefinedFunction.Name)
Call ExtendedProperties("VSSVersionNumber", VSSVersionNumber + 1, UserDefinedFunction.Owner, UserDefinedFunction.Name)
'we have to script it out again to get the version info correct
'first we delete the old script
On Error GoTo 0
objFso.DeleteFile(path + "\" + UserDefinedFunction.Owner + "." + strFuncName + ".FUNC1")
objFso.DeleteFile(path + "\" + UserDefinedFunction.Owner + "." + strFuncName + ".FUNC")
UserDefinedFunction.Script SQLDMOScript_Default + SQLDMOScript_Drops + SQLDMOScript_ObjectPermissions, path + "\" + UserDefinedFunction.Owner + "." + strFuncName + ".FUNC", SQLDMOScript2_ExtendedProperty
objVSS.VSSItem(VSSProjectPath).Items(False).Item(UserDefinedFunction.Owner + "." + strFuncName + ".FUNC").Checkin "Versioining System", path + "\" + UserDefinedFunction.Owner + "." + strFuncName + ".FUNC"
WriteDifferenceLog("Proc " + UserDefinedFunction.Owner + "." + strFuncName + ".FUNC in Database " + DatabaseName + " on Server " + ServerName + " has changed")
Else
'proc is the same, check it back in
objVSS.VSSItem(VSSProjectPath).Items(False).Item(UserDefinedFunction.Owner + "." + strFuncName + ".FUNC").UndoCheckout(path + "\" + UserDefinedFunction.Owner + "." + strFuncName + ".FUNC")
objFso.DeleteFile(path + "\" + UserDefinedFunction.Owner + "." + strFuncName + ".FUNC1")
End If
Else
' WriteLog(UserDefinedFunction.Owner + "." + strFuncName + ".FUNC is checked out")
'objFso.DeleteFile(path + "\" + UserDefinedFunction.Owner + "." + strFuncName + ".FUNC1")
End If
Else
'Wscript.echo(DatabaseName + "\" + UserDefinedFunction.Owner + "." + strFuncName + " is not in VSS")
'Wscript.echo("Proc does not exist, lets add it")
'Wscript.echo("Before we do that, let's tag it with extended properties")
'before we add it we must add the extended properties to the proc
'Wscript.echo("adding Extended Properties to " + UserDefinedFunction.Owner + "." + strFuncName)
Call ExtendedProperties("VSSDate", Date, UserDefinedFunction.Owner, UserDefinedFunction.Name)
Call ExtendedProperties("VSSComment", "This is the initial sync of the proc", UserDefinedFunction.Owner, UserDefinedFunction.Name)
Call ExtendedProperties("VSSUserName", objVSS.UserName, UserDefinedFunction.Owner, UserDefinedFunction.Name)
Call ExtendedProperties("VSSVersionNumber", "1", UserDefinedFunction.Owner, UserDefinedFunction.Name)
'Wscript.echo("Checking in " + UserDefinedFunction.Owner + "." + strFuncName)
'Wscript.echo("path is " + path)
UserDefinedFunction.Script SQLDMOScript_Default + SQLDMOScript_Drops + SQLDMOScript_ObjectPermissions, path + "\" + UserDefinedFunction.Owner + "." + strFuncName + ".FUNC", SQLDMOScript2_ExtendedProperty
'Wscript.echo(UserDefinedFunction.Name)
objVSS.VSSItem(VSSProjectPath).Add path + "\" + UserDefinedFunction.Owner + "." + strFuncName + ".FUNC", VSSFLAG_BINTEXT
End If
End If
Next
End Sub
'End Class
February 22, 2008 at 1:19 am
Hi Jason,
alternatively you might use scptxfr.exe in the following procedure:
----------------------- snip ---------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ScriptDatabase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_ScriptDatabase]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATEproc sp_ScriptDatabase
@dbname sysname,
@servername sysname,
@texttime varchar(10)
as
declare @command varchar(1000), @command2 VARCHAR(999)
set@command = '"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s ' + @servername + ' /I /d '
+ @dbname + ' /q /F c:\temp\' + @dbname + '_' + @texttime
print@command
exec master..xp_cmdshell @command
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------- snap ---------------------------------------
scptxfr.exe was a part of sql server 2000, don't know if it's in 2005...
i filched this procedure from this forum here, i am sure.
regards
karl
Best regards
karl
February 22, 2008 at 2:48 am
Would'nt it be possible for you run the change scripts in your test environment or would that be time consuming. That way your test data can be preserved.
"Keep Trying"
February 22, 2008 at 9:26 am
Check out Diff from ApexSQL.com. I believe it is command-line drivable as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 22, 2008 at 9:30 am
February 22, 2008 at 11:04 am
Jason,
Here's an example using SQL Compare 6 and SQLCMD to do the refresh.
Scheduling could be done with Task Scheduler and the appropriate credentials.
REM TestingRefresh.cmd
SQLCMD -S TestServer-E -Q "ALTER DATABASE TestingDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE"
SQLCMD -S TestServer -E -Q "DROP DATABASE TestingDB"
SQLCMD -S TestServer -E -Q "CREATE DATABASE TestingDB"
SQLCOMPARE /S1:myProductionServer /DB1:myProductionDB /S2:TestServer /DB2:TestingDB /synchronize
February 22, 2008 at 12:45 pm
If you want an automated way to do this, I'd highly recommend using Bill Wunder's DDL Archive Utility. Been using it for the last 3 years, and absolutely love it. He's working on a new version to support the 2005 objects ... but the current version is awesome. It relies upon some older technologies (SQLXML3, Com, SQL2000 Client Tools (for DTS)), but I've yet to find anything better for an automated tool.
http://bwunder.com/default.aspx
If you decide to go with it and would like more information or help, let me know. Also, tell Bill I sent ya 🙂
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply