logshipping with multiple databases
programmer76
Posts: 12
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
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
Comments
- 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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8