Prev: RUNNING VBA IN AN SSIS PACKAGE
Next: Sum Only Top 20 Customers?
From: Mike Husler on 3 Feb 2010 14:26
Our organization made a preliminary decision to store data for all new
projects in a single database because there is some commonality between
projects and some tables can be shared and relationships can be
maintained within a single database and PK/FK relationships cannot span
databases. For readability and administrative sake, we create a schema
per project and the common 'metadata' tables are in their own schema.

The problem with this is that the database will grow to hundreds of
Gigabytes or perhaps Terabytes eventually. Since each project will have
their own backup requirements, we created filegroups for each project.
It is straightforward to backup the PRIMARY filegroup and the other
filegroups but is there a way to backup a database without including the
filegroups so the database settings and database objects are backed up?
I realize the objects can and should be scripted and source controlled
and any database setting change can also be scripted but was curious
about automatically backing up/restoring databases without the
filegroups. i.e. backup only the database settings and objects via a
Maintenance Plan?

We are using SQL Server 2008.

Regards,
Mike H.
From: Erland Sommarskog on 3 Feb 2010 17:52
Mike Husler (Michael.P.Husler(a)noaa.gov) writes:
> The problem with this is that the database will grow to hundreds of
> Gigabytes or perhaps Terabytes eventually. Since each project will have
> their own backup requirements, we created filegroups for each project.
> It is straightforward to backup the PRIMARY filegroup and the other
> filegroups but is there a way to backup a database without including the
> filegroups so the database settings and database objects are backed up?
> I realize the objects can and should be scripted and source controlled
> and any database setting change can also be scripted but was curious
> about automatically backing up/restoring databases without the
> filegroups. i.e. backup only the database settings and objects via a
> Maintenance Plan?

You can back filegroup by filegroup. And you can restore a database only
partially, so that part of the database is online. But obviously, you cannot
refer to objects in the missing filegroups. Furthermore, if the filegroup
you restore is referenced by FK constraints in the missing filegroups,
reasonably you will get into problems.

The main intention with piecemeal restore is to permit a database to
come online more quickly after a disaster. I'm not really sure what you're
looking for, but I don't think this is what piecemeal restore was intended
for. Also, I'm not sure, but I think this may be available only in
Enterprise and Developer Edition.

The metadata is on the PRIMARY filegroup, so if you back up this group,
you get the metadata backed up.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: RUNNING VBA IN AN SSIS PACKAGE
Next: Sum Only Top 20 Customers?