Solved

Restore master db

Posted on 2007-11-14
5
465 Views
Last Modified: 2008-02-01
Hi All,
Using SQL 2005 , what is the best way to restore a backup of the master table to a new SQL Server instance from another server?

regards,
Bill
0
Comment
Question by:woyler
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20305156
Ehm, Bill, It's the master database :-)
That is a nifty thing to do, but well documented by MS:

In general for system databases:
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
more precisely for the master:
http://msdn2.microsoft.com/en-us/library/ms175535.aspx

Hope this helps ...

0
 
LVL 18

Expert Comment

by:Yveau
ID: 20305164
... make sure everything is exactly the same as on the old server, otherwise you will have some challenge !
Thinking of file locations, hardware, machine name ... just to mention a few.

Hope this helps ...
0
 

Author Comment

by:woyler
ID: 20312032
I guess my question , more specifically is the following;
I have full backups of Server A (all sys DBs and user db)  If Server A were to take a dirt nap and I needed to restore these backups to Server B, how do I get around the issue with the login accounts being restored?  I can restore all DB's except for the master , which contains the login info.  I have yet to find a solution that explains how to do this especially if the file structures  are different on the 2 servers.
I see you point out these issues in your 2nd response as challenges.  So what is best practice for a recovery scenario when you need to restore a DB to a different server?

thanks,
Bill
0
 
LVL 18

Accepted Solution

by:
Yveau earned 500 total points
ID: 20313027
Install a 'empty' SQL Server, then restore the master as described in the articles.
All user databases will be marked 'suspect' as the files are not in place.
Remove all user databases and either
- restore them from a backup set
- attach them by pointing to the files (on the new location).

Everything should be back in business.

Hope this helps ...
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20368640
Glad I could be of any help.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question