Restore Vault from full harddrive backup

[Mordachai]

Hi - I expect there must be support documents on this already, but not sure where to find them?

Our Vault Server PC died yesterday. I've got it up & running again - but have chosen to build it new, piecemeal, instead of doing a full restore from backup image (I can still possibly do that if required - however, whatever was wrong with the Windows Update on that PC remains true - so a full restore from that image seems futile or doomed.)

So I'm trying to restore Vault from the Windows Server Backup - which has a full copy of the machine - including drives - a day before it died. What I do not know is:

1. How do I restore the SQL Instance that Vault was using to a new virgin PC?
1a. I have installed the same version of SQL Server to the server using instance 'SOURCEGEARVAULT' (same named instance I was using before).
1b. Now - how in the world do I restore the previous SQL-Server data to this newly installed instance when what I have is a copy of the files, but no live server to work with, and no "SQL Server backup" - rather an image of the filesystem from a few days ago?
1c. Can I simply shut down SQL Server services, overwrite the data at the same location (where the blank instance was generated - C:\Program Files\Microsoft SQL Server\MSSQL13.SOURCEGEARVAULT)?
1d. Is there some special action I need to take to make this restore work properly? (I have never tried to manipulate the underlying files of a SQL Server instance before)

2. Once I have a SQL Server installed again - how do I restore the Vault SQL Server instance from a drive image to a live running system?
2a. How do I register / enable that to be a valid SQL Server instance, and not just a bunch of files on the HDD?

I have notes as to how I configured SQL Server originally - so I intend to follow that again - but I have never had to restore in this manner (I've only migrated from older PC to newer ones - which is a different process).

3. What else do I need to know / do?

[joe_sg]

Unfortunately there is no way you can take an attached, running SQL Data file that contains a live database (as it was when the backup was made) and connect that to a new SQL Server instance. SQL will see that the file is already in a "connected" state and refuse to attach it.

The good news is that if you can get your SQL data moved over to the new machine (I'll explain), getting Vault to recognize it as its own is pretty easy.

Longhand instructions, for perspective:

What you will need to do to restore your SQL Vault databases: You will need to restore your old, dead server from the image or backup you have. You can restore it to anything, including a VM. I would recommend a VM for simplicity and repeatability. Once that temporary server is running again, you can Backup the SQL databases.
Now, instead of live SQL data in a file, you have a SQL Database Backup file that will be portable to your shiny new server.

So you would next copy those SQL database backup files to New Server. If by chance you have a Vault Server installed on New Server, you should uninstall it before proceeding with SQL. Then Restore the newly copied database backups to that SQL instance. Now you have a functioning SQL Server with all your valid Vault databases attached.

Now you can install a fresh Vault Server (being sure to tell the installer to KEEP the existing databases). Vault will then be paired to your new SQL instance, and SQL will be set up with Vault as a SQL user with permissions to the databases. Then you should be up and running.


Here is a step-by-step version:
(Refer to viewtopic.php?t=924 steps A) 1) B-1) C-1))

1. Restore your old server to a temporary (VM?) location using the recent image or backup.
2, Back up the Vault databases. (see A) from the above link) (see 1))
3, If you have Vault Server installed on New Server, uninstall it now.
4. Restore the database backups to New Server.
5. Refresh Management Studio and look for databases in SINGLE-USER mode. Better yet, run this query on each DB to be sure: https://stackoverflow.com/questions/250 ... multi-user
6. Now, install Vault Server as you normally would. (see B-1)
7. That's it. You should be up and running with Vault. (see C-1))

I hope this helps. Please write back with any questions.

[Mordachai]

Thank You!

Great thought about restoring to a VM. I hadn't considered that option!

As it currently stands, I've got the server restored from Windows Backup as a full restore - which means our Vault server is restored as well... but I'm left with the real possibility that if I reboot this machine, it will die.

I've disabled the windows updates and cleared the Windows Updates download cache. So hopefully it can handle a reboot and will not install whatever it was that killed it before.

I have added a maintenance plan using SSMS to ensure we have regular backups of the Vault DB itself, separately from the live data store which seems semi-useless.

Hopefully this means I'm getting somewhere useful. With full database backups, hopefully I can use your instructions if this machine turns out to be unstable still and rebuild it and attach SQL Server to it and then attach Vault to that.

Much appreciated.

[joe_sg]

That's great. It sounds like you are well protected against another crash. However, in case that does happen again, please make sure you will be able to access the latest SQL backups post-crash
Good luck! Let me know if I can help.