SQL Server Copy Database options

Need to copy database from databaseName=dba_util to dba_util_test.

In SQL Server, several options to do that.

Option 1 – Using Backup and Restore

Backup
1.      Right click on the database you want to duplicate and choose Tasks->"Back Up..."
2.     Save the back up to a .bak file

Important - When doing export: go to tab Media Options -> select "Overwrite all existing backup sets"

Restore

1.      Right click on the "Databases" ->  "Restore Database"
2.      As the source, select "File" and point to the .bak file you created earlier.
3.      Change the name of the database to restore to.

Important:  When doing import: go to tab Options -> Uncheck "Tail-long Backup"

It will make the restore much faster, and to the point of time the backup was taken.

Option 2 – using Database Copy Wizard
Database -> my_database -> Tasks -> Copy ->
Source server MY_SERVER->
Destination Server MY_SERVER->
Select option Detach + Attach (source database must be offline, faster) or "SQL Management Option" (source database can be online, slower) ->
Select database Name to copy ->
Give new Database Name -> my_database_new
Give new Database File path  D:\DB
Give new Database Log path D:\Logs ->
A new package is created under server_name/DTS Packages/Copy Database Wizard Packages CDW_MY_SERVER_dba_util_copy ->
Run Immediately ->

 

Click Finish to perform the following actions:

 Source: MY_SERVER Other SQL Server Version, Microsoft SQL Server Standard Edition (64-bit) , Build 3223, Microsoft Windows NT 6.3 (14393) NT x64

Destination: MY_SERVER Other SQL Server Version, Microsoft SQL Server Standard Edition (64-bit) , Build 3223, Microsoft Windows NT 6.3 (14393) NT x64

Using SMO online transfer

The following databases will be moved or copied:

 

Copy:dba_util

Destination file will be created: D:\DB\dba_util_test.mdf

Destination file will be created: D:\Logs\dba_util_test_log.ldf

Stop transfer if duplicate database name exists at destination

 

Package scheduled to run immediately