Log Shipping for multiple Databases

jeffveg

I need to setup log shipping for over 70 databases. I also need to test failover on a few of these a couple times a year.

Wile testing I have not found a way to select multiple databases, and when I tested a failover it looks like I need to setup each one again.

Is there a way to script the install of the log shipping? I have not seen anything like this.

Am I correct that after promoting the backup server, I would need to re-create each log shipping job? I admit I may be doing this wrong.

Thanks

Jeff Schmidt

Matthew Flatt

Hi Jeff,

Thank you for your post into the forums.

Unfortunately there is no way of selecting multiple databases when setting up log shipping using the SQL Backup GUI.

I believe it is possible to script the log shipping setup process. If you create a test log shipping scenario and script both create jobs using SQL Server Management Studio you will be able to see the syntax used to create the SQL Agent Jobs.

From here it should be possible to create a stored procedure taking the database name as a parameter to create the jobs.

Unfortunately I have not tried this myself but would be interested in seeing the script if you can get this working.

With reference to promoting the destination server, if you wish to keep this server as active and log ship back to the original source server, log shipping would need to be re-created.

If you wish to get back to your original setup once the source server has been recovered, you could re-seed the databases and use the existing jobs.

Once reseeded you should ensure no old transaction log files are present in the network share.

I hope this helps.