Backup Only Database Schema Everynight

I have an instance with 15 databases on SQL Server 2012. I will set up to backup system which will work every night. I want to backup only the schema because several databases are very big. I haven't found any solution for this problem.

How can I backup only the schema with a job?

You can use a 3rd party tool like Red-Gate SQL Compare. It has a command-line interface that you can easily invoke using cmd or PowerShell (and you can do these from a SQL Server job, but you don't necessarily need to saddle SQL Server Agent with this task - you can just as easily do it using Windows scheduled tasks or a PowerShell job).

You can use your script to tell the tool to compare your source database with an intentionally empty database every night and either:

• save the script somewhere, or

• create a new database, apply the script, backup the new database and drop it.

(Depending on whether you want a script of the schema or an actual empty database that looks like your real database.)

That isn't the only tool to use, of course, but it's the one I have the most experience with. I know that it works and I know they invested in the command-line tool for exactly this purpose. You can check out some other potential offerings here:

• Re-Blog : The cost of reinventing the wheel

You may also see if tools like Visual Studio SSDT (which replaces Visual Studio Team Edition for Database Professionals) does this - you may already have a workable solution in house. I haven't done much with SSDT yet so can't be sure its schema comparison features are scriptable.

StackExchange Database Administrators Q#33009, answer score: 5