How to do a partial database backup and restore?

Question

Simple problem. I'm working on a single SQL Server database which is shared between several offices. Each office has their own schema inside this database, thus dividing the database in logical pieces. (Plus one schema that is shared between multiple offices.) The database is stored on a dedicated server and we use a single database to keep the backup/restore procedure easier.

The problem, however, is that the Accounting Office might be modifying a lot of data and then the Secretary Office makes a mistake which requires restoration of a backup. Unfortunately, restoring the backup means that Accounting will lose their recently added data.

So, the alternative solution is by restoring the backup into a new database, remove the data from the old accounting schema and move the data for accounting only from the backup top the original database. This is the current solution and it's time-consuming and error-prone.

So, is there a way to make backups of a single schema, possibly through code? And then to restore just that schema, probably through code too?

Answer 1

There is no way to backup and restore just a single schema.

However, you may try this approach: Restore the entire database (all schemas) to a different database xyz_OLD or something like that.

You could then fix the data using a script like:

UPDATE y
    SET col1=o.col1
    FROM xyz.YourTable y
        INNER JOIN xyz_Old.xyz.YourTable o ON y.PK=o.PK

INSERT INTO xyz.YourTable
        (col1, col2, col3,...)
    SELECT
        col1, col2, col3,...
        FROM xyz_Old.xyz.YourTable o
        WHERE NOT EXISTS (SELECT 1 FROM xyz.YourTable y WHERE o.PK=y.Pk)

etc...

Answer 2

You could create a script that copies each of the schemas to a separate database (backup_Accounting, backup_Secretary, backup_Shared), and then creates a backup file for each of those databases. If you ever need to do a restore, you can restore the backup file into the appropriate database and then run a script to copy the data back into the main DB.

Answer 3

You could use filegroups and the partial backup command.

You'll need to move each schema to a different filegroup and then use partial backup/restores as required.

See here for info on partial backups: http://msdn.microsoft.com/en-us/library/ms191539.aspx

See here for info on file groups: http://msdn.microsoft.com/en-us/library/ms179316.aspx

See here for info on piecemeal restore: http://msdn.microsoft.com/en-us/library/ms177425.aspx