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
No comments:
Post a Comment