How to know if an index is compressed

In management studio you can see if a table is compresed by just chose properties on it. But why is it not the same for index? Something that would be nice as I see it. Anyway, we can use some code to find it out.

SELECT * FROM sys.partitions a
INNER Join sys.indexes b ON b.object_id = a.object_id AND b.index_id = a.index_id
WHERE a.data_compression > 0

SQL serverjob “syspolicy_purge_history” fails with A drive with the name 'D' does not exist.

Going crazy on SQL serverjob “syspolicy_purge_history” I have setup a couple of brand new SQL 2012 clusters with al the laterst servicepack and so on but on all instances we receive the error message below.

Executed as user: DOMAIN\SQLSERVERACCOUNT. A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS -DisableNameChecking'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot find drive. A drive with the name 'D' does not exist. '. Process Exit Code -1. The step failed.

After a lot of trouble shooting it seem like the service account for SQL needs to have permission on the root for D:\ I don’t know why, we have SQL binaries on D:\Progrma Files\ Anyway, it´s solved by giving the SQL account read permission to the drive.

Generate a restore script for all databases

This time I will write little about database migration. Not so fancy, often just backup/restore. For the moment I participate to a large migration project and for one SQL instance the customer had nearly 700 databases used for the same system (different kind of tests).
Anyway, they wanted to move them at same time. Not the most exciting thing to do if you do it manually. I checked some existing script to generate a restore script for this but to my surprise I did not found any that worked. After a while I did create one for this purpose. First I made a stored procedure with a inputparameter for the databasename. Then I use a coursor to run in against all databases. This will make a restore script. The only thing I needed to do afterward was to change the path to backup files and datafile destinations. This is simply done with Notepad.

First the stored procedure:

CREATE PROCEDURE GenerateRestoreScript @DatabaseName VARCHAR(100)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @MoveOption AS TABLE (Id INT IDENTITY(1,1), MoveOption VARCHAR(MAX))

PRINT 'RESTORE DATABASE ' + @DatabaseName

PRINT 'FROM DISK = ''' + @DatabaseName + '.bak''' 

PRINT 'WITH'

INSERT INTO @MoveOption ([MoveOption])

SELECT

'MOVE ''' + b.name + ''' TO ''' + b.physical_name + '''' AS [MOVE OPTION]

FROM sys.databases a

INNER JOIN sys.master_files b

ON a.database_id = b.database_id

WHERE a.name = @DatabaseName

DECLARE @LastId INT = 0, @MoveOptionText VARCHAR(MAX)

WHILE EXISTS (SELECT TOP 1 1 FROM @MoveOption WHERE Id > @LastId)

BEGIN

SELECT TOP 1 @MoveOptionText = MoveOption, @LastId = Id

FROM @MoveOption

WHERE Id > @LastId

ORDER BY Id ASC

PRINT CASE WHEN @LastId = 1 THEN '' ELSE ',' END + @MoveOptionText

END

PRINT ', RECOVERY'

PRINT 'GO'

END

GO

Then the coursor to run:

DECLARE @DatabaseName VARCHAR(256)

DECLARE @sql NVARCHAR(256)

DECLARE C1 CURSOR FOR

SELECT name

FROM sys.databases

ORDER BY name

OPEN C1

FETCH NEXT FROM C1 INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

   BEGIN

SELECT @sql = 'exec dbo.GenerateRestoreScript [' + @DatabaseName + ']'

            EXEC sp_executesql @sql

            FETCH NEXT FROM C1 INTO @DatabaseName

   END

CLOSE C1

DEALLOCATE C1

This will generate a quite nice looking restore script. And it also take all the filegroups in the database.

RESTORE DATABASE Test_Db

FROM DISK = 'Test_Db.bak'

WITH

MOVE 'Test_Db' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test_Db.mdf'

,MOVE 'Test_Db_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test_Db_log.ldf'

,MOVE 'Test_Db_SecondaryFG' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\Test_Db_FG2.ndf'

,MOVE 'Test_Db_third' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test_Db_third.ndf'

,MOVE 'Test_Db_SecondaryFG2' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test_Db_SecondaryFG2.ndf'

, RECOVERY

GO

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