Title: MSSQL relocating a MASTER database to another server through the Netvault MSSQL plugin
Date: 10/02/2004
NV Version: 7.4.x
OS Version: Windows
Application version:MSSQL 7/MSSQL 2000
Plugin version:4.x
Description: Restoring a MASTER Database to another SQL server
PRE-REQUISITES:
The source and target sql servers must have both the binaries and the data installed in identical paths
i.e. if the source sqlserver is installed in :
E:\Program Files\Microsoft SQL Server\MSSQL\Data\
E:\Program Files\Microsoft SQL Server\MSSQL
then target server must also be installed in
E:\Program Files\Microsoft SQL Server\MSSQL\Data\
E:\Program Files\Microsoft SQL Server\MSSQL\
Otherwise, you cannot relocate the MASTER database to the target machine as the SQL server will fail on startup.
Method:
[1] Establish the full path of the target MSsqlserver's data file location.
i.e. E:\program files\microsoft SQL server\MSSQL\Data
[2] From the Restore Selections window, drill down to the desired MS-SQL APM backup saveset.
Open the the saveset (by double-clicking on it) to reveal the database items contained within.
[3] Select the MASTER database by clicking the box to the left of it (selected items will contain a green check).
[4] Once selected, right-click on the item and select Rename from the pop-up menu.
[5] The Restore Rename dialog box will appear.
Click the checkbox to the left of the Rename to field in order to activate it.
Input the desired new name for the selected item (i.e. ALPHA ).
[6] Click the checkbox to the left of the Relocate to field in order to activate it.
Type in the path to the directory where the item is to be restored. This is the full path of the target MSSqlserver's data file location.
(i.e. E:\Program Files\Microsoft SQL Server\MSSQL\Data\)
[7] Click on OK to commit the change (or Cancel to abort) and return to the Restore window.
The rename will be displayed with the selected data .
[8] Select the restore options tab & enter the target instance name into the target intance field
[9] Click the Target Client tab and select the desired Client Server from the list displayed. (i.e.SITH)
[10] Then submit the job.
IMPORTANT NOTE: Even if only renaming an item that is to be restored to it’s same original location,it is necessary to input the correct path in the Relocate to field. Otherwise, the restore will fail.
Once the restore job has completed :
[1] shutdown the target mssqlserver
[2] rename the orginal master database files from:
master.mdf
mastlog.ldf
to:
OLD.master.mdf
OLD.mastlog.ldf
[3] rename the restored master database files from:
ALPHA_master.mdf
ALPHA_mastlog.ldf
to:
master.mdf
mastlog.ldf
[4] restart the target mssqlserver
If the server fails to startup check the sql ERRORLOG file
If you see entries with the following contained then the source & target installation paths were not the same & you will have to regress to the original (OLD.master.mdf, OLD.mastlog.ldf) MASTER databases files
ERRORLOG extracts:
2004-02-10 11:01:28.27 spid9 FCB::Open failed: Could not open device e:\program files\microsoft sql server\mssql\data\MSSQL\data\pubs.mdf for virtual device number (VDN) 1.
2004-02-10 11:01:28.29 spid9 Device activation error. The physical file name 'e:\program files\microsoft sql server\mssql\data\MSSQL\data\pubs.mdf' may be incorrect.
2004-02-10 11:01:28.30 spid8 Device activation error. The physical file name 'e:\program files\microsoft sql server\mssql\data\MSSQL\data\msdbdata.mdf' may be incorrect.
2004-02-10 11:01:28.30 spid5 Device activation error. The physical file name 'e:\program files\microsoft sql server\mssql\data\MSSQL\data\model.mdf' may be incorrect.
2004-02-10 11:01:28.30 spid10 Starting up database 'Northwind'.
NB - the source MASTER database contains definitions for all the databases so if you have relocated any non-master databases into new locations or there any non-source data bases within this instance then you are going to have to:
[a] relocate the non-master source data-bases back to their default original path as they were on the source server
[b] re-attach any of the target databases that are not listed within the source MASTER database
