How to Backup your MS SQL Database Locally

Managing MS SQL database locally:

These instructions are for MS SQL 2000 using Enterprise Manager and DTS (Database Transformation Services). The procedure is nearly the same for MS SQL 2005 DBs using MS SQL Server Management Studio and SSIS (SQL Server Integration Services - formerly called DTS).

The concept is to create an identically named DB and user/login locally, which corresponds to your live/production DB online. Then depending on which server (local or remote) you are connected to, you would import or export to the other. We recommend exporting the live DB to your local DB to avoid accidentally deleting data on your live DB. Obviously, if you want to bring your local DB changes to the live DB then you will need to be extra careful. See warning below.

This article assumes you have already created a live MS SQL DB and User account using your EPhost control panel and that you have configured your local computer to access it using MS Enterprise Manager or MS SQL Management Studio.

WARNING: It is possible to delete your data accidentally. Please be careful to look at all of the options when importing or exporting. Specifically, look for the option that refers to "Dropping Destination" tables before importing data to a DB. This basically deletes the current data before importing. Sometimes, this is necessary and other times you'll want to append.

Step 1: Add a New Database Locally

1. Open MS SQL Enterprise Manager while logged in to your computer as an administrator
2. Expand the (LOCAL) server
3. Right click the Databases folder
4. Click the New Database menu option

In the Database Properties window:

1. Enter the Name of your production database (It's important that the database name and login be identical)
2. Click the OK button
3. Expand the Security folder
4. Right click the Logins icon
5. Click New Login

In the SQL Server Login Properties window:

1. Enter the Name of your production login (It's important that the database name and login be identical)
2. Select the Defaults / Database created earlier
3. Click the OK button

Step 2: Create a New Database User Locally

1. Right Click Users
2. Click the New Database User menu item

In the Database User Properties window:

1. Select the Login Name created above
2. Grant: Public, db_accessadmin, db_securityadmin, db_dlladmin, db_datareader, and db_datawriter in the Database role memberships list
3. Click the OK button

Step 3: Export DTS Database

1. Right click on your production Microsoft SQL Server
2. From the All Tasks menu, click the Export Data menu item in MS Enterprise manager.
3. In the DTS Import/Export Wizard window:
4. Click the Next button
5. In the DTS Import/Export Wizard window:
6. Select the Use SQL Server Authentication radio button
7. Enter your SQL login Username
8. Enter your SQL login Password (if you do not know your production SQL login password, it can be viewed in your web.config
9. Select your production Database
10. Click the Next button

In the DTS Import/Export Wizard window, the following options should be enabled:

• Copy destination objects
• Drop destination object first (SEE WARNING ABOVE)
• Include all dependent objects
• Copy data
• Replace existing data
• Copy all objects

The following options should be disabled:

• Include extended properties
• Append data
• Use default options
• Click the Options button

In the Advanced Copy Options window, the following options should be enabled:

• Copy database users and database roles
• Copy object-level permissions
• Copy indexes
• Copy triggers
• Copy full text indexes
• Copy PRIMARY and FOREIGN keys
• Use quoted identifiers when copying objects
• The following options should be disabled:
• Copy SQL Server logins
• Generate Scripts in Unicode
• Click the OK button

In the DTS Import/Export Wizard window:

• Click the Next button

In the DTS Import/Export Wizard window:

1. Enable Run immediately
2. Optional: Enable Save DTS Package (This will save all of your selected options on your local server for later execution)
3. Click the Next button

Optional Step:

1. Enter a Name and Description to save the DTS Package
2. Select the (Local) Server name
3. Select the Use Windows authentication radio button
4. Click the Next button

In the DTS Import/Export Wizard window:

• Click the Finish button
• The progress is displayed
• A message box confirms successful transfer

SEE ALSO: Database Knowledge base Articles | "Important IP Addresses" knowledge base article

Contact Support

If you are still having issues, please contact us for further help.
1-877-EPHOST1 | support@ephost.com