Monday, August 28, 2017

HELP! HELP! My system databases have disappeared revisited

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.

Monday, August 14, 2017

Speaking at SQLSaturday #662

I will be speaking at SQLSaturday #662 in Sioux Falls, SD on August 19th 2017!

I will doing a new presentation on regenerating system databases as well as LDF files called HELP HELP my system databases are missing!

You can find this information in some of my previous blog posts such as HELP! HELP! My system databases have disappeared and Where oh where did my LDF go?.

Register today and I hope to see you there!

Wednesday, July 12, 2017

Speaking at SQLSaturday #654

I will be speaking at SQLSaturday #654 in Omaha, NE on July 22nd 2017!

I will doing my presentation on "My Top 10+ Replication Tricks"

In the next few weeks I will be blogging on the content of the presentation...I know I also said that back in March; it has been a busy summer!

Register today and I hope to see you there!

Wednesday, February 22, 2017

Speaking at SQLSaturday #584

I will be speaking at SQLSaturday #584 in Colorado Springs, CO on March 25th 2017!

I will doing my upgraded presentation on "My Top 10+ Replication Tricks"

In the next few weeks I will be blogging on the content of the presentation.

Register today and I hope to see you there!

Thursday, January 19, 2017

Where oh where did my LDF go?

This is a follow-up to my previous post on recovering system databases. This will cover several ways to recover missing LDF files.

For this and some other testing I have used the Chinook database found at Codeplex. The convention used in this post is SQL Data files are on the S: drive and LDF files are on the L: drive.

If the database has been cleanly detached and the LDF gets deleted it can be recreated when attaching by using any of the following scripts:

CREATE DATABASE Chinook ON (FILENAME = 'S:\SQL_DATA\Chinook.mdf') FOR ATTACH;

CREATE DATABASE Chinook ON (FILENAME = 'S:\SQL_DATA\Chinook.mdf') FOR ATTACH_REBUILD_LOG;

EXEC sp_attach_single_file_db @dbname = 'Chinook' , @physname = 'S:\SQL_DATA\Chinook.mdf';

If SQL Server has been cleanly shutdown and an LDF gets deleted testing with the Chinook database shows that when SQL Server is restarted it will recreate the LDF.

To see what happens when SQL Server crashes I ran an update query with a BEGIN TRANSACTION but no COMMIT to create an open transaction. I then stopped the SQL Server database engine causing an error.

(1 row(s) affected) Msg 596, Level 21, State 1, Line 0 Cannot continue the execution because the session is in the kill state. Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.

The LDF files is then deleted and when SQL Server database engine is restarted the Chinook database shows Recovery Pending.

Best case is that the location for the LDF is still present or can be recreated by adding a new L: drive. If no backup set is available to restore and get back to as close as the time of crash as possible the database can be made useable by running the following scripts however any transactions in the LDF that were not written to the MDF (NDFs) will be lost.

ALTER DATABASE [Chinook] SET EMERGENCY; ALTER DATABASE [Chinook] SET SINGLE_USER; DBCC CHECKDB([Chinook], REPAIR_ALLOW_DATA_LOSS); ALTER DATABASE [Chinook] SET MULTI_USER;

You will see the following messages before the DBCC results:

File activation failure. The physical file name "L:\SQL_LOGS\Chinook_log.ldf" may be incorrect. The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure. Warning: The log for database 'Chinook' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files. DBCC results for 'Chinook'.

If the database data files must be moved to a different server a “hack attach” must be used.

To do a hack attach you first create a new database with the same name as the broken database. Create the same file structure as the broken database. Take the database offline then replace the data files with the ones from the broken database then bring the database online; you may receive an error message; just click OK. It will show as recovery pending; you may need to refresh the databases list to see this. Now you can run the scripts previously listed to make the database usable. You will see this in the messages window informing you that the files didn’t come from same database originally.

Msg 5173, Level 16, State 1, Line 8 One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. Log file 'L:\SQL_LOGS\Chinook_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously. Warning: The log for database 'Chinook' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files. DBCC results for 'Chinook'.

If the database previously had service broker enabled you will not be able to simply enable it since the database service broker guid will not match that stored in master.sys.databases. If you look at the database properties it will appear to match but that simply tells us that SQL Server pulls the database properties from master database. If you try to enable service broker you will see the following:

ALTER DATABASE Chinook SET ENABLE_BROKER

Msg 9776, Level 16, State 1, Line 8 Cannot enable the Service Broker in database "Chinook" because the Service Broker GUID in the database (AD93393D-7798-486E-8406-1D887CE9655A) does not match the one in sys.databases (D49E0203-E48D-46CE-ABA9-3585BA768ECA). Msg 5069, Level 16, State 1, Line 8 ALTER DATABASE statement failed.

You can fix this by running the following statement:

ALTER DATABASE Chinook SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

This will reset the guid in both places and enable the broker. You can check this with this query:

SELECT [name] ,[service_broker_guid] ,[is_broker_enabled] FROM [master].[sys].[databases]

Tuesday, January 10, 2017

HELP! HELP! My system databases have disappeared.

Disaster! The SAN array that hosts our system databases crashed. Now what do we do?

Fortunately, the array that hosted the C: drive where SQL Server was installed was intact as was our drives for the data files, transaction logs and backups. Here is how we got our server back up and running. First you will need to copy the ISO of the appropriate version and edition of SQL Server to your VM and mount it.

Next run command prompt as admin and execute the following script changing the drive letter, instance name if a named instance, Windows account to add with administrative access and password to fit your circumstances:

E:\>Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="domain\YourLogin" /SAPWD=$0m3P@$$w0rD

Once it finishes rebuilding the system databases it should look similar to the following.

If you look in C:\Program Files\Microsoft SQL Server\...\MSSQL\Data you should now see system data files and transaction logs.

Go to SQL Server Configuration Manager and in the Startup Parameters add the -m flag to start SQL Server in single user mode.

Now in SQL Server Management Studio you can connect a query window, not object explorer, and use the following query to restore master database altering the query to fit your situation. You could also do this from SQLCMD if you prefer. Before running this you need to make sure that new drives with the same drive letters have been created for all system databases.

RESTORE DATABASE master FROM DISK = 'C:\IT\master_backup_2016_09_09_140813_6577087.bak' WITH REPLACE

Once the restore completes it will stop SQL Server. Remove the -m flag. You should also update the location for master database under Startup Parameters and copy the newly restored data and log files to the correct locations. You can now start SQL Server and reconnect SQL Server Management Studio and restore msdb; just be sure the SQL Agent service isn’t running. You can restore model and distribution if needed as well.

You should now be back in business.