What are the challenges you face when working across database platforms? Take the survey

logshipping with multiple databases

programmer76programmer76 Posts: 12
edited March 2, 2011 8:37PM in SQL Backup Previous Versions
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?



  • Options
    peteypetey Posts: 2,358 New member
    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 ...
    BACKUP LOGS [*] TO ...
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.