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?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.