May 10, 2005 at 1:53 pm
Hi,
i need to develop a .sql script to be run in an remote shared environment to create a database and objects ( table, constraints, indexes etc). i have the database and objecst up and running locally, this has to be transfered to destination server. i can not use DTS as the destination server is a shared environment and they don't allow me to do anything on the server.. all that they will do is run a script on the server if i give one...
So i was wondering if i could generate script from sql server.. else i will have to write a script my self which is time consuming
please let me know
Thanks
THNQdigital
May 10, 2005 at 2:48 pm
yes you can... from enterprise manager, mouse right click -->all task -->generate sql scripts.
Rgds
JFB
May 11, 2005 at 1:37 am
Let me know if it works, I've found this to be unreliable and I think I have a better way.
May 11, 2005 at 6:25 am
The main problem I have run into generating scripts from EM for the entire database is when nested objects exist. Take stored procedures, for example. EM does not generate the code in order of dependency. If stored procedure A calls stored procedure B, you will get an error if EM creates the script for proc A before it creates the script for proc B. Of course, you can use EM to generate the script, then reorganize the code as needed.
Also, be careful about creating logins. All logins for the server are included, not just the ones associated with the database users. You don't want to drop/add logins by mistake. I would handle that step separately. In all of my databases, permissions are assigned only to roles. So if I script the database and include object permissions, I don't have to worry about login/user problems (missing user, login already exists,etc). I can add the users to the roles in a separate step.
May 11, 2005 at 7:01 am
that's the problem (dependencies) I've always encountered. What I do is build the database object by object in a recursive loop until I get no errors (even sysdepends errors) and then I know what is left are valid errors that need to be investigated or if there's nothing left I know the database has been built correctly. On a successful completion of running an object script, that object script is added to a single database script which can be run without errors and is safe to export to any users as the process inherantly tests itself.
This algorithm means that no matter how complex the database is with regards to dependencies, you'll always get a script that works. That saves me time.
regards,
Mark Baekdal
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server
May 11, 2005 at 2:04 pm
Thank you guys.. for all the help
THNQdigital
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply