move database from 1 instance to another

Hi,
One of my colleague has a SQL server 2k5 on C:\Program files\...
i have basically installed all instances and program files of SQL server
2k5 on D: drive.
I've recreate his instance on D: drive and he gave me all files MDF,
LDF... of his instance (quick and dirty backup :-( )
when i stop the instance process and copy all his files on my D: drive
into the right instance folder, my instance process does not restart and
i got the following error into log events :
The description for Event ID ( 17204 ) in Source ( MSSQL$MAGICEYE )
cannot be found. The local computer may not have the necessary registry
information or message DLL files to display messages from a remote
computer. You may be able to use the /AUXSOURCE= flag to retrieve this
description; see Help and Support for details. The following information
is part of the event: FCB::Open failed, C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf, 1, 3(The system
cannot find the path specified.).
so this is clear that the instance path is stored into database files.
how can i do to make this instance files (dirty back) work on my D:
drive instance ?
thanks a lot,
A.Did you restore his master, model, msdb perhaps? If so, do not do this.
Just restore his user databses. There will be issues with logins, however.
You will need to recreate them and sync the login/database user information
if you care about that.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Alain R." <noemail@.nospam.com> wrote in message
news:%23RlvthdNIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Hi,
> One of my colleague has a SQL server 2k5 on C:\Program files\...
> i have basically installed all instances and program files of SQL server
> 2k5 on D: drive.
> I've recreate his instance on D: drive and he gave me all files MDF,
> LDF... of his instance (quick and dirty backup :-( )
> when i stop the instance process and copy all his files on my D: drive
> into the right instance folder, my instance process does not restart and i
> got the following error into log events :
> The description for Event ID ( 17204 ) in Source ( MSSQL$MAGICEYE ) cannot
> be found. The local computer may not have the necessary registry
> information or message DLL files to display messages from a remote
> computer. You may be able to use the /AUXSOURCE= flag to retrieve this
> description; see Help and Support for details. The following information
> is part of the event: FCB::Open failed, C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf, 1, 3(The system cannot
> find the path specified.).
> so this is clear that the instance path is stored into database files.
> how can i do to make this instance files (dirty back) work on my D: drive
> instance ?
> thanks a lot,
> A.|||I already only restored his DB file, but after my instance service was
not able to restart :-(
so any other suggestion ?
TheSQLGuru wrote:
> Did you restore his master, model, msdb perhaps? If so, do not do this.
> Just restore his user databses. There will be issues with logins, however.
> You will need to recreate them and sync the login/database user information
> if you care about that.
>|||The files for master db is pointed to in the registry. Resource db need to be in the same folder.
All the other databases are pointed to from master. Problem is that if you fix registry path for
master and make sure resource db is in same folder, then SQL Server will get a bit further, but when
it tires to open model, it won't find the files and won't start (can't live without tempdb and can't
create tempdb without model). There's a section in BOL 2005 on how to move system databases and also
KB 224071 can be useful.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Alain R." <noemail@.nospam.com> wrote in message news:uGUuU%23dNIHA.1188@.TK2MSFTNGP04.phx.gbl...
>I already only restored his DB file, but after my instance service was not able to restart :-(
> so any other suggestion ?
> TheSQLGuru wrote:
>> Did you restore his master, model, msdb perhaps? If so, do not do this. Just restore his user
>> databses. There will be issues with logins, however. You will need to recreate them and sync the
>> login/database user information if you care about that.