November 18, 2007 at 11:01 pm
how can i move master database from one server A to another server B .(server A production server)
and
the best way of moving user database from one server to another server
some one pls help........
November 19, 2007 at 12:30 am
For moving use databases there are two options:
1) detach/attach
2) backup/restore
November 19, 2007 at 2:02 am
Suresh B. (11/19/2007)
For moving use databases there are two options:1) detach/attach
2) backup/restore
Don't forget the "copy database" option.
Markus
[font="Verdana"]Markus Bohse[/font]
November 19, 2007 at 2:09 am
Remember that you can't just move the as any user DB. it has lot of constraints. you need to have the edition, verion and the build number smae to restore or move a master datbase. For user database use the
detach/attach method or backup/restore method.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
November 19, 2007 at 2:25 am
Since this is a production server, DETACH option may not be suitable, as it makes the database OFFLINE.
November 20, 2007 at 1:40 am
Moving user databases is DBA bread-and-butter - the basics that earn your keep. Backup and restore with EXEC sp_change_users_login after to tidy up. I keep restore scripts for each server and many databases, especially if they are copied regularly or have multiple files.
As for master - why on earth do you need to copy it? After several years as DBA I have never needed to do that! Yes I've one or two stored procedures in there to be copied to new servers, but they are scripted and the script run against the new server's master DB. Likewise msdb and jobs.
November 20, 2007 at 2:32 am
For the master database I would use a backup of the production server master database.
Start the SQLServer in single user mode. (sqlservr.exe -m)
Open SSMS and restore the master database using a "Restore Database master from...." script
You can then restore all the user databases on the non production server.
November 20, 2007 at 5:58 pm
Have you built in any objects, such as stored procedures in the master database? If not, and all you have to move is the users, there are ways to do that without backup/restore that are simpler.
How to transfer logins and passwords between instances of SQL Server
K. Brian Kelley
@kbriankelley
November 21, 2007 at 11:37 pm
Ummmm seeing as you can't detach system databases you can't do it to Master. And if you are trying or needing to you are in a whole world of hurt. None of the databases will restore unless previously detached. Collations could be wrong. I would even be surprised if SQL even starts. Moving master - Not Good !!
November 22, 2007 at 8:45 pm
Malcolm Daughtree (11/21/2007)
Ummmm seeing as you can't detach system databases you can't do it to Master. And if you are trying or needing to you are in a whole world of hurt. None of the databases will restore unless previously detached. Collations could be wrong. I would even be surprised if SQL even starts. Moving master - Not Good !!
Agreed, I wouldn't consider moving master over in this manner. Doing a restore from backup, yes, especially given the small size. But if all that needs to move are logins, then script 'em out and apply the script to the new server.
K. Brian Kelley
@kbriankelley
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply