Solved

Restore master db

Posted on 2007-11-14
5
463 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
  • 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Backup skipping a few tables 7 58
How to place a condition in a filter criteria in t-sql? 12 83
Query to Add Late Tolerance 10 82
Replace Dates in query 14 55
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

685 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