• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

sql bare metal restore

im backing up my sql servers as follows
taking a vmdk image of the c drives
taking a file system backup of all drives except c dive excluding databases
taking a database level of all databases

this works perfect for most of my sql servers that have the master database located on the c drive as all i need to do is the following

restore vmdk which gives me back a working server with sql installed and running
the master databases as its on c drive exists and the service is started
i can then restore the file level backup whick restores and files and folder structure
then i can restore all my databases.

however i face a problem when someone has located the master database on a another drive like e drive etc
then when i restore vmdk the master datbases does not exist and sql service can not be started.
i cant restore the master databse as sql service is not started
how can i rebuild the master database ore restore it easily in a dr scenario

im using sql 2012 but have also 2008 and 2005
0
dougdog
Asked:
dougdog
  • 9
  • 3
  • 3
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>taking a database level of all databases
this is good, as it includes the master db.

so, you need to restore the master db, which in short means to startup the instance in single user mode, do the master db restore (from the backup as normal), and start the instance again (as the restore of master db finishes with a sql instance stop.

see here : http://technet.microsoft.com/en-us/library/ms190679.aspx
0
 
dougdogAuthor Commented:
will this still allow me to do that even when the master.mdf does not exist
0
 
dougdogAuthor Commented:
the master dataabse deos not exist after a restore as it is not backed up in the image
it is only backed up in the database backup
and the database rstore wont work because the master database is not up and running
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Jim P.Commented:
The way I've done it in the past is to use the sp_help_revlogin as a scheduled task to dump the results to a file on disk. I've also built the rest of my maintenance jobs as a script.

Then from there if I have to do a bare metal move/restore, it is a matter of installing the SQL Server. Then I restore the databases and use the sp_help_revlogin output to recreate the users. Then use the maintenance jobs script to recreate the jobs.

I don't worry about restoring the system databases because I don't need them.
0
 
Anthony PerkinsCommented:
Are you not doing a SQL Backup of the master database?  If the answer is yes, then you can restore that database.  If the answer is no, they why not?
0
 
dougdogAuthor Commented:
i am but i cant restore it
i need to get into sql management studio
and i cant get into it because sql management studio because the service cant run without the database
0
 
Jim P.Commented:
You do the base install of SQL Server to match the version thaqt you are restoring.
Then you run the restore from the command line as outlined in the link in Guy's post.
0
 
dougdogAuthor Commented:
as i have the server restored as an image
is there no easy way to rebuild the master database to get it working then do the restore
if i have to remove sql then reinstall it i need to know databse locations etc
0
 
Jim P.Commented:
If it wasn't on the C: drive then yes. Or follow the steps here for 2008 and above to get the system databases back.

Then do the restore. But it still has to be done from the command line and not from the SSMS.
0
 
dougdogAuthor Commented:
tried the above
it does re create the system databases however i still cannot start the service
when i check the evnet viewer it says

source mssqlserver eventid 17204
FCB  open fialed could not open the file (location) mastlog.ldf
 for file number 2 os error 5 (access is denied)
0
 
Anthony PerkinsCommented:
i am but i cant restore it
i need to get into sql management studio
and i cant get into it because sql management studio because the service cant run without the database

No, you do not need SSMS.  You restore the master database from the command line.  Let me know if you need details.
0
 
dougdogAuthor Commented:
yes please
0
 
Anthony PerkinsCommented:
For obvious reasons, you cannot restore the master database when the service is running unless it is in single user mode.
Pinal Dave has a good article on how to restore the master database see SQL SERVER – Restore Master Database – An Easy Solution

If on the other hand you prefer a step by step process with pictures you can go to youtube and see How to restore SQL Server 2005-2008 master database
0
 
dougdogAuthor Commented:
most of these solutions are on the basis that a master database exists  and the service can be started
my problem is
i have a server restored but the only copy of the master database i have is in a backup by a 3rd party backup tool
i cant restore this database because i cannot start the master database due to the file not existing
0
 
dougdogAuthor Commented:
for the master database to be started the following databases needs to exist
master
model
resource database
0
 
dougdogAuthor Commented:
this was the solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 9
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now