Restore master backup from different server causes file location problems

Hi,
I have just restored a backup of our production master database to a
development PC as a test and now SQL Server won't start. This is
because it cannot open the devices for the various databases. Fair
enough, I understand why that is - we have system dbs (model, msdb) on
D: drive in production while they are on C: drive on dev PC.
My questions are: -
1. Is it possible to repair this by somehow updating the server's links
to where the database files are? I know you can specify master db/log
files' location on command line, but not the other system DBs.
2. Is it only the device activation error for the system databases that
prevent SQL Server restarting or do all user database files need to
also be in their correct location?
I can't get SQL up and running because there's no D: drive on the dev
PC, so I can't move the files into the correct location for SQL Server
to find them on startup...
Rgds,
Andrew DuncanSQL Server will start once the system databases are set
correctly. The following articles have information on
changing the file locations for system databases:
Moving SQL Server databases to a new location with
Detach/Attach
http://support.microsoft.com/?id=224071
INF: Moving SQL Server 7.0 Databases to a New Server with
BACKUP and RESTORE
http://support.microsoft.com/?id=304692
-Sue
On 16 Mar 2005 00:10:03 -0800, andrew@.duncanhcmc.com wrote:
>Hi,
>I have just restored a backup of our production master database to a
>development PC as a test and now SQL Server won't start. This is
>because it cannot open the devices for the various databases. Fair
>enough, I understand why that is - we have system dbs (model, msdb) on
>D: drive in production while they are on C: drive on dev PC.
>My questions are: -
>1. Is it possible to repair this by somehow updating the server's links
>to where the database files are? I know you can specify master db/log
>files' location on command line, but not the other system DBs.
>2. Is it only the device activation error for the system databases that
>prevent SQL Server restarting or do all user database files need to
>also be in their correct location?
>I can't get SQL up and running because there's no D: drive on the dev
>PC, so I can't move the files into the correct location for SQL Server
>to find them on startup...
>Rgds,
>Andrew Duncan|||Thanks Sue,
I actually had this article but for some reason assumed I couldn't move
the files unless they were already in a valid location!
One point not mentioned in the article was I had to start sqlservr with
-f flag due to tempdb files not being found. Only then could I perform
the ALTER DATABASE commands to move tempdb.
Thanks again for response!|||The -f flag is mentioned several times in the second
article. It's a more inclusive article but there are a few
differences with SQL 7 and 2000 and the first article is
what MS has for SQL Server 2000. I'd prefer that they just
update the second article on using backup and restore with
SQL Server 2000.
-Sue
On 16 Mar 2005 18:17:38 -0800, andrew@.duncanhcmc.com wrote:
>Thanks Sue,
>I actually had this article but for some reason assumed I couldn't move
>the files unless they were already in a valid location!
>One point not mentioned in the article was I had to start sqlservr with
>-f flag due to tempdb files not being found. Only then could I perform
>the ALTER DATABASE commands to move tempdb.
>Thanks again for response!