How to ensure dbs are all in sync when restored?

Hi,

In large server environments, how do you handle the issue of backing up SQL Server dbs which may not be in sync with other dbs they rely on?

So if I back up DB1 from a server, and it uses another db which is not backed up, doing a restore when the dbs are in differing state could cause problems? It seems like all dependent DBs should be backed up, regardless of size etc, but in my current job (where we're a datacentre company and I'm a .NET Developer), I only backup some of several dependent DBs on a SQL Server instance.

Thanks

From serverfault blade

• Your first thing is to backup ALL databases, period. Especially if there are dependencies from other databases. Your dependent database backups are useless if they rely on the state of other databases that aren't backed up.

  That said, there are a multitude of ways to address the state issue you describe depending on what kind of application your databases support. Your backup strategy has to go hand in hand with the architecture of your application. Ideally your application architecture is designed with backups in mind.

  If your application is a real-time entry system (an online store for example) then you are faced with something like restoring all the applicable databases to a certain point in time.

  If your application is something more of a calculated reporting system where the system state is stable at the point of the last calculation, then you might be able to get away with just doing full db backups some time after the calculations have completed. You will also need to take user inputs after the calculations have completed into consideration, maybe a restore from a full backup and then rolling trans logs.

  These are some ideas. Hopefully they can help to stear you toward a solution.

  From squillman