Creating 1 backup for multiple DB

is there a way to create one backup from multiple databases ?

Hello,

If you mean being able to append multiple database backusp to a single backup file, then yes this is possible. You do this via NOINIT:

backup database Database_A to disk = 'C:\DBBackupFile.BAK'

backup database Database_B to disk = 'C:\DBBackupFile.BAK' WITH NOINIT

Then, the file C:\DBBackupFile.BAK will contain two backup sets. To reference either of the backup sets, you then need to use the FILE clause of the restore command as such:

restore filelistonly from disk = 'C:\DBBackupFile.BAK' with FILE=2 (this referrs to Database_B)

Cheers,

Rob

|||

If what you are looking for is a backup at one point in time for multiple databases, so that they can all be brought back to a common point in time, the way to accomplish that is with marked transactions.

There is not a mechanism to create a single backup spanning multiple databases.

You can, however use the full recovery model to roll a group of databases forward to a single point in time.

The mechanism for making sure that this point is exactly coordinated across databases is to use the

BEGIN TRANSACTION 'MyTransaction' WITH MARK

statement to begin a marked transaction. If that is a distributed transaction (accomlished by touching each of the databases within the transaction), then the mark will be saved in each database's log.

You can then restore each database independantly, and apply logs using the STOPATMARK = 'MyTransaction' clause to cause all databases to roll forward to exactly the same point in time.