Totally restore of system databases to new server and file structure.

Even since I have work as DBA for many years and participated to a couple of real disaster recovery’s I find this operation quite complicated. During a disaster recovery test for a customer I decided to document this and make it clear.

Test cases

During my test I have done it in two ways. I have started the SQL engine as a service with NET START and with sqlservr.exe. I will describe both ways below. The only difference I found was that I was forced to point out the resource database mssqlsystemresource when I did use sqlservr.exe

There is no difference if you use Management Studio or SQLCMD. I did test both.

In both situation you shall not open any object explorer in Management Studio when you run the single user mode switch –m

Prerequisites

The test was done on a SQL 2012 enterprise SP1 with CU7 installed. The restore is done on a new installed server with different folder structure then the original server.

Recovery by start SQL as service

Start SQL with minimal configuration and single user mode from a command prompt.

NET START MSSQL$SQLEXPRESS /m /f

Restore master

Open Management Studio query window and run the restore command.

RESTORE DATABASE [master] FROM  DISK = N'D:\temp\master.bak' WITH

MOVE N'master' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\master.mdf',

MOVE N'mastlog' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\mastlog.ldf', REPLACE

Stop the SQL engine

If you start the SQL engine now it would be impossible. In the SQL errorlog you will have errors like the one below indicating that the files are missing for the other systemdatabases. This is of course true since the Master database is from another server.

Starting up database 'model'.

2014-02-07 11:05:56.23 spid9s               Error: 17204, Severity: 16, State: 1.

2014-02-07 11:05:56.23 spid9s               FCB::Open failed: Could not open file K:\PR06_data\MSSQL11.DB_PR06\MSSQL\DATA\model.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).

2014-02-07 11:05:56.23 spid9s               Error: 5120, Severity: 16, State: 101.

2014-02-07 11:05:56.23 spid9s               Unable to open the physical file "K:\PR06_data\MSSQL11.DB_PR06\MSSQL\DATA\model.mdf". Operating system error 3: "3(The system cannot find the path specified.)".

How do we solve this then? By adding the traceflag 3608 we can prevent SQL server from automatically starting and recovering any database except the master database. So let’s start it.

NET START MSSQL$SQLEXPRESS /f /T3608

At this point we can use Management Studio again and adjust the path to the other system databases.

USE master

GO

ALTER DATABASE model

MODIFY FILE (name = 'modeldev',

FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\model.mdf')

GO

ALTER DATABASE model

MODIFY FILE (name = 'modellog',

FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\modellog.ldf')

GO

ALTER DATABASE tempdb

MODIFY FILE (name = 'tempdev',

FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\tempdb.mdf')

GO

ALTER DATABASE tempdb

MODIFY FILE (name = 'templog',

FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\templog.ldf')

GO

GO

ALTER DATABASE msdb

MODIFY FILE (name = 'MSDBData',

FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\MSDBData.mdf')

GO

ALTER DATABASE msdb

MODIFY FILE (name = 'MSDBLog',

FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\MSDBLog.ldf')

GO

Now it is possible to start SQL as usual and do the restore of the MSDB database or Model if necessary.

Start SQL from command prompt

The operation by using the sqlservr.exe is quite similar. Start command prompt and run

sqlservr.exe -sSQLEXPRESS -f –m

sqlservr.exe is often found in the folder C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn

Restore Master

User Management Studio and run the restore command

RESTORE DATABASE [master] FROM  DISK = N'D:\temp\master.bak' WITH

MOVE N'master' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\master.mdf',

MOVE N'mastlog' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\mastlog.ldf',

REPLACE

Then stop the service by hit Ctrl C. As with the other test it will be impossible to start SQL now.

Start the SQL engine by adding the traceflag.

sqlservr.exe –sSQLEXPRESS -f -T3608

The only difference from the first test is that I also need to change the path to the resource database. That was not needed in the first case.

ALTER DATABASE mssqlsystemresource

MODIFY FILE (name = 'data',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\Binn\mssqlsystemresource.mdf')

go

ALTER DATABASE mssqlsystemresource

MODIFY FILE (name = 'log',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\Binn\mssqlsystemresource.ldf')

GO

ALTER DATABASE model

MODIFY FILE (name = 'modeldev',

FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\model.mdf')

GO

ALTER DATABASE model

MODIFY FILE (name = 'modellog',

FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\modellog.ldf')

GO

ALTER DATABASE tempdb

MODIFY FILE (name = 'tempdev',

FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\tempdb.mdf')

GO

ALTER DATABASE tempdb

MODIFY FILE (name = 'templog',

FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\templog.ldf')

GO

GO

ALTER DATABASE msdb

MODIFY FILE (name = 'MSDBData',

FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\MSDBData.mdf')

GO

ALTER DATABASE msdb

MODIFY FILE (name = 'MSDBLog',

FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\MSDBLog.ldf')

GO

Now it is possible to start SQL as usual and do the restore of the MSDB database or Model if necessary.

PS: It would also be possible to change the file path by the command below.

UPDATE sys.master_files

SET physical_name = 'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\model.mdf'

WHERE name = 'modeldev'

GO

UPDATE sys.master_files

SET physical_name = 'D:\Program Files\Microsoft SQL Server\MSSQL11.DB_PR06\MSSQL\DATA\modellog.ldf'

WHERE name = 'modellog'

GO

Where are the databasefiles on disk?

A fast way to see where the databasefiles is placed on disk is to use the sys.master_files. With this you can get a view over the database and where their files reside.

select d.name, m.name, m.type_desc, m.physical_name
from sys.master_files m
inner join sys.databases d
on (m.database_id = d.database_id)
order by m.name

Windowing functions - stochastic oscillator

Last time I was looking on the calculation of simple moving average using the new windowing features in transact SQL. I was curious how to do some deeper calculation in the stock analysis area. So today I shall see how we calculate the Stockhastic oscillator. This function use two inputs. D% and K% and the formula are:

%K = 100*(C - L14)/(H14 - L14). C = the most recent closing price  L14 = the low of the 14 previous trading sessions  H14 = the highest price traded during the same 14-day period. 

%D = 3 or 8-day period moving average of %K

The oscillators value are between 0 and 100. Below 20 is oversold and over 80 is overbought. A buy respective sell signals is occur when the %K is cut thru %D from across 80 respective below 20.

CREATE table #calculation (

       [id] [int],

       [aktie] [nchar](10) NULL,

       [datum] [date] NULL,

       [stang] [decimal](18, 2) NULL,

       [K] [decimal](18, 2) NULL

 CONSTRAINT [PK_calculation] PRIMARY KEY CLUSTERED

(

       [id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ARITHABORT OFF

SET ANSI_WARNINGS OFF

GO

INSERT INTO #calculation

SELECT id, aktie, datum, stang

,K = (100*((stang - MIN(stang)  OVER (PARTITION BY aktie ORDER BY datum ROWS BETWEEN 13 PRECEDING AND CURRENT ROW))) / ((MAX(stang) OVER (PARTITION BY aktie ORDER BY datum ROWS BETWEEN 13 PRECEDING AND CURRENT ROW)) - (MIN(stang)  OVER (PARTITION BY aktie ORDER BY datum ROWS BETWEEN 13 PRECEDING AND CURRENT ROW))))

FROM dbo.borsdata

GO

SELECT id, aktie, datum, stang

,AVG(stang) OVER (PARTITION BY aktie ORDER BY datum ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) as  '200 SMA'

,AVG(stang) OVER (PARTITION BY aktie ORDER BY datum ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) as  '50 SMA'

,AVG(stang) OVER (PARTITION BY aktie ORDER BY datum ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) as  '20 SMA'

,K

,D = (AVG(K)  OVER (PARTITION BY aktie ORDER BY datum ROWS BETWEEN 2 PRECEDING AND CURRENT ROW))

FROM #calculation

drop table #calculation

High number of LATCH_EX waits

During an implementation of a new SQL environment for a customer I went into some problem with high nr of waits of LATCH_EX type when they run some heavy batches. I was not sure what this really was. I quite familiar with PAGELATCH and so on. Those are much more common, at least in server I have seen.

The customer asked me to investigate what the problem was. First I read the whitepaper “Diagnosing and Resolving Latch Contention on SQL Server” from Microsoft.

There were some ideas coming up after that. The server did have many CPU cores, 32 to be exact. The database had only one datafile.

First I tried to add more files to the database. I tried to use 4 but there was no big change. I went down from 32 ms in average to 27ms. After investigate sys.dm_os_latch_stats is was quite obvious what the problem was. ACCESS_METHODS_DATASET_PARENT was huge.

Regarding BOL it means Used to synchronize child dataset access to the parent dataset during parallel operations.

I did some try to set the Max Degree of Parallelism from default 0 to 4 or 8. This change had the desired effect. In the graf below we can see the effect very clearly on the black line. We see the batch starts, then a change to 8 for the Max Degree of Parallelism. Then I change it back, and then to 4 and then to 8.

Play around with new windowing functions

Im not an developer but off courseI like to try new features and get knowledge in the transact language anyway. I have been interested in stocks for many years and was playing around with Excel to do some simple classic stock analysis. Then it come to my mind if these calculations is possible direct in SQL.

Here was a must try scenario to learn some more transact. In my example I have a simple table with date, high, low, close columns. The data is from OMX30 index beginning from year 2005 to now.

In the first case I would like to calculate Moving average for a period of 200 days. One of the most simple analysis in stock analysis are when the price pass over 200 days moving average there is a buy situation. When going down under this value you shall sell. If its tru or not I don’t know. But it’s a good indication if the market is positive or negative.

How can we use the new windowing function in transact then? Here is an example:

SELECT id, stock, date, close

,AVG(close) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) as  '200 SMA'

Really simple. Actually I don’t even know how to do when you don´t have this grate functions. Here is the result.

Stock         Date         Close        200 SMA

omx30     2013-01-28 1160.25 1057.748500

I will investigate those function more and try use them for more advanced calculations. That would be another post.