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