SQL 7.0 Database File Transfer Problem

Transfering Files between two versions of 7.0 with database files only

[esaslo]

I recently had a server crash--the kernel corrupted. I was able to recover all of the files on the harddrive in their actual states.

I installed SQL 7.0 on another machine, and NEED to get the databases from the previous machine loaded into the new one. I have the entire directory file from the old machine--- C:/MSSQL7/*.* --- including the data file, which seems to contain the databases I'm missing.

I've tried just coping over the data files, replacing the data files, replacing the entire directory under MSSQL7, etc.

Does anyone know how I can access those databases from the previous machine having the actual files from it?

Thanks in advance for whatever help can be provided.

ES

[aspdaddy]

Hi, If you dont have a SQL backup you need the data file (.mdf file) and the transaction log (.ldf file).

Its been a long while since I had to do this , but I'm pretty sure that you need to create a new database in Enterprise Manager first and then stop the SQL service. Then go into the folder where the new Data File and Transaction Logs have been created, delete them and copy in the original ones from your other machine, the names but match exactly e.g MyDatabase_log.ldf and myDatabase_Data.mdf.

[esaslo]

I get an error 22274 -- this database has been marked inaccessible, when I switch the files around as you instructed.

Please advise.

Thanks.

[aspdaddy]

Have the files become read only in the process? If they have right click and uncheck

[TheNige]

Can you try using the Attach option in enterprise manager? Right-click on the database folder-->all tasks-->attach database and then you can select your mdf file.

Don't create any new databases.

[aspdaddy]

That does sound a better option actually, never tried it myself though.

I just tried the method I described above, just to convince myself, and it does work fine. As long as you have an un-corrupted mdf, you should be fine.

[esaslo]

Hi guys.

Thanks for the additional option.

I'm going to reinstall sql on a new machine and try them both today.

I'll let you know. I'm so much better with other things.

ES

[esaslo]

I do not seem to have an attach database command under the database >> all tasks. All that up is import/export, backup & restore.

Additionally, when I try to rename, as suggested, I then show (no items) under the database folder menu.

Ideas?

Thank You.

[syber]

Try using the stored procedure sp_attach_db

EXEC sp_attach_db @dbname = 'test',
@filename1 = 'F:\mssql7\data\test_data.mdf',
@filename2 = 'F:\mssql7\data\test_log.ldf'

[esaslo]

Hi All.

I ran the stored procedure as suggested. The response was that the database as successfully attached.

However, when I go into enterprise manager to try and view the database, I doesn't show the database, and still shows not items.

I feel like I'm really close on getting this. Thanks for your help everyone.

Let me know if you have any ideas on how to correct this last bump.

ES

[esaslo]

Hi!

I got it! Thanks for all your help everyone.

I just had to reset the ODBC on the machines to find the databases, and not replace the system databases.

Thanks again everyone who contributed.