To print this entire article, click the printer icon at the bottom of the page
Click the images for full view

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

• In the Database Properties window:
• Enter the Name of your production database (It's important that the database name and login be identical)
• Click the OK button

• Expand the Security folder
• Right click the Logins icon
• Click New Login

• In the SQL Server Login Properties window:
• Enter the Name of your production login (It's important that the database name and login be identical)
• Select the Defaults / Database created earlier
• Click the OK button

• Expand the database created above
• Right Click Users
• Click the New Database User menu item

• In the Database User Properties window:
• Select the Login Name created above
• Grant: Public, db_accessadmin, db_securityadmin, db_dlladmin, db_datareader, and db_datawriter in the Database role memberships list
• Click the OK button

• Right click on your production Microsoft SQL Server
• From the All Tasks menu, click the Export Data menu item

• In the DTS Import/Export Wizard window:
• Click Next >

• In the DTS Import/Export Wizard window:
• Select the Use SQL Server Authentication radio button
• Enter your SQL login Username
• Enter your SQL login Password (if you do not know your production SQL login password, it can be viewed in your web.config
• Select your production Database
• Click the Next > button

• In the DTS Import/Export Wizard window:
• Select the Use Windows Authentication radio button
• Select your local Database
• Click the Next > button

• In the DTS Import/Export Wizard window:
• Select the Copy objects and data between SQL Server databases
• Click the Next > button

• In the DTS Import/Export Wizard window:
• The following options should be enabled:
  • Copy destination objects
  • Drop destination object first
  • 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
  • Generage 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:
• Enable Run immediately
• Optional: Enable Save DTS Package (This will save all of your selected options on your local server for later execution)
• Click the Next > button

• Optional Step:
• Enter a Name and Description to save the DTS Package
• Select the (Local) Server name
• Select the Use Windows authentication radio button
• 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