Thursday, February 20, 2014

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

Friday, February 7, 2014

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