SQL 2008 R2 DR Restore Question

We practice restoring our core servers twice a year. My question is in regards to restoring one of our SQL servers that has failed to be restored in the past. Please note the SQL databases are backed up via SQL then backed up as a flat file via TSM.

I was going to restore the data drive first since this is where the service reside before restoring the C and system state so the directory structure is in place for the services. After the server comes back up hopefully I will need to restore the master DB followed by the application DB's correct
LVL 21
compdigit44Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
We generally do the following:-

1. Restore the VM from backup.

2. Then Restore the SQL database.

So your practice is similar, we use DPM to backup our SQL servers.
compdigit44Author Commented:
A system state restore & data restore should cover this correct?
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
Correct or complete VM (VMDK) + DB
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

compdigit44Author Commented:
My concern if with the master & system databases. Since these are active DB's they are backed up via a SQL job which TSM then grabs the flat file. If I do a system state restore the master DB would be missing correct?
Daniel_PLDB Expert/ArchitectCommented:
Hi,
Yes, indeed, you won't be able to start any other restores without master database files. You have some options here. You can keep initial system databases files backed up as flat files (e.g. after server install, before SQL patching, etc.). Then in your restore procedure append some steps to restore system database files and then restore most current backups starting from recovering master database.
To be able to restore master database you need master database to be able to start SQL in single user mode ;)
Another way is to rebuild system databases after recovery and then push off backups to recover starting with system databases.

Some links to review:
Restore the master Database (Transact-SQL)
The SQL Server Instance That Will not Start [Gail Shaw]
Rebuild System Databases

Regards,
Daniel

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
compdigit44Author Commented:
So after I do the system state restore I "should" not reboot the host but restore the master DB corrcet?
Daniel_PLDB Expert/ArchitectCommented:
Hi,

That's not exactly what I mean. After system restore you need to recover SQL Server system databases, master at least (plus mssqlsystemresource database files). It doesn't matter if you perform SQL Server recovery before or after restart.
I'm not sure where you have installed SQL Server, only shared components are always installed in %systemroot%\program files. If that is the case you need to always backup your SQL Server binaries along with system backup.

Regards,
Daniel
compdigit44Author Commented:
What will happen to the original SA password will it change?
Daniel_PLDB Expert/ArchitectCommented:
Please read thru links I provided before, SA password needs to be provided during master rebuild procedure. In that other case when you recover system databases backed up as flat files your SA password will be from the time when backup was taken.

Regards,
Daniel
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VMware

From novice to tech pro — start learning today.