Replace Database WIth Copy Of Other Database

As part of our testing process, I’m trying to automate the following:

1. Drop DatabaseB.
2. Create a backup of DatabaseA
3. Recreate DatabaseB from the backup of DatabaseA

I had hoped something like this would work RESTORE DATABASE DatabaseB FROM DISK = 'c:tempDatabaseA.bak' WITH REPLACE, but that turns out to want to overwrite the existing ldf and mdf files for DatabaseA.

It seems that Azure’s SQL Server has this functionality: CREATE DATABASE DatabaseB AS COPY OF DatabaseA, but this isn’t supported on our on-prem servers.

Is there a simple way (and clean) way to do this?

Answer

Based on feedback from @Larnu and more research, here’s where I ended up:

USE Master

--Drop the existing testing database
IF DB_ID('DatabaseB') IS NOT NULL
BEGIN
    ALTER DATABASE DatabaseB 
    SET SINGLE_USER 
    WITH ROLLBACK IMMEDIATE

    DROP DATABASE [DatabaseB]
END


--create a backup of the database we want
BACKUP DATABASE DatabaseA TO DISK = 'c:tempDatabaseA.bak'
WITH CHECKSUM, COPY_ONLY, FORMAT, INIT, STATS = 10;

-- List the files (for dev purposes only, any file listed here needs a MOVE statement below, you may have user and memory files as well as db and logs)
--RESTORE FILELISTONLY FROM DISK = 'c:tempDatabase.bak'

-- restore the backup top
RESTORE
DATABASE [DatabaseB]  
FROM DISK = 'c:tempDatabaseA.bak'
WITH CHECKSUM,
MOVE 'DatabaseA' TO 'c:tempDatabaseB.mdf',
MOVE 'DatabaseA_log' TO 'c:tempDatabaseB.ldf',
RECOVERY, REPLACE, STATS = 10;