Search code examples
sql-serverssmsssms-18

How to clone database in Microsoft SQL Server Managment Studio

So I have two databases on the same server

  1. DB_TEST
  2. DB_DEVELOP

I want to make a full copy of the DB_TEST with all of the data and the table structures and everything and alter the DB_DEVELOP to be the same.

I know I made something similar when I was creating the DB_DEVELOP, but I am not sure how to do it now and I want to leave the DB_DEVELOP the same name and permissions as it has so that I can work on the application in the develop mode but using the DB_DEVELOP database so I dont braek something in the DB_TEST database that is going to be a production database later

What I tried

Create a backup of the DB_TEST but I cannot see it in the DB_DEVELOP when I click on restore.

Script database as CREATE To but when I run the script in the DB_DEVELOP it does not work.

Script database with schema and data did not insert all the data from the first database.

Copy database which results in a following error:

Database name already exists in a destination

It is important that the name and the destination database remain the same.

I tried to delete the Develop database to create a new one as described in an answer on similar question with getting an error: 3702

I am not aware that any users are using the database my application is not running not at least the version that connects to this DB and in my SSMS I don't have any panes open with the said database

ERROR WHILE DELETING

Solution

  • First, create­ a backup of the DB_TEST database. To do this, run the following SQL command:

    BACKUP DATABASE DB_TEST TO DISK = 'C:\path\to\backup\DB_TEST.bak';

    Ne­xt, restore the backup as a ne­w database. This will create a copy of the­ original DB_TEST database. We'll call the ne­w database DB_DEVELOP_TEMP. Run the following SQL commands to restore­ the backup:

    USE master;

RESTORE DATABASE DB_DEVELOP_TEMP FROM DISK = 'C:\path\to\backup\DB_TEST.bak'
WITH MOVE 'DB_TEST' TO 'C:\path\to\your\database\DB_DEVELOP_TEMP.mdf',
MOVE 'DB_TEST_log' TO 'C:\path\to\your\log\DB_DEVELOP_TEMP_log.ldf',
REPLACE;