restore system databases to server with different/alternate data p

I finally figured out how to do this when the source server has its master,
model, msdb, and tempdb databases in a different location than the target
server.
You may have to interpret some of the file names and locations here:
If you are going to restore system databases from a source who’s data
directory path is different from this computer, use the following procedure:
1. WARNING you must be able to detach and reattach the model and msdb
databases, therefore, you should:
o Open enterprise manager
o Restore the model database from the source server
o Restore the msdb database from the source server
2. Stop the MSSQL service.
3. Go to Start menu and open a command prompt.
4. Change to directory C:\Program files\ Microsoft SQL Server\MSSQL\binn
5. Start sql server with: sqlservr –m –c -f (this brings the system up i
n
Single User Mode; if you add -T3608 (don’t use a lowercase t) so SQL Serve
r
does not recover any database except the master database, the restore
database master command will complain about not having a tempdb).
6. Go to Start menu and open a second command prompt.
7. Change to directory where the backup files are (e.g. E:\ MSSQL\BACKUP).
8. Type (case sensitive): osql –S serverName -E
o This logs in to the specified server and logs in with the Windows user
currently logged in.
o You should see a command prompt that looks like: >1
9. Type:
restore database master
from disk='e:\mssql\backup\from_prod\master_b
k.bak'
with move 'master' to 'e:\mssql\data\master.mdf', move 'mastlog' to
'e:\mssql\data\mastlog.ldf'
(the ‘with move’ part is necessary because the location of the files is
changing)
go
o When that completes, both the osql session and the sqlservr session will
be shut down. You should see:
The master database has been successfully restored. Shutting down SQL Server
SQL Server is terminating this process.
10. to move the model database, start sql server with sqlservr -m -c -f -T36
08
11. in the other window, run osql –S serverName –E again and type
use master
go
sp_detach_db 'model'
go
sp_attach_single_file_db 'model','e:\mssql\data\model.mdf'
go
sp_detach_db 'msdb'
go
exit
12. Stop the server with Ctrl+C
13. Restart the server with sqlservr -m -c –f
14. in the other window, run osql –S serverName –E again and type
use master
go
sp_attach_single_file_db 'msdb','e:\mssql\data\msdbdata.mdf'
go
Alter database tempdb modify file (name = tempdev, filename =
'E:\MSSQL\Datatempdb.mdf')
go
{you will see: File 'tempdev' modified in sysaltfiles. Delete old file
after
restarting SQLServer.}
Alter database tempdb modify file (name = templog, filename =
'E:\MSSQL\Datatemplog.ldf')
go
{you will see: File 'templog' modified in sysaltfiles. Delete old file
after
restarting SQL Server.}
exit
o you will not actually have to delete any files because they are already in
the correct place. The master database just had to be modified so that it
would know that.
15. Stop the server with Ctrl+C
o You should now be able to restart the sql cluster resources (or the sql
server service if not using a cluster)
16. start the normal sql service and test by opening Enterprise Manager and
connecting to the server. If it hangs up, you might just have to restart the
service again.
o Note: On a fresh restore the user databases will be listed as Suspect.
You must now restore each of the user databases to the correct file location
.
This can easily be done with Enterprise Manager (assuming it doesn’t hang
up
or take forever for the restore database dialog box to come up, which usuall
y
is the case, actually).I forgot to mention that if the server name is different, you are going to
have problems with your scheduled jobs. Run this script to fix:
'replace the newName with the name of the new server and the oldName with
the name of the old server.
use msdb
update sysjobs
set sysjobs.originating_server = 'newName'
where sysjobs.originating_server = 'oldName'
select * from msdb..sysjobs order by name --to verify
Oh, and by the way, your backup "devices" will still be set to the same
location and thus will need to be recreated.