May 28, 2013 at 9:58 am
Is there a way to backup/restore a Database Schema (not the Database) in SQL Server 2012? (or SQL Server 2008 R2). In oracle your could backup/restore a user/schema. Can this be done in SQL Server? I know in SQL Server you can have mutliple databases per instance and multiple schemas per database, so I would like to know if I could backup/restore a single schema in SQL Server. If so, how can this be done?
I also would like to know is it better in SQL Server to set up multiple schemas within a database or create a separate database for each schema? What are the pros/cons of having one SQL Server database with 3 schemas verses having 3 SQL Server databases?
Thanks in advance, Kevin
May 28, 2013 at 11:47 am
Depending on your environment schemas can be used a couple different ways. They can logically group types of tables together like
lookup.state
lookup.country
lookup.zip
admin.role
admin.permission
admin.rolePermission
person.name
person.address
dbo.appTable1
dbo.appTable2
...
Schemas can also provide additional security functionality for a database. For example, place all financial tables into a Finance schema and only assign permissions to those in a user defined database role. This prevents regular database users from viewing salaries or bonuses.
You cannot backup a specific schema. You might be able to get away with it by placing schema objects on different file groups and doing file group backups but there's no easy way to do it. You could script the objects in the schema along with data and back it up in that manner.
One database with three schemas or three databases each with one schema. Again, depends on your requirements. There's a lot of things to cover such as security, performance, accessability, roles, users, etc. You have to either think it through or provide more detail for others to assist in the decision making.
May 28, 2013 at 12:09 pm
If you isolate the schemas as was mentioned, it can be possible to do a partial restore to get back information from just the schema. But it's still a lot more work than simply restoring a database backup.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 28, 2013 at 3:59 pm
Thanks Calvo and Grant. Just wondering what are the benefits of creating additional databases verses additonal schemas within a database? I see the advantages of schemas (object name resolution, object organization, and security/permission management, etc.)
However, since you can not easily backup a specific schema and restore it then this leads to one advantage of creating additional databases. In general, what are some other advantages of creating additional databases instead of schemas?
Thanks, Kevin
May 28, 2013 at 9:08 pm
Well, backups, obviously. The issue for me would be, are we talking about functionality that belongs inside the same database or not? For example, do we need referential integrity. You can't use declarative RI across databases.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply