How to Backup/Restore MS SQL Server Database Using DTS

[Shane Melson]

Backing up or restoring your MS SQL Server database is a relatively simple process. However; in order to either back up or restore your database you will first need a few programs installed on your computer.
You will need to get the Ms SQL Server Utilities which include Enterprise Manager, Client Network Utilities, Query Analyzer and, of course, the Import/Export utility which you will use to transfer your data back and forth via DTS.
All of these are available when you install Microsoft MSDE.

Please try this with the help of your senior windows admins/experts

+++++++++++++++++++++++++++++++++++++++++++++
Backing Up Your SQL Server Database
+++++++++++++++++++++++++++++++++++++++++++++

First we will explore the easiest way to do this which is by creating a brand new local database and then copying all of the objects from your existing remote database to the new local one.

1. Create a new blank database in Enterprise Manager.
2. Open your Import/Export Utility program which is the Data Transformation Services Import/Export Wizard.
3. Click Next and Select "Microsoft OLE DB Provider" for SQL Server in the Data Source drop down menu.
4. In the Server drop down menu select your remote server. If there are no servers listed - you will need to establish a connection to your databases.
=========
NOTE:: Please search the forum for How to Connect to an MS SQL Server Database using Enterprise Manager.
=========
5. Click the Radio Button that says "Use SQL Server Authentication" then enter your remote SQL Server Username and Password, select your SQL Server Database Name from the drop down menu and click next.
6. Now we enter our local database information just like we did the remote database information in steps 3-5 above. Click Next.
7. On this page we want to select the 3rd option Copy objects and data between SQL Server databases and click next.
8. On the Select Object to Copy page we will keep all of the defaults and simply click next to get to the next page.
9. Select the check box Run immediately and click next and then click finish.

If you followed the steps above correctly you will see the message "Successfully copied objects from Microsoft SQL Server to Microsoft SQL Server" and you will now have a copy of your remote SQL Server database on your local machine including all of your users and their permissions for each object.

=========
Here is the problem with the above routine.
First we don't want to keep creating new databases each time we back up our remote database. What we would rather do is create one similar local database and then keep updating that database when new information needs to be backed up from our remote or visa versa. This is where DTS, or the Data Transformation Services comes into play.
=========

Now let's assume we want to back up only one table from the database we just created above. This is slightly different from what we did above.

1. Start out by following the procedures 1-6 above until we get to the "Select Table Copy or Query" page.
2. Now on this page instead of selecting the 3rd radio button we are going to select the 1st one: "Copy Table(s) view(s) from the source database and click next.
3. This bring up a grid with three columns being Source, Destination and Transform. We will select only on of the table for this example to demonstrate but when you make a backup of your database you will want to back up all the tables and follow the same steps outlined here.
4. Select a table from the destination column, preferably one that has a unique ID field. You will see information appear in the destination column when you do.
5. Use the drop down menu from the destination column and select the table you wish to copy this information to. Usually this is the same table. Watch out for owner names because sometimes DTS wants to give the destination table a new owner ame rather than the one that it there such as DBO.
6. Once you have your destination table selected properly, go to the transform column and click on the grey box with the 3 dots...
7. On the new window that pops up there are 2 columns of radio buttons. In the left column we want to select either "Delete Rows in destination table" or "Append rows in destination table". Select Delete Rows so our tables will match. If you select Append by accident you will get an error when you try to run the project.
8. On the right column of radio buttons, and if we selected a table with a unique id field, we want to select "Enable identity insert". This will allow DTS to insert identity columns into our existing table without causing an error.
9. Click OK to close the popup window and then click next.
10. Select Run immediately, click next and then Finish.

If you followed the steps above you should see your DTS Package run successfully and get the message "Successfully copied X Table(s) from Microsoft SQL Server to Microsoft SQL Server".

There you have it.
Restore Your SQL Server Database


If you want to restore your remote database simply reverse your source and destination SQL Servers in the steps above.

[sam]

Thanks shane for this posting it is very helpful.

[BodShane]

Here, you need to make sure you get MS SQL Server utilities that includes Enterprise Manager, Client Network Utilities, Query Analyzer and Import as well as Export utility which transfers data through DTS.

[rt.garg1991]

Thanks Shane.this is really helpful.