July 24, 2012 at 3:54 am
Hi,
I am new to this forum but I have just had to deal with setting up log shipping for a DR site which would have meant setting this up for 25 databases. As most of you may be aware doing this on a single database can be a chore so you can imagine 25 is laborious work.
After searching around for an easier solution and not really finding much to help (I was on a time limit) I ended up writing my own set of procedures that set up all the log shipping backup jobs, copy jobs and restore jobs in addition to remotely creating the databases on the secondary server, these stored procedures were all initiated from the primary server with minimal configuration.
As I have not seen anything like this in my searches I was wondering if it is something that other DBA's may find useful.
If so I can try to write and publish an article on this site with the code and background information. I assume this is how I go about things.
July 24, 2012 at 4:58 am
stephen.chapman 77519 (7/24/2012)
Hi,I am new to this forum but I have just had to deal with setting up log shipping for a DR site which would have meant setting this up for 25 databases. As most of you may be aware doing this on a single database can be a chore so you can imagine 25 is laborious work.
After searching around for an easier solution and not really finding much to help (I was on a time limit) I ended up writing my own set of procedures that set up all the log shipping backup jobs, copy jobs and restore jobs in addition to remotely creating the databases on the secondary server, these stored procedures were all initiated from the primary server with minimal configuration.
As I have not seen anything like this in my searches I was wondering if it is something that other DBA's may find useful.
If so I can try to write and publish an article on this site with the code and background information. I assume this is how I go about things.
Microsoft already supply a complete set of SPs for setting up and maintaining log shipping
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 29, 2012 at 9:08 pm
stephen.chapman 77519 (7/24/2012)
Hi,I am new to this forum but I have just had to deal with setting up log shipping for a DR site which would have meant setting this up for 25 databases. As most of you may be aware doing this on a single database can be a chore so you can imagine 25 is laborious work.
After searching around for an easier solution and not really finding much to help (I was on a time limit) I ended up writing my own set of procedures that set up all the log shipping backup jobs, copy jobs and restore jobs in addition to remotely creating the databases on the secondary server, these stored procedures were all initiated from the primary server with minimal configuration.
As I have not seen anything like this in my searches I was wondering if it is something that other DBA's may find useful.
If so I can try to write and publish an article on this site with the code and background information. I assume this is how I go about things.
Hopefully I am misunderstanding you and you did not manually implement log shipping.
How to: Enable Log Shipping (Transact-SQL) - SQL Server 2008 R2
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 5, 2012 at 5:58 pm
Hi Stephen,
I have the exact same requirement whereby I have been given the task to LS multiple databases. approx. 25 @ this stage. (SQL2008R2) As I'm not one for reinventing the wheel I presumed there would be code 'out there' available to the SQL community generated by like minded people who have previously encountered such a requirement.
I would be supremely appreciative if you could post your code to enable others to benefit from the time you have devoted towards creating a soluiton.
Many thanks,
John
December 5, 2012 at 11:41 pm
jmaikido (12/5/2012)
Hi Stephen,I have the exact same requirement whereby I have been given the task to LS multiple databases. approx. 25 @ this stage. (SQL2008R2) As I'm not one for reinventing the wheel I presumed there would be code 'out there' available to the SQL community generated by like minded people who have previously encountered such a requirement.
I would be supremely appreciative if you could post your code to enable others to benefit from the time you have devoted towards creating a soluiton.
Many thanks,
John
Errr yes, it's called log shipping and its built into the sql server database engine. There are a complete set of SPs for implementing, maintaining and monitoring log shipping so why re invent the wheel?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 6, 2012 at 2:56 pm
Right-click db, properties, see Trans Log Shipping on left?? Follow wizards 🙂
Regards,
Chris
mssqlconsulting.com
Chris Becker bcsdata.net
December 6, 2012 at 5:43 pm
I think the point is, is there an easy way to setup 25 databases for log shipping without hitting the wizard 25 times.
My answer would be sure, anything can be scripted. Since you're needing to talk to more than one instance at a time as well as potentially setup 25 Windows shares a PowerShell script that calls the procs in the link I showed above would be where I would go with it. You could then call said PowerShell script 25 times, once for each database. Once you have a script that works you could come up with lots of ways to "feed" it the list of n primary and secondary instance and database names.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply