June 1, 2005 at 9:23 am
Hello,
I am having a problem scripting out a database (all objects) to use for disaster recovery and development database purposes. I want to automate the process so it can be run in a scheduled job. I tried using the scptxfr utility and Ken Henderson's sp_generate_script (from The Guru's Guide to Stored Procedures, XML, and HTML - see http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=183331 for more info). I've also tried just right clicking the database and choosing generate SQL script.
Anyway, whenever I use these methods, it changes the names of some of the Primary Keys (that's all I've noticed so far). Example:
Original - PK_tblAccessLevelREF_AccessLevel
After script - PK__tblAccessLevelRE__0DAF0CB0
The names aren't specified like that in the script generated, but they end up that way in the resulting database objects after the script is run. Does anyone know of a way to generate a script that maintains the object names?
TIA, chris.
June 1, 2005 at 9:36 am
A little more info... The key names aren't specified in any of the scripts generated. The interesting thing is that if you go into manage indexes, then double click on the index in question, it gives you an option to generate SQL. If you do that, it retains the name in the t-sql snippet that it displays. When you script the objects out, no index names are specified.
June 2, 2005 at 2:15 am
you may want to try the DB Ghost data and schema scripter, it's free and it has a COM interface so you can easily create a script to automate the process. The scipter always extracts the names of constraints so the script exactly matches the object it scripted, it was one of those things that annoyed me when using Enterprise Managers scripting functionality. There are codes samples within the help file. The data and schema scripter is bundled within the DB Ghost evaluation.
regards,
Mark Baekdal
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply