Best way to backup SQL server schema

user_5240975 0
Viewed 11500

What is the best way to backup SQL server schema ?? My schema contains (file-groups ,file streams,tables,relations between tables,constraints,users,...)

I want to backup the schema and its related objects, so I can restore it later to the same database or to a different database that has another schema and objects.

I tried using the file-group backup because it has some restrictions (database must be FULL recovery mode, must backup primary file-group and log), but I want to backup only one schema at a time.

Has anyone any idea to backup schema to file (access or any format) or any way.

backup schema sql-server
2 Answers

Unless you have a really large schema, you can just script it. In SSMS right-click on your database, then select Tasks - Generate Scripts... There are some dialogue windows to go through, you can select only certain db objects if you prefer. I'm not sure if this covers file-groups or file streams but all the other db objects you mention are covered. You could try it with just one table to see what happens.

Be careful, on the "Set Scripting Options" dialogue, click Advanced and scroll down to "Types of data to script" in the modal window and ensure that "Schema Only" is selected. You can script the schema, the data or both. If you db contains a lot of data and you try to script that, it will almost certainly cause SSMS to crash. You've said in your comment that you also want to back up data. This is the only way I know to achieve this without backing up all the other schemas in the db.

Most of the other options are self explanatory. The tool will generate a SQL script. You can run it on a empty db to build up the data structure that you scripted (or just one schema if you prefer)

I hope this helps.

user_10070740
Related