Home / server / Questions / 1021684
Accepted
Krackout
Asked: 2020-06-17 03:19:09 +0800 CST

MS SQL 2008 R2 disaster restore

  • 772

I need to restore an MS SQL 2008 R2 server. The original server was completely destroyed, nothing to salvage. Yet there is backup of all data, plus system databases, master, msdb, model; .bak files.

I setup a new machine, exactly same version of sql, 2008R2 SP2, and tried to restore master db first. The restore succeeded, but then the sql service could not start, using the recovered master. It seems that every path on the old-destroyed server was custom, data DBs, system DBs, even sql executables.

Is there any way to figure out the paths used on the old server, even for sql executables?
I suppose I'll have to uninstall-install sql server again, to have all custom paths set, in case we locate the custom executable paths, is this correct?
Also, for future reference, on an MS SQL server, is there any way to export all these paths?

restore sql-server-2008-r2 disaster

2 Answers

  1. Consider NOT restoring the system databases. Just restore the data containing files, and hopefully someone was smart enough to make them contained (so they contain the users).

    • 1
  2. Best Answer

    I followed this procedure to figure out which were the paths used on the destroyed server:

    An older System State backup of the destroyed server was located by the local admin. So I used this backup to extract the registry and find the MS SQL executable and instance path. HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Setup
    HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL10.MyInstance

    On an other, lab SQL server, I made a new database and then imported the destroyed server's masterdb backup.

    Using this query on the imported old master db, data paths of old sql server were located:
    SELECT name, physical_name FROM destroyed_master

    (altered this query which outputs data paths on a live sql server)
    SELECT name, physical_name AS current_file_location FROM sys.master_files

    Having recovered the paths, I made a new clean install of Windows & SQL, used the recovered paths while installing, and then master was restored successfully. SQL service got up and running, data restore followed and the server is back.

    • 0