January 31, 2012 at 9:38 am
I want to move SQL server 2005 databases from one site to another. The would like to just detach and attach all databases including system databases. Is this way the best way and what other issues of concern when attaching the system databases if any?
January 31, 2012 at 9:51 am
Attaching system databases is not possible, where as restoring the system db's is possible. However, restoring system db's from one SQL Instance is not done unless you know why you are doing it.
Detach / attach is one of the possible ways to move, but you have to remember the databases will not be available on the Source (Original) SQL Server. You might want to do it by restoring backups (if you have configured db backups on the source SQL Server)..
However, you will have to give more details on what is that you are trying to achieve, to enable us to understand what is needed and give a better suggestion / solution. Without more info, it would be more of guess work from those who want to help you..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
January 31, 2012 at 10:23 am
Detach and Attach is the best way for the user databases. For system, you may be able to do some of them (model perhaps) but not master for sure. I did read an article whereby you modify the startup parameters and change the file locations there, stop SQL and then move the master.mdf & ldf but its 2008 R2.
Here it is:
http://msdn.microsoft.com/en-us/library/ms345408.aspx
'Only he who wanders finds new paths'
January 31, 2012 at 10:42 am
Thanks for the info. I guess i am looking for suggestions on Moving from one site to another and the best way to do it. We plan on copying all the backup files and restoring. Should I be able to restore everything without any problems? And what else do I need to be concern with if I restore system and user databases. (sql server 2005 & windows 2008 r2).
January 31, 2012 at 10:53 am
Also should I take the approach of detaching all user databases and restoring system databases? Is the efficient? Should I have any issues with different patches of sql server 2005?
January 31, 2012 at 10:59 am
Backing up all the databases and restoring them is hte best possible way.
Make sure to check the integrity( DBCC) of all the databses before you restoring them.
Regards,
Skybvi
Regards
Sushant Kumar
MCTS,MCP
January 31, 2012 at 11:14 am
The bottom line is we trying to move a lot of databases. The approach we are taking is to take a copy of the databases and transfer them to another site and begin to restore. Looking for suggestions on whether to take this approach on all system and user databases or detach user and restore system? And maybe I'm not understanding detach...I thought if I detach the user databases then I could copy the mdf and ldf to a flash drive of some sort and then just attach the files back on the original server. Help?? with understanding??
January 31, 2012 at 12:18 pm
I would choose backup restore as my choice as I would not have to worry that db's on original Server are offline during the copying phase. If the databases are very large db's then it might tale longer time to copy them (there by increasing the downtime).
About system databases, there is no point in having tempdb and model db's restored, so that leaves with MSDB and Master databases. I understand why you might want to have msdb, but I am not sure why you want to restore master database on the other server too..
If you are planning to restore Master db, then you should be aware that it would / might not work if the SQL Server names are different or the if the SQL Editions and Versions are different.. So be aware of that before you do it..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
January 31, 2012 at 12:51 pm
I would go with backup/restore for the user databases and not worry about the system databases. Build the systems at the new site, script out all jobs and logins (sp_help_revlogin), restore the user databases and apply the scripts to recreate the jobs and logins.
I don't like restoring system database unless we are recreating the exact same system due to a disaster.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 31, 2012 at 1:28 pm
I know there could well be no difference really but...
If the databases are not intended to be online again on the original server then wouldn't an attach/detach be better, only suggesting this as its considered to be best practice when moving to a new instance?
Obviously take a backup first regardless...
'Only he who wanders finds new paths'
January 31, 2012 at 1:46 pm
Restoring MSDB is this the 'norm' or just script the jobs and logins...? Can I have some advice on when to restore the Master and are you saying once the user databses are down I'm okay with the master database..? Just a question I've always had. In the past i've never restored the master, just restore the user db and script the logins...
January 31, 2012 at 1:51 pm
I am not sure if attach / detach is considered as a best practice, but I would be more inclined to do it using backup / restore, unless there is a reason why it can't be done (backups can't be done due no local disk space etc etc). Even in such a scenario, it is possible to backup to a network location and restore from that location..
As always, there is plus and minus for both detach/attach and backup / restore..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
January 31, 2012 at 1:51 pm
Thanks this is definitely the route we're planning to go and usually the way I do it. Just needed some validation...
January 31, 2012 at 1:53 pm
Kasnut (1/31/2012)
Thanks this is definitely the route we're planning to go and usually the way I do it. Just needed some validation...
You are welcome..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply