1

At work we have a productive SQL Server working, and we create backups of each database periodically, including system databases master, msdb and model.

I'm trying to restore the whole datawarehouse into another SQL Server instance as a test, to write documentation in case of total loss. I was able to restore the datawarehouse databases, msdb and model, but the problem started when I restored master.

I restored it in single-user mode, with replace and it worked, but then my SQL Server instance stopped and now I can't restart it.

It says something like 'request error or the service did not response in time'. I've checked the log folder and here's a part of the error message:

Service Master Key could not be decrypted using one of its encryptions. See sys.key_encryptions for details.
An error occurred during Service Master Key initialization. SQLErrorCode=33095, State=8, LastOsError=0.

And

Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf for file number 1. OS error

For each of the databases.

Is a restore of the master database necessary or can I pass on it? What will I lose?

Hope you guys can help me with this issue, and sorry for my English.

Improve this question
3
  • If you restore master you often need to backup/restore the SMK also. Try rebuilding master learn.microsoft.com/en-us/sql/relational-databases/databases/… and then restore the SMK, then try restore master again.
    – Charlieface
    Commented Jan 16 at 22:11
  • I've checked for SMK in my SQL Server executing "SELECT * FROM sys.certificates" and "SELECT * FROM sys.simmetric_keys" and it returns empty tables. Should I create a SMK or how can i fix it? Thanks
    – santiago arnesano
    Commented Jan 17 at 13:55
  • SMK will not be in either of those places. The SMK is stored in the registry, it always exists. You can regenerate it using ALTER SERVICE MASTER KEY REGENERATE but that probably won't help your issue. You need to back it up and restore as mentioned.
    – Charlieface
    Commented Jan 17 at 15:40

1 Answer 1

Reset to default
0

Is a restore of the master database necessary or can I pass on it? What will I lose?

The following is stored in the master database:

  1. List of databases and paths to them
  2. Logins
  3. Server settings

If you use the default server settings, you work under the sa user, and you do not have many databases, then in principle you can do it without a backup of the master.

However: Restore of the master is needed precisely in order to quickly return the server settings that were before the breakdown - that will simplify your work in case of emergency recovery (obviosly, minimizing downtime is important, besides, you may simply not remember what, how and were was configured before).

Also in the documentation SQL Server there is a recommendation to make a backup of the master database

Regarding the error you get when restore. You probably need to restore the certificate. If you do not have a backup of the certificate, then, unfortunately, you will not be able to restore the database. After creating the certificate, you must make a backup of the certificate.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.