Create script for copying master database structure

Single-click script execution on multiple SQL Servers.

Moderators: JonathanWatts, Anu Deshpande, dlkj, Colin Millerchip, Michelle Taylor

Create script for copying master database structure

Postby apisano » Mon Sep 10, 2012 5:34 pm

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.
apisano
 
Posts: 2
Joined: Sat Sep 08, 2012 12:02 am

Postby stanori » Tue Sep 11, 2012 8:33 pm

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
_________________
Steve Tanori
Product Support
Red Gate Software Ltd.
866-997-0378
stanori
 
Posts: 84
Joined: Mon Apr 23, 2012 1:13 pm

Postby apisano » Tue Sep 11, 2012 8:48 pm

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.
apisano
 
Posts: 2
Joined: Sat Sep 08, 2012 12:02 am

Postby stanori » Tue Sep 11, 2012 8:59 pm

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.
_________________
Steve Tanori
Product Support
Red Gate Software Ltd.
866-997-0378
stanori
 
Posts: 84
Joined: Mon Apr 23, 2012 1:13 pm


Return to SQL Multi Script 1

Who is online

Users browsing this forum: No registered users and 0 guests