Database Restore

[Dudlee]

I have a database that includes one particular table that needs to be restored. The database is fine, the table in question needs to be restored. Normally, I suppose one would restore the database from one computer to another and then copy the necessary files. I do not have another computer that is suitable for this. Is it possible to simply copy both the database and log files (*.mdf & *.ldf) to another directory on the same computer, then do a restore to the original directory? If this fails can the files simply be copied back and the database will read fine (even with the bad table)? Any assistance will be much appreciated. Thanks.

Darryl

 

[tlbroadbent]

Do you have a backup of the database that contains a good copy of the table? Or do you have a copy of the MDF and LDF files that contains a good copy of the table?

If you have a backup you can restore the database with different name. For example, to recover a table from a datbase named Accounting, you could restore it as Accounting_Restore. Use Enterprise Manager or T-SQL queries to do the restore.

If you have a backup of the MDF and LDF files, you could place them in a different directory but I recommend renaming them. Attach the files using sp_attach_db. See SQL BOL for details.

Then you would replace the bad table with the good table from the restored or attached database. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Dudlee]

Thanks Terry, much appreciate the info. I have backup copies of the .mdf and .ldf files that contain a good copy of the table. I'm new to SQL and have been thrown into the pit "so to speak" - what is SQL BOL? Do I just do a straight copy and paste of the table or is there a specific procedure I must follow. Thanks again for your help.

Darryl

 

[tlbroadbent]

SQL BOL: SQL Books Online (see faq183-689)

If the table structure is undamaged and you simply need to restore the data, you can use a simple SQL script.

Truncate table DB_name.dbo.table_name

Insert Into DB_name.dbo.table_name
Select * From DB_name_Restore.dbo.table_name

If you need to replace the table structure. You can[ol][li]use the Import/Export Wizard or DTS to copy the table.

[li]create a SQL Script from the restored database and run it on the production database. Use Enterprise Manager to generate the script. Then insert the records as noted above.[/ol] Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Dudlee]

Thanks, I'll give that a go.