4

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?

  • 3
    When you say 'schema only', are you meaning you only want to backup certain schemas. Or are you using schema to mean the db structure, and you want the create scripts for tables, indexes, etc? – Stuart Moore Jan 16 '13 at 12:01
  • Why would you want to backup the schema only? Aren't you using a versioning system to keep the structure of your database? This is where the schema info should be, not on a nightly backup.. Or, the least, in a set of scripts that create the database and populate it with initial seed data. But I can't understand why would you need to backup the schema every night. Is your database structure so dynamic that you need to monitor it? – Marian Jan 17 '13 at 8:36
5

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:

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.

0

You can use powershell and scripter object to script all the objects in the database.

  • 1
    While true, it is going to be a whole lot of work to coerce the SMO scripter to script every single object of every single type. At least IMHO. – Aaron Bertrand Jan 16 '13 at 17:46
  • Yes.It is going to be a big script. – Gulli Meel Jan 16 '13 at 17:47
  • 2
    Another complication: will it script things in the right order? Not just dependencies and deferred name resolution, but how about foreign keys, etc.? All of the thinking you'll have to put into this to make the output usable is the point behind my blog post. – Aaron Bertrand Jan 16 '13 at 17:49
  • yes. You need to use dependency walker and DriALL script options. I usually use it when I have to truncate a table in DEV environment and it has FK dependencies. – Gulli Meel Jan 16 '13 at 17:54
0

Microsoft has finally released a command line tool of their own to do this.

mssql-scripter is an open-source python command line client that can export creation scripts of objects sans data. It is new as of Spring 2017 and I immediately ran into issues the last time I tried to use it, so YMMV as far as how reliable it is, but it is actively maintained.

Its usage guide details an extensive number of flags you can use to customize the output.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.