Here is what I have just RECENTLY figured out...

Here is my situation...

Server A has backups to tape. Server A dies. Server B is created with
little care to mimic Server A specs. IE Drives are misnamed...

A: B:
C-os C-os
D-Apps D-CDRom
E-Data E-Apps
F-N/A F-Data
G-N/A G-Backups

When restoring the Master, the with move is needed, but then it shuts down.
You start the server backup, it cannot find the model, msdb, or temp, as the
master thinks it resides on the D. Also, I needed to change the server
name...not a normal restore procedure, but is listed as well. Here is what
I had to do...

-- Undocumented Trace flags
-- T3607 Recovers no database. Skips automatic recovery (at startup) for all
databases.
-- T3608 Recovers master database only. Skips automatic recovery (at
startup) for all databases except the master database.
-- T3609 Skips the creation of the tempdb database at startup. Use this
trace flag if the tempdb database is problematic or problems exist in the
model database.
-- T4022 Bypasses automatically started procedures. (-f will do the same
thing...)


-- Start SQL Server with 'sqlservr -c -m'

restore database master
from disk = 'g:\master.bak'
with Move 'Master' to 'f:\mssql\mssql\data\master.mdf',
Move 'Mastlog' to 'f:\mssql\mssql\data\mastlog.ldf'
go

-- Restart SQL Server with 'sqlservr -c -f -T3608'

sp_detach_db 'model'
go

sp_attach_db
'model','f:\mssql\mssql\data\model.mdf','f:\mssql\mssql\data\modellog.ldf'
go

sp_detach_db 'msdb'
go

sp_attach_db
'msdb','f:\mssql\mssql\data\msdbdata.mdf','f:\mssql\mssql\data\msdblog.ldf'
go

alter database tempdb modify file (name = tempdev, filename =
'f:\mssql\mssql\data\tempdb.mdf')
go

alter database tempdb modify file (name = templog, filename =
'f:\mssql\mssql\data\templog.ldf')
go

-- Restart SQL Server with 'sqlservr -c'

restore database model
from disk = 'g:\model.BAK'
with move 'modeldev' to 'f:\mssql\mssql\data\model.mdf',
move 'modellog' to 'f:\mssql\mssql\data\modellog.ldf',
Replace
go

--restore filelistonly from disk = 'f:\msdb.bak'

restore database msdb
from disk = 'g:\msdb.BAK'
with move 'msdbdata' to 'f:\mssql\mssql\data\msdbdata.mdf',
move 'msdblog' to 'f:\mssql\mssql\data\msdblog.ldf',
Replace
go



-- Rename SQL Server.

-- IMPORTANT: This procedure is required only if you have to change the name
of the SQL Server installation to which you have just restored the master
database.

-- For example, you would rename SQL Server if:

-- * You restored the database backups from a computer that is named
SOURCESVR to a computer that is named TARGETSVR.
-- * The computer that is named TARGETSVR has a SQL server that is named
SOURCESVR.
-- If you need to rename SQL Server, use the follow syntax to synchronize
the computer name and the SQL Server name:

exec sp_dropserver 'SOURCESVR'
go
exec sp_addserver 'TARGETSVR', 'LOCAL'
go

Cory Ellingson

Production Services

-----Original Message-----
From: Jon [mailto:mssqldba-ezmlmshield-x78453010.[Email address protected]
Sent: Tuesday, April 26, 2005 2:10 PM
To: LazyDBA Discussion
Subject: How do you restore to a new server with different drives?

I'm trying this for the first time with some issues.

What would be the restore syntax for a restore with a backup to a new
server with different drivesets?

Jon Brewer
SunGard Availability Services
MidRange DBA Group
(856) 866-4018


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]

MS Sql Server LazyDBA home page