1

Our windows 2016 server failed to restart after a windows update yesterday.

In order to avoid the update that crashed it issue (whatever it was), I'm in the process of rebuilding it clean.

So I've reinstalled Windows Server 2016, and SQL Server 2016 (same version we were using before).

I have a full system backup from a couple of days ago viz Windows SErver Backup.

What I am having a hard time finding is information on

"How do I restore a SQL Server instance directly from Win Backup files?"

I have recreated the same named instance we had before (so that the file paths match, etc.) - but I have no idea how to make that work? Can it possibly be as simple as shutting down SQL Server temporarily - using Windows Backup to restore the necessary C:\Program Files\Microsoft SQL Server\MSSQL13.SOURCEGEARVAULT\?

Or is there more to it than that? If so, where can I find instructions for this?

If I had a true backup of the database - and not simply the filesystem - I would have access to lots of how-to's - I've done that before. But I don't have that - I just have the raw filesystem a day before it died.

Any pointers would be appreciated!

Mordachai
  • 111
  • 4

1 Answers1

3

At its most basic: You put the files (the SQL Database and its transaction logs) somewhere that SQL can access them, and then you "attach" an existing database, pointing it to those files.

You can do this through SSMS by right-clicking on Databases and choosing Attach....

However there is no guarantee that this database you are attaching will come up healthy. You might get lucky and it might come online fine - or it might need "repairing" (which usually means truncating corrupt/half present records).

Mark Henderson
  • 68,823
  • 31
  • 180
  • 259
  • Are the transaction logs stored in the same place with the db files for an instance (e.g. C:\Program Files\Microsoft SQL Server\MSSQL13.SOURCEGEARVAULT\)? Or are they stored someplace else? – Mordachai May 30 '18 at 16:34
  • You can put them whereever you want. When you attach the database, it will ask you where the log files are. In the olden days you would put the database and the log files on different spinny disk volumes, but these days with SSDs that's less important. – Mark Henderson May 30 '18 at 16:35
  • @Mordachai WBM usually need a installed OS to be run and restore, but a system-state backup/full system restore will be applied over the target OS. Is that a need to do a granular file system restore ? (as you have the full backup) – yagmoth555 May 30 '18 at 16:48
  • I'm trying the full system restore now - over the top. My worry is that this won't reboot properly (I'm recreating the conditions of failure state that it was in maybe)? – Mordachai May 30 '18 at 17:55
  • @MarkHenderson What I don't know is where on my backup those files would be? I'm not really a DBA - I'm a software engineer really - and I used the defaults when installing SQL Server - so I'm just wondering where I'd find those files? – Mordachai May 30 '18 at 17:56
  • @Mordachai Oh, I see. Sadly I have no idea, because the product could have put them anywhere. If it's a standard configuration, search the disk for `*.ldf` - that should show them up eventually. I don't know where SQL puts them by default I'm afraid (it's been many many moons since I used the SQL Server defaults) – Mark Henderson May 30 '18 at 17:57
  • 1
    By default, the transaction log files (.ldf) are in the same location as the database files (.mdf), both of which will be in the Data folder for that SQL Server instance. Assuming SQL Server was installed using the default settings on the original server that's where you'll find the files in your backup. – joeqwerty May 30 '18 at 18:27