0

We had a server running Microsoft SQL Server 2014 databases on Hyper-V. I exported it (with C: and D: drives) and opened on other machine.

I could not start the MSSQLSERVER service because it said the master db needed to be rebuilt.

I've rebuilt the master db, but now the databases are empty, I cannot see databases except master, model, msdb, and tempdb.

I have all databases (mdf and ldf files) in this folder D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA. How to restore them and not lose all functionality?

Improve this question
2
  • Did you migrate and attach the volume (VHD) that represents the D drive of the VM as well?
    – alroc
    Commented Nov 15, 2020 at 16:54
  • Yes I did. C and D drives
    – Marin
    Commented Nov 15, 2020 at 17:01

1 Answer 1

Reset to default
2

I have all Databases (mdf and ldf files) in this folder D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA how to restore them and not lose all functionality?

When you rebuild master, it loses track of where the user databases are. You just need to attach them, and recreate the logins for any orphaned users.

And also consider linked servers, scheduled jobs and everything else which is stored outside the user databases and was scratched by the rebuild.

Improve this answer
4
  • The thing is, that admin credentials cannot contact domain controller and let me into SQL server. It is maybe off-topic. And other credentials have no rights to attach or recreate logins. My brain just thinks only to clone DC. Or are there no such brutal ways?
    – Marin
    Commented Nov 15, 2020 at 19:16
  • 1
    If you're logged on as a local administrator and can't connect to SQL Server as a sysadmin, follow the procedure here: learn.microsoft.com/en-us/sql/database-engine/configure-windows/…. If you can't contact the domain controller to authenticate, then that's a separate issue, but you can fix the SQL Server using a local admin account.
    – David Browne - Microsoft
    Commented Nov 15, 2020 at 21:33
  • Do you have enough privileges to use SQLCMD? support.atandra.com/article/…. If privileges are the problem you have to fix that first: contact your sysadmin.
    – Francesco Mantovani
    Commented Nov 17, 2020 at 15:17
  • You said "recreate the logins for orphaned users" The link "orphaned users" said "execute the following statement in the user database:" - what is user database? Where to execute this query?
    – Marin
    Commented Feb 21, 2021 at 20:03

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.