views:

395

answers:

3

I am pretty sure this isn't possible....

We have a database with several schemas. Each schema belongs to a different user. One user was asking "if I find out I made a whole load of errors would it be possible to revert to the state my data was in yesterday". Obviously we could restore the whole database, but that would restore the other schemas as well which we do not want to do....

A: 

Restore the whole database to a database with a different name.

Copy over the parts that you wish to restore.

Andomar
Thanks. Was thinking about that option, but was looking for a lazy way out...!
MT
+1  A: 

You are correct, it is not possible to restore a single schema only.

That said, had you stored all specific schema objects to a specific Filegroup and had been taking Filegroup backups then you could restore just the affected Filegroup.

If you are administering a large number of schemas/filegroups however, this would be quite cumbersome.

John Sansom
That is interesting. At the moment there are not that many schemas so I might look into that. We already have a tool that deletes all the data from a schema and repopulates from an xls so was thinking about going with Andomar's suggestion and pointing at the restored version rather than the xls, but this might be easier.
MT
Excellent news! Let me know how you get on.
John Sansom
A: 

You have to restore a copy of the whole database to a point in time and then copy over the schema data back into the original database. If this is needed on a regular basis in future you could use filegroups as John suggested and do a partial recovery of a copy, then copy the data back in. But you cannot, even with filegroups, do a partial recovery to a point in time (which is what you're asking for), afaik no such thing exists.

Remus Rusanu