Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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?

share|improve this question
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 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 at 8:36

2 Answers

up vote 4 down vote

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:

http://madelinebertrand.com/2012/04/20/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.

share|improve this answer
up vote 0 down vote

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

share|improve this answer
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 at 17:46
Yes.It is going to be a big script. – Gulli Meel Jan 16 at 17:47
1  
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 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 at 17:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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