Create script for copying master database structure

apisano

I'm new to Multi Script. I've been using SQL Compare to update several databases from a master copy. I was hoping to be able to speed this process up by using Multi Script. As a source script I created a script file for my master database from the SQL Management Studio. When I tried propagating this to another database using MultiScript I got an error and it did not propagate the changes.

"Msg 15023, Level 16, State 1, Line 2
User, group, or role 'BUILTIN\Administrators' already exists in the current database."

I'm not sure what this error is about and what I have to do to get this script to work properly. I also don't know whether it's safe to just ignore all error messages.

Any help would be appreciated.

stanori

Hi apisano,

Thanks for your post. It sounds like your script that you are specifying in Multiscript is attempting to add a new user object that already exists in the destination SQL Server database. This message is coming from the SQL Server itself as a result of the script.

To get the script working properly, you would need to manually edit the script to remove the entry that is making changes to this particular user object. Also, since this is the master database, this is a system DB, so you will need to be careful about what changes you are rolling into it.

Best Regards,
Steve

apisano

Is there a better way of creating the script for propagating from a master db to several other databases when an update is made? If not, it seems to me that creating the script for this would become very time consuming for the database I am maintaining. It contains a combination of over 500 tables and views.

stanori

You could try using our SQL Compare tool to generate a deployment script and deploy to each of your remote servers. There is a command line version of SQL Compare that you can use to create a batch job to do this automatically.