Is it possible to copy system DBs to another server?

290 pts.
SQL Server 2005
SQL Server import/export
SQL Server migration
SQL Server replication
Hi, I am at total lost at trying to copy system db(master, model, distribution, etc) from a 2005 server on one machine to another 2005 server on a different machine, so all the logins, job, etc are also copied to the new server. I have tried to google this problem and am at a lost to how to do this. Is possible to even do this, if it isn't can someone please suggest a good practice at how I should go abouts at create a standby server from a production server. Basically what I have now is a script to copy and restore all the user dbs on the prod to standby server, but what about the logins, jobs, alerts, replication data on the production server, how do I go about copying these over so the standby server will still function? I am really at a lost so any input and idea will be greatly beneficial to me. Thanks.

Answer Wiki

Thanks. We'll let you know when a new response is added.

In order to restore system databases (master and msdb you have to have the database service in single user mode. In order to do this you have to manually start the SQL Server from the command line with the -m and -c switches. This will allow a single user to connect a single process and restore the master and msdb database from backup.

As the distribution database also needs to be restored as a system database I would assume this database needs the same attention. However I’m not positive as I’ve never had to restore a distribution database before.

When restoring your published database be sure to use the KEEP_REPLICATION flag in order to keep the replication settings intact.

The model database does not need to be restored as it is simply an empty database which is used as a template with new databases are created.

It is recommended that you simply create the logins, jobs, etc on the destination database and manually keep them in sync as moving these databases from server to server is not an easy task.

Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: