Multiple Database Backups

Question

Multiple Database Backups

Andy Fox

SSC Journeyman

Points: 93
More actions
September 1, 2004 at 4:26 am

#164908

I have a number of related databases on a single SQL2000 server. How can I tie the backups together to ensure that their relational integrity is preserved.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Create a job. (Enterprise Manager, expand down to Management. Expand SQL Server Agent. Right click on Jobs. Select New Job).

I backup all my databases at "one time" (it's actually one right after the other). In the job make each step a different database backup.

For example, in my FullBackup job...

Step 1 backs up the Master DB

Step 2 backs up the MSDB

Step 3 backs up one of my user DBs

Step 4 backs up another user DB

SQLBill

Andy Fox SSC Journeyman Points: 93 More actions · Answer 2

Thanks for that.

Does this ensure that there will be no changes committed to any of the databases during the execution of the job?

Say I have a transaction that makes changes to Db1 and Db2. This transaction gets blocked while Db1 is being backed up. It could then commit after Db1 backup is complete but before Db2 backup starts. The subsequent backup of Db2 will then be inconsistent with the backup of Db1.

Or have I got the wrong end of the stick?

Ray Mond SSCertifiable Points: 7159 More actions · Answer 3

INSERT, UPDATE, or DELETE statements are allowed during a backup operation. One suggestion to ensure consistency across databases for the backups is to set all databases to single user mode for the duration of the backups, though this will block all other users.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

EdVassie SSC Guru Points: 60478 More actions · Answer 4

There is no way to ensure you have a relationally intact set of data by taking a backup. This is because applications can continue to update the databases while the backups are running.

Conceptually, ensuring you have a relationally intact set of data is a restore issue, not a backup issue. Any attempt to restore individual databases to different points in time will leave you with what is called 'The jagged edge of recovery'.

You can ensure you get a relationally intact set of data on a restore. You need to restore all databases to the same point in time. This will ensure all completed transactions are included in the restored data, and all in-flight transactions are rolled back.

To do a point in time recovery, you need to run your databases using the Full recovery model, and keep your transaction log backups as well as your full database backups.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Andy Fox SSC Journeyman Points: 93 More actions · Answer 5

Thanks very much for that.

We had better try it before we hit a crisis situation!