Home > IT Answers > SQL Server > Is it possible to copy system DBs to another server?
Db23
290 pts.
 Is it possible to copy system DBs to another server?
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.

Software/Hardware used:
ASKED: October 9, 2008  3:38 AM
UPDATED: October 9, 2008  1:23 PM
RELATED QUESTIONS
Answer Wiki:
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.
Last Wiki Answer Submitted:  October 9, 2008  5:41 am  by  Denny Cherry   64,550 pts.
All Answer Wiki Contributors:  Denny Cherry   64,550 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.

Denny Cherry
 64,550 pts.