As part of our testing process, I’m trying to automate the following:
- Drop DatabaseB.
- Create a backup of DatabaseA
- 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?
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;