Just over a week ago I was speaking at SQL Saturday #662 covering rebuilding system databases and as I was discussing moving system files back into their normal location, when not kept in the default location, I was asked if there was a way to do it during the rebuild process. One of the attendees suggested that maybe removing the /QUIET parameter would enable one to enter a path. I had not tried this so today I tested.
I tried first removing the /QUIET parameter. This doesn’t do anything more than allowing the following to pop-up on screen. Since this isn’t much help I think keeping the /QUIET parameter in place is fine…if you want to see this pop-up to know it is doing something that is up to you.
Next I did some reading on the Microsoft Docs site (https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-2016-from-the-command-prompt) on installing SQL Server from command line, which also has the section on rebuilding system databases, and found the /INSTALLSQLDATADIR parameter in the installation section…it was not mentioned in the rebuilding system databases section. I added it into my code with the correct path and received an error.
Even though you cannot specify where the rebuild will create most of your system database files I did find that there are parameters you can use with the rebuild action to help you out with TempDB:
- /SQLTEMPDBDIR
- /SQLTEMPDBLOGDI
- /SQLTEMPDBFILECOUNT
- /SQLTEMPDBFILESIZE
- /SQLTEMPDBFILEGROWTH
- /SQLTEMPDBLOGFILESIZE
- /SQLTEMPDBLOGFILEGROWTH
So at least you can put TempDB back in place right away…however once you restore master database those settings will be put back in place automatically.
There are two other parameters that may be of interest /INSTANCENAME which would be very useful when you need to restore the system databases for a named instance and /SQLCOLLATION which again would be useful when restoring a server that is not using the default.
