|
|
Comments and Discussions
|
|
 |
|
Great time saver for any dev project with SQL Server database. Though, with slight modification it can be adopted for any other database, say, Oracle, Sybase so on. The DDL script generator part uses scptxfr.exe which came bundled with SQL Server setup but as it is stopped being shipped starting SQL 2005, for SQL 2005 or above, DDL creation part can be done by using SMO library. An example for SMO can be found here:
http://msdn.microsoft.com/en-us/library/ms162153.aspx
modified 2 Mar '12 - 10:53.
|
|
|
|
|
This is a well written article and a well thought out methodology for a problem that plagues most IT shops and goes largely unnoticed. However, I believe there are some fundamental issues with versioning SQL scripts. First, no matter how you swing it, there is always the problem that a developer checks in the wrong script or even forgets to check in entirely. Therefore, the integrity of the SQL code is always in doubt. Also, versioning SQL scripts often presents problems when attempting to automatically deploy changes in certain scenarios and especially rollback deployed changes. Deployment issues crop up when there are complex dependencies. Rollbacks are almost always near impossible without separate rollback scripts.
DBVS is an innovative product that versions not SQL scripts, but XML representations of each object. You don't have the problem of developers forgetting to check in since changes are made directly in the development environment and a check in command causes the DBVS server to connect directly to that database to extract its metadata. Therefore, you can use any means including SQL scripts or IDEs to alter the object. Once you're finished, you check in and DBVS handles the rest. As far as control, DBVS can deny ALTER permissions from all controlled objects until they are checked out. Once checked out, only the user who performed the check out is granted ALTER permissions. This methodology gives you 100% data integrity.
By versioning XML rather than SQL code, automated database deployments and rollbacks become possible. DBVS compares the revisions of the database objects being deployed to the corresponding live objects in the target database and produces a list of differences. Those differences equate to changes that must be made in the target database to make those objects look like the object revisions being deployed. The list of changes is ordered to account for dependencies and translated into SQL code, which is then run against the target database. Rollbacks work in a similar fashion.
You can find out more at http://www.dbvsys.com. All comments, questions and suggestions are welcome. I would be interested in hearing your feedback.
Brian Krebs
Chief Technology Officer
Database Versioning Systems.
|
|
|
|
|
|
Hi,
I applied the "Moving from 2000 to 2005" thread patches to get this working with SQL 2005. The problem I'm having is that 3/4 of my stored procedures are not being saved to separate files. They show up in the large single file but the RegEx expression is not detecting them. I've compared the SQL scripts of those that are working and those that are not -- it turns out that 3/4 of our stored procedures are using comments right after the "EXEC dbo.sp_executesql @statement = N'" line. We have a dozen databases with 1000s of stored procs, so moving the comments elsewhere is not feasible.
e.g.
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stored_proc_name]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author: John Doe
-- Create date: March 27, 2008
-- Description: Get current preferences and details
-- =============================================
CREATE PROCEDURE [dbo].[stored_proc_name]
Anyone know how to modify the following WriteGroup's RegEx in order to match SP's like the one above?
// Loop through all the SP's
WriteGroup("StoredProcs", "BEGIN\\s+EXEC\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?<Statement>CREATE\\s+PROC(?:EDURE)?\\s+(<Owner>[^\\.\\s]*?\\.)?(?<Name>[^\\s]*?)[(\\s]+.*?)'\\s+END\\s*GO\\s", scriptInput, outputPath, includeOwner);
Cheers,
Eric.
|
|
|
|
|
The way that I handled this case is with the following additions to the Regex and WriteGroup method.
// Alternate method for Stored Procedures with leading comments.
WriteGroup("StoredProcs", "BEGIN\\s+EXEC\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'(?<Comment>.*?)(?<Statement>CREATE\\s+PROC(?:EDURE)?\\s+(<Owner>[^\\.\\s]*?\\.)?(?<Name>[^\\s]*?)[(\\s]+.*?)'\\s+END\\s*GO\\s", scriptInput, outputPath, includeOwner);
Now the comment(s) is held in the match with a named identifier, and can be concatenated with the staement in WriteGroup().
string comment = match.Groups["Comment"].Value;
// Add the openning comment, if it exists.
statement = comment + dropStatement + statement;
I also found this statement useful.
// Replace escaped single quotes placed around literals by wizard.
statement = statement.Replace("''", "'");
Hope this helps someone. Ken
|
|
|
|
|
Hello together,
I have a problem using the code together with the Microsoft SQL Server Database Publishing Wizard 1.3.
The code works fine for tables, but it does not extract the foreign key constraints. They are in the big SQL file like this:
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Song_Tag_Tag]') AND parent_object_id = OBJECT_ID(N'[dbo].[Song_Tag]'))
ALTER TABLE [dbo].[Song_Tag] WITH CHECK ADD CONSTRAINT [FK_Song_Tag_Tag] FOREIGN KEY([Id_Tag])
REFERENCES [dbo].[Tag] ([Id_Tag])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Song_Tag_Tag]') AND parent_object_id = OBJECT_ID(N'[dbo].[Song_Tag]'))
ALTER TABLE [dbo].[Song_Tag] CHECK CONSTRAINT [FK_Song_Tag_Tag]
GO
But the code does not extract them.
So I tried to create a new RegExp like this:
WriteGroup("ForeignKeys", "(?<Statement>ALTER TABLE\\s+\\[(?<Owner>.*?)\\]\\.\\[(?<Name>.*?)\\]\\s+WITH\\s+CHECK\\s+ADD\\s+CONSTRAINT\\s+\\[(?<ForeignKey>.*?)\\]\\s+FOREIGN\\s+KEY\\(.*\\)\\s+REFERENCES\\s+\\[(?<Owner2>.*?)\\]\\.\\[(?<Name2>.*?)\\]\\s+\\(.*\\)?)\\s+GO\\s", scriptInput, outputPath, false);
But this does not match.
Can anybody help me with the correct RegExp or a better solution?
Best regards,
Bernd
|
|
|
|
|
Once a user has downloaded updated SQL scripts from SVN, how will they know which order to run them in?
Seems like a tricky problem to me.
Given the database continuously logs all changes, they ought to make it automatically keep a set of script files in the filing system up to date. Then you wouldn't even need to re-generate the files and they'd be exactly in synch with source code.
|
|
|
|
|
Looks like a great tool/code and something I wont to get running in our dev enviroment. Ive been doing a bit of searching around the web and discovered Microsoft Database Publishing Wizard:
Microsoft Database Publishing Wizard 1.1[^]
This tool also scripts a database to a single file and has command line arguments. It looks like an updated version of the orginal Microsoft Tool on the 2000 cd!
Looking at your code it looks like you take a single file and split it up so in theory it shouldnt be too much work to upgrade to the latest Microsoft Tool? What do you think?
Blog Post showing how the unsplit file can be Saved into SNV with[^]
I think the Source Controlling Databases is something that lots of devs are missing, IMHO this is something that could be turned into a commercial application if it is given a full UI to help manage Merging...
|
|
|
|
|
This is pretty similar to the instructions posted by peter.stuart for getting it to work for SQl2005, the main difference is you need to make sure you are running the Database Publishing Wizard 1.3, and update the path to use that exe instead of the 1.1 exe. 1.3 is installed with Visual Studio 2008 SP1, or you can install it manually from: http://go.microsoft.com/fwlink/?LinkId=119368 [^] You can check to see if it's installed by looking in Add/Remove programs for "Microsoft Sql Server Database Publishing Wizard 1.3".
After this is installed change the SqlScriptExecutablePath path in the App.config to "C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2\SqlPubWiz.exe"
Then make all the changes that peter.stuart posted below, here is a summary of those changes:
Change the following line from:
ProcessStartInfo info = new ProcessStartInfo(sqlScriptExecutable, "/s " + serverName + " /d " + dbName + " /f " + scriptFile + " " + switches);
To:
ProcessStartInfo info = new ProcessStartInfo(sqlScriptExecutable, "script -S " + serverName + " -d " + dbName + " /f " + scriptFile);
Change the regular expressions to these:
WriteGroup("Tables", "BEGIN\\s+(?<statement>CREATE TABLE \\[(?<owner>.*?)\\]\\.\\[(?<name>.*?)\\].*?)END\\s+GO\\s", scriptInput, outputPath, includeOwner);
WriteGroup("Views", "EXEC\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?<statement>CREATE\\s+VIEW\\s+(<owner>[^\\.\\s]*?\\.)?(?<name>.*?)\\s+AS\\s+.*?)'\\s*GO\\s", scriptInput, outputPath, includeOwner);
WriteGroup("StoredProcs", "BEGIN\\s+EXEC\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?<statement>CREATE\\s+PROC(?:EDURE)?\\s+(<owner>[^\\.\\s]*?\\.)?(?<name>[^\\s]*?)[(\\s]+.*?)'\\s+END\\s*GO\\s", scriptInput, outputPath, includeOwner);
WriteGroup("Functions", "BEGIN\\s+execute\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?<statement>CREATE\\s+FUNCTION?\\s+(?<owner>[^\\.\\s]*?\\.)?(?<name>[^\\s]*?)[(\\s]+.*?)'\\s+END\\s*GO\\s", scriptInput, outputPath, includeOwner);
WriteGroup("Triggers", "EXEC\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?<statement>CREATE\\s+TRIGGER\\s+(?<owner>[^\\.\\s]*?\\.)(?<name>[^\\s]*?)\\s+ON\\s+.*?)'\\s+GO\\s", scriptInput, outputPath, false);
</name></owner></statement></name></owner></statement></name></owner></statement></name></owner></statement></name></owner></statement>
Another change I made was adding this right after the checking of the output path if it contains spaces, this way it will create the output path if it doesnt already exist (good for first time ran):
if (!Directory.Exists(outputPath))
{
Directory.CreateDirectory(outputPath);
}
I also didn't like the new Guid for the script file that contains everything, so I changed:
string scriptFile = outputPath + "\\" + Guid.NewGuid().ToString() + ".sql";
To this:
string scriptFile = outputPath + "\\" + dbName + ".sql";
And added this right after the Directory check I added above so that it deletes the file before the Publishing Wizard runs again:
if (File.Exists(scriptFile))
{
File.Delete(scriptFile);
}
|
|
|
|
|
Thanks davidm99, saved me some time this morning.
I had problems getting it to work first time as I use the Date datatype which is in SQL2008 but not SQL2005.
I found making a slight mod made your changes work even better
ProcessStartInfo info = new ProcessStartInfo(sqlScriptExecutable, "script -S " + serverName + " -d " + dbName + " /f " + scriptFile + " -targetserver 2008");
The -targetserver 2008 seems to be poorly documented but does work.
Hope this helps anyone stumbling across this page.
HTH
Ed
|
|
|
|
|
We are just about to move from 2000 to 2005. I just found this tool and would be interested in getting familiar with it now and using it in production when we move to 2005. Has anyone tried it with 2005?
Warm regards,
|
|
|
|
|
Yes, I tried it in SQL 2005, works just fine from what I can tell, scripts created and appear right! Thanks for the tool, this will be very handy and offer a layer of code protection which we didn't have before.
Becker
|
|
|
|
|
I found that some databases under 2005 would work but others wouldn't. The root of the problem seems to be that scptxfr has trouble with 2005. A bit of googling led me to the following tool.
http://download.microsoft.com/download/a/1/a/a1a0cfc2-5942-409d-abef-0b4980b9d6a0/DatabasePublishingWizard.msi
Using this in connection with ScriptDB4SVN requires a minor mod to the source (Program.cs).
Change the following line from:
ProcessStartInfo info = new ProcessStartInfo(sqlScriptExecutable, "/s " + serverName + " /d " + dbName + " /f " + scriptFile + " " + switches);
To:
ProcessStartInfo info = new ProcessStartInfo(sqlScriptExecutable, "script -S " + serverName + " -d " + dbName + " /f " + scriptFile);
Where sqlScriptExecutable has now be set in the config file to be "C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz.exe" or whereever you have put the publishing wizard.
I hope this helps. I am still familarising myself with ScriptDB4SVN but so far it looks really promising. Thanks!
|
|
|
|
|
Further to my last message it seems the regular expressions need tweaking if you use the publish wizard as the script script differs slightly fom that generated by scptxfr
<pre>// Loop through all the Tables
WriteGroup("Tables", "BEGIN\\s+(?<Statement>CREATE TABLE \\[(?<Owner>.*?)\\]\\.\\[(?<Name>.*?)\\].*?)END\\s+GO\\s", scriptInput, outputPath, includeOwner);
// Loop through all the Views
WriteGroup("Views", "EXEC\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?<Statement>CREATE\\s+VIEW\\s+(<Owner>[^\\.\\s]*?\\.)?(?<Name>.*?)\\s+AS\\s+.*?)'\\s*GO\\s", scriptInput, outputPath, includeOwner);
// Loop through all the SP's
WriteGroup("StoredProcs", "BEGIN\\s+EXEC\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?<Statement>CREATE\\s+PROC(?:EDURE)?\\s+(<Owner>[^\\.\\s]*?\\.)?(?<Name>[^\\s]*?)[(\\s]+.*?)'\\s+END\\s*GO\\s", scriptInput, outputPath, includeOwner);
// Loop through all the Functions using a regular expression
WriteGroup("Functions", "BEGIN\\s+execute\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?<Statement>CREATE\\s+FUNCTION?\\s+(?<Owner>[^\\.\\s]*?\\.)?(?<Name>[^\\s]*?)[(\\s]+.*?)'\\s+END\\s*GO\\s", scriptInput, outputPath, includeOwner);
// Loop through all the Triggers
WriteGroup("Triggers", "EXEC\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?<Statement>CREATE\\s+TRIGGER\\s+(?<Owner>[^\\.\\s]*?\\.)(?<Name>[^\\s]*?)\\s+ON\\s+.*?)'\\s+GO\\s", scriptInput, outputPath, false);</pre>
|
|
|
|
|
Thanks for the upgrade instructions. Would be useful to know of any other tweaks.
For info, I've added the -schemaonly switch to the sqlpubwiz invocation which could save a lot of unnecessary output.
Still early days for me but, yes, this tool looks very useful.
|
|
|
|
|
|
This works for me for the Tables and Stored Procedures. Also works for the views if there are no header comments... I'm sure I can figure out that. What doesn't work for me is the foreign keys. I can't seem to figure out how to resolve it. They are no longer scripted with the table using the SqlPubWiz.exe from what I can tell. so maybe this calls for a new WriteGroup case statement?
|
|
|
|
|
Hi,
I am trying to use this utility for a sql server 2005 database, but I am unable to locate <b>scptxfr.exe</b>, Please suggest.
Regards,
Venk
|
|
|
|
|
in file Program.cs function Main line 125
should change
if (tables2Script.Length > 0)
to
if (!string.IsNullOrEmpty(tables2Script))
to prevent program crash if key "ScriptTableData" is absent in the config file.
Peoples should think.
Machines shold work.
|
|
|
|
|
Has anyone tried to use this tool with SQL Server 2005 ? (I'll have a closer look at it myself as soon as i have the time).
We are looking for a way to store SCADA applications in SVN, so this would be quite handy for us. A few SCADA systems we use (like Siemens WinCC) are based on SQL Server these days, which makes the use of SVN hard since the whole database must be detached, checked in and re-attached with every change, and most of that data is useless anyway.
If anyone has experience with it, i would like to share idease with him/her.
regards,
Peter
|
|
|
|
|
Your program deletes all previous *.sql files in all directories (well deletes in the root and blanks in subfolders). This is assuming that everything in the project folder where you run this executeable is owned by the executable. I had created a version update file for migration (surely one of the reasons to have versioned control databases) and this got nuked before I committed it. Doah! 
To cope with this I've hard coded a it so it won't delete from a folder called updates now on but it would be cool if you could have an exclusions folder in the config. Just looked and found out it has been nuking my livedata files, my test scripts too... lucky for version control!
Thanks though for creating it. It's been very useful otherwise!
|
|
|
|
|
I have a "varbinary(max)" data-type in a table def, and scriptdb4svn is generating the following on an exportdb:
[value] [varbinary] (-1) NULL ,
which is invalid sql 
I can't just substitute an actual number in the column def, since anything over 8000 is rejected. SQL Server won't take an actual length > 8000, hence the "max" keyword. "varbinary(max)" means anything well into gigabyte range.
How can I have a varbinary and have scriptdb4svn handle it correctly?
|
|
|
|
|
It appears the SQL scripter from SQL Server 2000 doesn't support the varchar(MAX). I hacked together a fix several months ago. In the Program.cs near the bottom, I added the following lines.
// Trim trailing \n so output of different servers is the same
statement = statement.TrimEnd(new char[] {'\n', '\r'});
if (includeOwner && owner.Length > 0)
objectname = owner + "." + objectname;
string varbinary = "[varbinary] (-1)";
if (groupName == "Tables" && statement.Contains(varbinary))
statement = statement.Replace(varbinary, "[varbinary] (max)");
string varchar = "[varchar] (-1)";
if (groupName == "Tables" && statement.Contains(varchar))
statement = statement.Replace(varchar, "[varchar] (max)");
string filename = outputPath + "\\" + groupName + "\\" + objectname + ".sql";
|
|
|
|
|
I have a user-defined function that contains the following in a comment:
/*
SCRIPTING NOTE:
When generating a SQL script for the database, the script creates this function before some of the objects it depends on.
To avoid errrors, move the "CREATE FUNCTION dbo.GETUSERFUNCTIONDEPARTMENTS" before "CREATE VIEW dbo.View1".
*/
Your script detects the creation of a new view and starts scripting a view that contains an error. How would you update this code to ignore create statements that have been commented out? I believe you need to add something to the regular-expression, but I'm not a RegExpert.  Any ideas?
|
|
|
|
|
I've found the utility does not like comments in triggers to appear before the "AS". If comments occur before "AS", the comment is included in the object's name.
|
|
|
|
|
|
|
 General  News  Suggestion  Question  Bug  Answer  Joke  Rant  Admin
|
This tool (ScriptDB4Svn) enables you to add your SQL Server databases to Subversion (SVN) source control.
| Type | Article |
| Licence | |
| First Posted | 13 Oct 2006 |
| Views | 165,529 |
| Bookmarked | 136 times |
|
|
Submit an article or tip
Post your Blog
