SQL Database Cloning (Clone an existing database to a new database) Create database replica

Duplicating SQL Database

I need to essentially copy all of these tables and the data and the constraints exactly from one database to another.

The simplest backup command would be:

BACKUP DATABASE [db-prod] TO DISK = 'D:\dbbackup\db-prod.bak' WITH INIT;
Now to restore this as a different database, you need to know the file names because it will try to put the same files in the same place. So if you run the following:

EXEC [db-prod].dbo.sp_helpfile;
You should see output that contains the names and paths of the data and log files. When you construct your restore, you'll need to use these, but replace the paths with the name of the new database, e.g.:

RESTORE DATABASE [db-local] FROM DISK = 'D:\dbbackup\db-prod.bak'

WITH MOVE 'db-prod' TO 'C:\Program Files\Microsoft SQL 

Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\db-local.mdf',

MOVE 'db-prod_log' TO 'C:\Program Files\Microsoft SQL 

Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\db-local_log.ldf';

You'll have to replace dbname and newname with your actual database names, and also some folder and C:\path_from_sp_helpfile_output\ with your actual paths. I can't get more specific in my answer unless I know what those are.

Of course if the clone target (in this case DB-B) already exists, you'll want to drop it:

USE [master];

GO

IF DB_ID('db-local') IS NOT NULL

BEGIN

ALTER DATABASE [db-local] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE [db-local];

END

GO