Restore Master DB to New Server

Hello,
I am trying to restore my databases to a new server for a BCP Drill, and I am running into a few issues.
When I try to restore the master (in single user mode -c -m), that appears to go well, however, when I try to start SQL Server back up, it gives numerous error messages, stating the \\..\dbname.mdb and .log are not there, when infact, they are not there.
However, I am trying to restore. From what I can tell, restore the master, then the user DB's...another possible kink is that the .mdb's and the .log's are on different drives (F & G)...
Ideas?
Cory
The first thing you need to consider is whether the system databases are on the same path. If they are, then
SQL Server should start. If not, then SQL Server will not even start as it looks for model (which isn't in the
mast as stated in master..sysdatabases) and since model isn't there, it can't create tempdb and SQL Server
cannot live without tempdb.
If the system databases are on the same path, all you need to do for your user database is to drop them (they
are suspect anyhow) and then try to attach to the database files using sp_attach_db.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Cory" <Cory.ellingson@.rcis.com> wrote in message news:0BD2AFC2-6EF6-4C7D-BC29-D99D37BE9FFE@.microsoft.com...
> Hello,
> I am trying to restore my databases to a new server for a BCP Drill, and I am running into a few issues.
> When I try to restore the master (in single user mode -c -m), that appears to go well, however, when I try
to start SQL Server back up, it gives numerous error messages, stating the \\..\dbname.mdb and .log are not
there, when infact, they are not there. However, I am trying to restore. From what I can tell, restore the
master, then the user DB's...another possible kink is that the .mdb's and the .log's are on different drives
(F & G)...
> Ideas?
> Cory
|||Also, check put below KB articles:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:e3qbJ6eEEHA.2460@.TK2MSFTNGP10.phx.gbl...
> The first thing you need to consider is whether the system databases are on the same path. If they are, then
> SQL Server should start. If not, then SQL Server will not even start as it looks for model (which isn't in
the
> mast as stated in master..sysdatabases) and since model isn't there, it can't create tempdb and SQL Server
> cannot live without tempdb.
> If the system databases are on the same path, all you need to do for your user database is to drop them
(they
> are suspect anyhow) and then try to attach to the database files using sp_attach_db.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Cory" <Cory.ellingson@.rcis.com> wrote in message news:0BD2AFC2-6EF6-4C7D-BC29-D99D37BE9FFE@.microsoft.com...
> to start SQL Server back up, it gives numerous error messages, stating the \\..\dbname.mdb and .log are not
> there, when infact, they are not there. However, I am trying to restore. From what I can tell, restore the
> master, then the user DB's...another possible kink is that the .mdb's and the .log's are on different drives
> (F & G)...
>
|||Okay, I looked at where the system databases are, and they are on the same path. I restore the Master via command line utlity, and once the master is restored, it kills that connection. I attempt to reconnect to restore the other DB's, and it gives an
error for each and every user DB stating:
Timestamp Spid X Starting up database 'db'.
Timestamp Spid X udopen: Operating System Error 2(The system can not find the file specified.) during the creation/opening of physical device f:\microsoft sql server\mssql\data\db.mdf.
Timestamp Spid X FCB::Open failed: Could not open device f:\microsoft sql server\mssql\data\db.mdf for virtual device number (VDN) 1.
Timestamp Spid X Device Activation error. The physical file name 'F:\microsoft sql server\mssql\data\db.mdf' may be incorrect.
I have looked through the other reply you sent, and that seems to appear that if you have the DB to reattach, all I have are the backup files.
My thinking is correct, right? Restore Master first...then msdb, and then user DB's...?
-- Tibor Karaszi wrote: --
The first thing you need to consider is whether the system databases are on the same path. If they are, then
SQL Server should start. If not, then SQL Server will not even start as it looks for model (which isn't in the
mast as stated in master..sysdatabases) and since model isn't there, it can't create tempdb and SQL Server
cannot live without tempdb.
If the system databases are on the same path, all you need to do for your user database is to drop them (they
are suspect anyhow) and then try to attach to the database files using sp_attach_db.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Cory" <Cory.ellingson@.rcis.com> wrote in message news:0BD2AFC2-6EF6-4C7D-BC29-D99D37BE9FFE@.microsoft.com...
> Hello,
to start SQL Server back up, it gives numerous error messages, stating the \\..\dbname.mdb and .log are not
there, when infact, they are not there. However, I am trying to restore. From what I can tell, restore the
master, then the user DB's...another possible kink is that the .mdb's and the .log's are on different drives
(F & G)...
|||Yes, the user database will be suspect. Just drop them and then do the restore (or attach).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Cory" <anonymous@.discussions.microsoft.com> wrote in message
news:DF1DC370-0156-4EE8-B80C-8FA56EE08B60@.microsoft.com...
> Okay, I looked at where the system databases are, and they are on the same path. I restore the Master via
command line utlity, and once the master is restored, it kills that connection. I attempt to reconnect to
restore the other DB's, and it gives an error for each and every user DB stating:
> Timestamp Spid X Starting up database 'db'.
> Timestamp Spid X udopen: Operating System Error 2(The system can not find the file specified.) during the
creation/opening of physical device f:\microsoft sql server\mssql\data\db.mdf.
> Timestamp Spid X FCB::Open failed: Could not open device f:\microsoft sql server\mssql\data\db.mdf for
virtual device number (VDN) 1.
> Timestamp Spid X Device Activation error. The physical file name 'F:\microsoft sql
server\mssql\data\db.mdf' may be incorrect.
> I have looked through the other reply you sent, and that seems to appear that if you have the DB to
reattach, all I have are the backup files.
> My thinking is correct, right? Restore Master first...then msdb, and then user DB's...?
> -- Tibor Karaszi wrote: --
> The first thing you need to consider is whether the system databases are on the same path. If they are,
then
> SQL Server should start. If not, then SQL Server will not even start as it looks for model (which isn't
in the
> mast as stated in master..sysdatabases) and since model isn't there, it can't create tempdb and SQL
Server
> cannot live without tempdb.
> If the system databases are on the same path, all you need to do for your user database is to drop them
(they
> are suspect anyhow) and then try to attach to the database files using sp_attach_db.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Cory" <Cory.ellingson@.rcis.com> wrote in message
news:0BD2AFC2-6EF6-4C7D-BC29-D99D37BE9FFE@.microsoft.com...
issues.
I try
> to start SQL Server back up, it gives numerous error messages, stating the \\..\dbname.mdb and .log are
not
> there, when infact, they are not there. However, I am trying to restore. From what I can tell,
restore the
> master, then the user DB's...another possible kink is that the .mdb's and the .log's are on different
drives
> (F & G)...
>
>
|||The issue that I am having is that with these error messages, the SQL Server will not even start for me to drop or restore the db's.
-- Tibor Karaszi wrote: --
Yes, the user database will be suspect. Just drop them and then do the restore (or attach).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Cory" <anonymous@.discussions.microsoft.com> wrote in message
news:DF1DC370-0156-4EE8-B80C-8FA56EE08B60@.microsoft.com...
> Okay, I looked at where the system databases are, and they are on the same path. I restore the Master via
command line utlity, and once the master is restored, it kills that connection. I attempt to reconnect to
restore the other DB's, and it gives an error for each and every user DB stating:
> Timestamp Spid X udopen: Operating System Error 2(The system can not find the file specified.) during the
creation/opening of physical device f:\microsoft sql server\mssql\data\db.mdf.
> Timestamp Spid X FCB::Open failed: Could not open device f:\microsoft sql server\mssql\data\db.mdf for
virtual device number (VDN) 1.
> Timestamp Spid X Device Activation error. The physical file name 'F:\microsoft sql
server\mssql\data\db.mdf' may be incorrect.
reattach, all I have are the backup files.
then
> SQL Server should start. If not, then SQL Server will not even start as it looks for model (which isn't
in the
> mast as stated in master..sysdatabases) and since model isn't there, it can't create tempdb and SQL
Server
> cannot live without tempdb.
(they
> are suspect anyhow) and then try to attach to the database files using sp_attach_db.
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
news:0BD2AFC2-6EF6-4C7D-BC29-D99D37BE9FFE@.microsoft.com...
issues.
I try
> to start SQL Server back up, it gives numerous error messages, stating the \\..\dbname.mdb and .log are
not
> there, when infact, they are not there. However, I am trying to restore. From what I can tell,
restore the
> master, then the user DB's...another possible kink is that the .mdb's and the .log's are on different
drives
> (F & G)...
|||SQL Server will not start if any of the system databases cannot be started (except for msdb). You need to walk
the errorlog file to fond out which system database is the problem. And also check the links I sent earlier.
then should have all info you need.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Cory" <anonymous@.discussions.microsoft.com> wrote in message
news:A7F41CF4-A4A6-4EAB-887F-E996C6207D2E@.microsoft.com...
> The issue that I am having is that with these error messages, the SQL Server will not even start for me to
drop or restore the db's.
> -- Tibor Karaszi wrote: --
> Yes, the user database will be suspect. Just drop them and then do the restore (or attach).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Cory" <anonymous@.discussions.microsoft.com> wrote in message
> news:DF1DC370-0156-4EE8-B80C-8FA56EE08B60@.microsoft.com...
Master via
> command line utlity, and once the master is restored, it kills that connection. I attempt to reconnect
to
> restore the other DB's, and it gives an error for each and every user DB stating:
during the
> creation/opening of physical device f:\microsoft sql server\mssql\data\db.mdf.
for
> virtual device number (VDN) 1.
> server\mssql\data\db.mdf' may be incorrect.
> reattach, all I have are the backup files.
they are,
> then
(which isn't
> in the
SQL
> Server
drop them
> (they
> news:0BD2AFC2-6EF6-4C7D-BC29-D99D37BE9FFE@.microsoft.com...
> issues.
when
> I try
..log are
> not
> restore the
different
> drives