1

I've mistakenly deleted a table in Microsoft Server Management Studio, and the restore function was not properly configured.

I have older .mdf and .ldf files of the particular db with the deleted table in it. How would I properly restore the older version with these files? Can I "Detach" and the re-attach the older version that exist in another folder? How does this functionality work? What will happen to the current .mdf/.ldf files and will the old ones I'm attaching be moved to the appropriate folder?

1 Answer 1

4

I would attach the files to a new database, script the table and re-create it in your active database.

First up, move the *.mdf and *.ldf files to the default database file locations. This way, they'll inherit the appropriate file level permissions.

Now, create a new database from the existing files, but using a new name:

USE master
GO

CREATE DATABASE mydb_restored ON 
( FILENAME = N'C:\Program Files\MSSQL\Data\mydb.mdf' ),
( FILENAME = N'C:\Program Files\MSSQL\Log\mydb_log.ldf' )
FOR ATTACH
GO

Now, you should be able to browse the old database, find the table in question. Right-click it, select "Script Table as", and open a "CREATE TO" script in the query editor.

Edit the script to use the original database instead of the restored one, and you should be able to re-create the table.

You can also generate a script that includes the data from the restored table

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .