Hi,
We are trying to attach a Database from SQL7 to SQL2K SP3.
On SQL7
The Database was detached and the MDF copied to the New Server.
On SQL2K
We used the attach Db (in Enterprise Manager).
We get a red 'x' as normal against the logfile and noramlly it would create a new one when u proceed, but this time we are getting an error after the dialogue box asking to create a new one:
Error 1813: Could not open new database 'dbname'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'd:\mssql7\data\dbname.ldf' maybe incorrect.
The path is where the logfile resided on the old server.
Regards
John.
Hi,
It seems your old database have mutiple LDF files. If you have mutilple LDF files missed out you will not be able to use the procedure sp_attach_single_file_db (or through Enterprise Manager) to attach the MDF file alone.
A solution for this is:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete
the LDF files.
3. STart SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use LOG files
update sysdatabases set status=32768 where name ='dbname'
6. Restart sql server. now the database will be in emergency mode
7. Now execute the undocumented DBCC to create a log file
DBCC REBUILDLOG(dbname,'c:\dbname.ldf')
8. Execute sp_resetstatus <dbname>
9. Restart SQL server and see the database is online.
|||
This usually happens when SQL Server is unable to create the log file with the same full name. Is 'd:\mssql7\data\dbname.ldf' a valid path in the new machine? I recommend always copying the log file with the database, otherwise the database may not be able to recover. You can use the following SP to attach the database in the new location:
sp_attach_db 'dbname', 'new_path_to_mdf', 'new_path_to_ldf'
Thanks,
Fabricio.
