Backup you MS SQL database to another server

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

 

From your target computer.(the computer where you want the backups)

You will want to create a blank database on the local sql server as your target.

Open up Enterprise Manager and connect to your sql database.

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.

Now select the target database on your local server.

Select Next

Leave the Copy Table and Views checked and press next

Select (check) all the tables and views you want to backup Press Next

 

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.  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:

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!!  I usually delete or rename the table where I am going to restore the data.

 

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

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

Select the correct database from your local server, press next.

Since you started this import data from the correct database the next screen Choose a destination should be properly populated, press next.

Leave the copy Tables and views checked and press next

Select the tables you want to restore

Check Run immediately and select next and finish

 

Author: eweb-admin



Print