Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Bkup using a database with multiple schemas

Author  Topic 

Starting Member

4 Posts

Posted - 2015-04-27 : 17:17:07

 I have my project where i have one centralized database, where for every company that uses the system has a schema with tables and objects, which have one common schema that is used by all companies, with some tables, as follows:

 CRM Database

    Common Schema: Users and Companies Tables

    Company X Schema: Table Accounts, Contacts, Opportunities, Activities

    Company Y Schema: Table Accounts, Contacts, Opportunities, Activities

    Company Z Schema: Table Accounts, Contacts, Opportunities, Activities

Currently not use the concept of Filegroups and how few clients and bases not very large, do not know if it is still recommended to use. I would like the opinion and recommendation on how to proceed to manage bkups, creating Automatic routines bkups and if you could have bkups partitioned by schemas (from what I've been searching is not possible) even using filegroups.

    What would really like is to perform scheduled and incremental bkups from a first bkup and if possible have them separated by schemas, where if a problem occurs on information from a particular company, only that company would stop to perform the restore process thereof.

   Any ideas and help are welcome.

Best Regards,


Almighty SQL Goddess

38200 Posts

Posted - 2015-04-27 : 17:29:35
I don't understand why your database is designed like this. Typically if you want this kind of separation, you'd just maintain multiple databases. Yes you could use filegroups, but I don't see the need here. Just backup the database. In the event of a restore, which should be very, very rare, you restore the whole thing or the filegroups.

Tara Kizer
SQL Server MVP since 2007
Go to Top of Page

Starting Member

4 Posts

Posted - 2015-04-27 : 22:09:00
Hi tkizer!

I have this structure for a few reasons:

1) With multiple databases I can not get Referential Integrity between tables of different banks, with Schemas I can.

2) I believe that query's with joins between tables in the same database are more performing than the joins between multiple databases.

3) I think bkups management with one database with multiple schemas would be simpler than one separate database for each company, but do not know if I'm right.

4) As my application is centered on the Web for different companies, with the Common Schema, which has the table of users and companies in the validation of the login screen I connect to the bank (which is unique) and after validating the user logged I identify the schema (company) and set up the connection. If you had separate databases, I would need a connection at login to the database with the User information and company and then would have to make new connection to the specific database of the user company logged.

Basically these are the reasons for using this structure.

Thanks for your help.

Go to Top of Page

Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-04-28 : 01:32:34
Unless you go down the filegroup option , then the only other option you have is a Full\Diff option. Focus on maintaining security, therefore users should not be able to read\write objects in other schemas

Jack Vamvas
Go to Top of Page

- Advertisement -