Technical Article

Schema Only Database Backup


Each time that I need to create a Schema Only database backup I get stuck for a good 15 minutes scratching my head - I know that I've done this 50 times, but where is the "with schema only" option on the BACKUP statement?

The answer is not obvious, and hence I end up searching the wrong terms. You need search for "DBCC CLONEDATABASE", not "sql server schema only backup".

The intended use of the DBCC CLONEDATABASE command is to generate a schema-only clone of a database in order to investigate performance issues. But nothing stops you from making a backup of the clone and using it in your development and testing processes.

As an alternate to DBCC CLONEDATABASE; you could also script out the whole database, use DACPAC or manually remove all data from a full backup. But none of those options are as convenient as a good old *.bak file created with 4 lines of T-SQL.

So I'm posting the solution here at SQLServerCentral in the hope that search engines return this script when searching for "sql server schema only backup" in the future, and that I lose less hair scratching my head trying to remember how to make a schema only backup next time.

Now, before you go wild your new super powers, please go read up on DBCC CLONEDATABASE first. Only ever use the schema only backup for development and testing purposes, and don't bother using the "WITH BACKUP_CLONEDB" option. It does not give you any control over the name or location of the backup. Simply backup the clone database yourself.

--Step 1: Clone your database
--Here the "Demo" database is being copied as "Demo_Clone"
--Refresh SSMS's Object Explorer to see the new clone in the list of databases.
dbcc clonedatabase(Demo, Demo_Clone) with verify_clonedb;

--Step 2: Make the clone writable
--All clones are read-only by default, so this is required 
-- if you are going to use the clone for anything
alter database [Demo_Clone] set read_write;

When developers or testers say they need a empty copy of the database 
without any data in it, what they really mean is that they want 
the schema AND all the basic reference data and config tables 
needed to run their code.
So at this point you can also copy in some reference data from the 
source database into your clone.
insert into Demo_Clone..Accounts select * from Demo..Accounts

--Step 3: Create a full backup
backup database [Demo_Clone] to disk = N'C:\temp\Demo_SchemaOnly_20220821.bak';

--Step 4: Remove the clone before the DBA includes it into maintenance plans :)
drop database [Demo_Clone];


3 (2)




3 (2)