3

Summary of Question

My question is for SQL Server 2017 is there a way you can re-attach the model database (without rebuilding master or restoring master) if it has been dropped given that both of the following fail

-- both of these do not work
sp_attach_db 'model','G:\model\model.mdf','G:\model\modellog.ldf'

CREATE DATABASE model
ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\model.mdf'),
(FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\modellog.ldf')
FOR ATTACH;

Setup for my question

I followed an answer https://dba.stackexchange.com/a/39494/57571 for moving the model database which is not correct for SQL Server 2017 (I now know that the correct procedure to follow is https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-2017).

As part of it's instructions it had the following:

sp_detach_db 'model'
sp_attach_db 'model','G:\model\model.mdf','G:\model\modellog.ldf'

Problem was that every time I tried that it would stop the running sql server (since it was trying to access the now dropped model database)

I tried using

CREATE DATABASE model
ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\model.mdf'),
(FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\modellog.ldf')
FOR ATTACH;

But would only get this error message

Msg 33401, Level 16, State 1, Server SQL01.TEST, Line 1
FILESTREAM database options cannot be set on system databases such as 'model'.

I ended up having to backup the databases and rebuild the master database using the instructions here https://docs.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-2017

So rebuilt the master database with:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="NT AUTHORITY\SYSTEM" /SAPWD=StrongPa$$word

Then manually restored all the users and databases which existed beforehand.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.