0

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?

Krackout
  • 1,559
  • 6
  • 17
  • What exactly does the startup log say? And there is no need to export the paths - backups should restore to the same locations (which should be documented).a – TomTom Jun 16 '20 at 11:34

2 Answers2

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).

TomTom
  • 50,857
  • 7
  • 52
  • 134
  • Not an option, the users are on master only :( – Krackout Jun 16 '20 at 11:25
  • Then wh at is the question: a restore should restore EXACTLY the same layout. Obviously you ahve it documented (not just pulled random backups) - make the new server look EXACTLY like the old one. – TomTom Jun 16 '20 at 11:29
  • No, there was no documentation of the layout, of the paths. That's the question, can I get the paths somehow, having only .bak files of all DBs, including system, msdb, model. – Krackout Jun 16 '20 at 11:34
  • Read the logs then. SQL server is writing logs. Also. master does not contain the location of the executable paths - a install sets them in teh services. THis makes zero sense then. Data is not code - the install sets up the paths. The path to master db is in the service startup parameters. – TomTom Jun 16 '20 at 11:36
0

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.

Krackout
  • 1,559
  • 6
  • 17