June 3, 2009 at 2:58 am
That's not a particularly good idea
Take note of what Gail says. What is the ultimate goal you are trying to achieve?
If your goal is to have the same set of logins in the new machine as are in the old, it is FAR safer to script out the logins from the old system and script them into the new system compared to restoring the master DB. If it is to get the same set of sp_configure options, then create a script to configure these (or see what FineBuild can do for you).
As Gail and others have said, master contains some instance-specific information. You risk problems occurring at some point in the future if you have restored master from one machine to another.
It is important to have a backup of master, but you should use it only to recover the originating master DB.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 3, 2009 at 3:24 am
Hi ,
actually i give you full scenario
We have a Desktop machine which is currently a sql server 2005 server
with Developer Edition (64-bit) . Now we got a new server level machine and now the mission is to move the sql server ( database with all instances )
But the another requirement is , we need to install ENterprise edition
So please tell me how can i achieve it ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 3, 2009 at 4:35 am
Personally, I would break this down as follows:
1) SQL Server install
2) Transfer any required logins, etc from the old to the new machine via scripts
3) Transfer the user databases from the old to the new machine preferably via backup and restore
4) Test everything works as expected
I would try to avoid taking any system databases from one machine to another, especially where the target machine is in Production. I want to be as certain as possible that the production machine contains no hidden surprises that cause a problem some months later.
FineBuild has SPs and scripts that can transfer logins, jobs, DTS packages, etc from one machine to another. Even if you do not use FineBuild to build your new SQL instance, you may find the SPs useful.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 3, 2009 at 5:18 am
But how would you go for JOBs migrations ?????
actually new server needs to have cloning of existing server.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 3, 2009 at 7:08 am
bhuvnesh.dogra (6/3/2009)
But how would you go for JOBs migrations ?????
Method 1:
Script the jobs out, run the script on the new server
Method 2:
Use the SSIS Transfer Jobs task.
Either way, jobs aren't in master and it's copying master that we're talking about here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2016 at 1:08 pm
My blog "http://sqlserverrestoremasterdb.blogspot.com/" will be helpful for you
Sheraz Mirza::hehe:
October 27, 2016 at 1:28 pm
Sheraz.Mirza (10/27/2016)
My blog "http://sqlserverrestoremasterdb.blogspot.com/" will be helpful for you
May help others going forward (I haven't read your post, so buyer beware), but for the OP this is a 7 year old thread, they should have resolved this by now.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply