Restore a table in MS SQL

In this entry I will show you how to schedule a backup of your ms sql server database tables to flat files on the same server.

In my case I like to have these back-ups because it will be much easier to recover from disaster on the production server and because it is much easier to fix small dumb mistakes I have done on the production server like and it gives me table by table flexibility.

Recover from bad update command

Recover from bad insert command

Recover from dumb sql commands

Backing-up individual tables to the target computer using Flat Files.

From your source computer.

First you must connect to your sql database from Enterprise Manager.

After connected to your database right click on the database or tables icon go to ‘All Tasks’ and select export data. 

In the Data Transformation Services Import/Export Wizard select next.

Since you were already connected to the database you want to export from the first screen ‘Choose a Data Source’ should be properly populated click next.

In the Destination drop-down select text file (it will be at the bottom)

Now select a target location and file name I suggest you use the name of the table you are going to put in that file.  Include the .txt which
is necessary in this case.

Select Next

Leave the Copy Table and Views checked and press next

At the top select the source table you want to backup.

I leave all the other settings as default. Press Next

Backup ms SQL text file

Now check the Schedule DTS package for later execution.  You can leave the Run immediately checked if you like.

Select the button to the right with the three dots as in this image

 Recover from sql upload

Select the schedule for which you want your backup to run.

Make sure the Save DTS Package is checked.

I always save it as SQL Server Meta Data Services

Press Next

Now enter the details of how to save this backup package.  Probably should give it a name like tblCatToProdDailyFlatFile.  You want to save this on the database at the source which will be the default settings.

If you want to delete the backup package or make changes you can find it in Enterprise Manager under Data Transformation – Local Packages

 

 

To Restore a single table:

Make a copy of the table you want to restore or just delete all the data in that table.  Be very careful you need to know what you are doing here!!  You can use this sql command and run it from  query analyzer to empty out the table “Delete From [yourtable]”.  No matter how you do it you will need the correct table structure to import the data.

If you have some auto increment fields you will need to remove the auto increment so the data can be imported.  After importing turn the auto increment back on.

Right click the tables in the database you want to restore and select All Tasks – Import Data

In the Data Transformation Services Import/Export Wizard select next.

On the Choose a data Source select text file from the Data Source Drop down

Locate the text file you want to restore press ok and then press Next

Press next with the default command for file format (assuming you used the default when you created the back-up)

Press next and next again at the Specify Column Delimiter

Select the correct database at the Choose a destination screen.

Make sure that it is going to go to the correct table (which will be easier if the file name has the same name as the table)  Click the transform button a screen similar to below will pop-up.

Click in the destination column on the first row and select the target field. (this should be the first field for the first row and the second field for the second row.) Press OK

Press Next

Restore a backup of a table

Check Run immediately and select next and finish

Make sure to recreate any Auto increment fields you have in the table definition.

Author: eweb-admin



Print