Procedure to Copy SQL Server Database

From Another SQL Server

From Source Server

  1. Determine location of database/logs
  1. Start Enterprise Manager
  2. Click + to expand SQL Server Group
  3. Click + to expand your server group
  4. Click + to expand Databases
  5. Right click your database
  6. Click Properties
  7. Write down location of database
  8. Click Cancel button on Properties dialog
  1. Create a complete backup of the database
  1. Click your database
  2. Click backup database on right side of screen
  3. The Database - complete radio button should be selected
  4. Click the Add button
  5. The File name radio button should be selected
  6. Click ... box
  7. Scroll down and click + to expand the D folder
  8. Click + to expand your directory (SQLxxxx, where xxxx indicates your division)
  9. Click your backup folder to open
  10. Enter your file name in the File name box - (suggestion: BKUP_databasename_yymmdd)
  11. Click OK on the Backup Device Location dialog box
  12. Click OK on the Choose Backup Destination dialog box
  13. Click Overwrite existing media radio button
  14. Click Schedule check box, if you would like to schedule this backup at a later time
  15. If backup is not to be scheduled, skip to step 1.y
  16. Click ... box
  17. The Enable check box should be selected
  18. Click the One time radio button
  19. Select data and time backup is to occur by either keying in On date and On time or using the scroll buttons
  20. Click OK on the Edit Schedule dialog box
  21. Click on the Options tab
  22. Click Verify backup upon completion check box
  23. Click OK on SQL Server Backup dialog box
  24. If backup is not to be scheduled, you are done
  25. Click + to expand Management
  26. Click + to expand SQL Server Agent
  27. Click Jobs
  28. Right click job name on right side of screen
  29. Click properties
  30. Click scroll down button for Owner
  31. Scroll down and select sa
  32. Click the Apply button
  33. Click OK on the back properties dialog box

From Window NT Explorer

  1. If necessary, map source and destination servers
  1. Open Window NT Explorer
  2. Click Tools
  3. Select Map Network Drive...
  4. In Path enter your source/destination server address and directory

    5. (ex. \\biwgdcsql02\sqlcorp)

  5. Click OK button
  1. If necessary, create backup and database destination directories
  1. Click destination server mapped network drive
  2. Click File drop down menu
  3. Move cursor to New
  4. Click Folder
  5. Enter new folder name and press <Enter>
  1. Verify Server Administrator has full control over database directory
  1. Right click database directory
  2. Click Properties
  3. Click Security tab
  4. Click Permission tab
  5. Server name\Administrators should be either the directory owner or have full control
  1. Click + to expand directory on source server containing your database backup
  2. Click + to expand backup destination directory on your destination server
  3. Click folder to open folder containing database backup on your source server
  4. Click and drag database backup file on your source server to destination server folder

From Destination Server

  1. Start Enterprise Manager
  2. Click + to expand SQL Server Group
  3. Click + to expand your server group
  4. Right click Databases
  5. Select All Tasks
  6. Click Restore Databases...
  7. Enter database name to be restored in Restore as database
  8. Click from device radio button
  9. Click Select Devices...
  10. Click Add... button
  11. Click ... button
  12. Scroll down and click + to expand directory containing database backup
  13. Click database backup file
  14. Click OK button on Backup Device Location dialog
  15. Click OK button on Choose Restore Destination dialog
  16. Click OK button on Choose Restore Devices dialog
  17. Click View Contents button on Restore Database dialog
  18. Select backup set to restore
  19. Click OK button on Select Backup dialog
  20. Click Options tab
  21. Verify that the radio button for Leave database operational has been selected
  22. Click OK button on Restore database