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.