Solved

sql  bare metal restore

Posted on 2014-04-08
16
384 Views
Last Modified: 2014-04-28
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
Comment
Question by:dougdog
  • 9
  • 3
  • 3
  • +1
16 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39985732
>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
 

Author Comment

by:dougdog
ID: 39985903
will this still allow me to do that even when the master.mdf does not exist
0
 

Author Comment

by:dougdog
ID: 39985940
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 39987288
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39987674
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
 

Author Comment

by:dougdog
ID: 39988413
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 39988438
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
 

Author Comment

by:dougdog
ID: 39988448
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 38

Expert Comment

by:Jim P.
ID: 39988472
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
 

Author Comment

by:dougdog
ID: 39988820
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39989326
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
 

Author Comment

by:dougdog
ID: 39989449
yes please
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39990388
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
 

Author Comment

by:dougdog
ID: 40001030
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
 

Accepted Solution

by:
dougdog earned 0 total points
ID: 40017423
for the master database to be started the following databases needs to exist
master
model
resource database
0
 

Author Closing Comment

by:dougdog
ID: 40026818
this was the solution
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now