logshipping with multiple databases

programmer76

Hi gurus,

I have to do log shipping for all the 100 database located on the production server. When i go and try to do it by wizard it will only let me do the log shipping set up for 1 database at a time and automatically 1 back up and 1 restore job is created by the wizard. In that scenario I will have to go through wizard 100 times and create 100 back up and restore jobs. Is there a way to do this easier and faster way in only 1 back up and restore agent job?

Thanks

petey

With regards to backups, you could set up a single job to back up all the trx logs for the databases if:

- their names can fit into one of the multi-database option e.g.

BACKUP LOGS [db1, db2, db3 ...] TO ...
or
BACKUP LOGS [*] TO ...
or
BACKUP LOGS EXCLUDE [db101, db102...] TO

- you do not mind the backups to be taken sequentially

Note that you can dynamically generate the folder to store the files in using properties of the backup e.g.

BACKUP LOGS ... TO DISK = [g:\backups\<TYPE>\<DATABASE>\<AUTO>] ...

With regards to restores, there isn't a way to restore trx logs for multiple databases in a single command. One option I can think of is to write a procedure to loop through the databases in your instance, generate and run the restore command using each database's properties.