Automate LogSHipping for multiple databases
-
I need to set up log shipping for about 100 databases. Log backup would occur ever 4 hours and would need to restore automatically to the source server.
If anybody can help me with any kind of script which would work for this process, would be appreciated.Thanks for your help.
- desiboy102493 - Wednesday, January 17, 2018 11:54 AM
Just follow the SSMS logshipping GUI for one db, script out the whole thing, and modify the scripts for the remaining 9 db's.
- RandomStream - Wednesday, January 17, 2018 12:14 PM
Sorry I meant 100 databases. This was there will be 3 job per database.
Is there way to get all dbs backuped in one job and another for restore. (don't really need the copy job)
Thanks -
Something like this here
http://www.sqlservercentral.com/articles/Log+Shipping/75042/
but one of the script (4th one) is not working for me. - desiboy102493 - Wednesday, January 17, 2018 11:54 AM
You setup the log backups with Log Shipping - it's part of the process of log shipping itself. You have log shipping log backups and don't do others outside of those for log shipping. You can schedule the backups for whatever time increments you want. The log is backed up, the file is copied and then restored to the secondary.
In terms of automatically restore to the source server - is that really what you want? I guess you really mean restore to the secondary. And that is what log shipping does - the job on the secondary does this.
It sounds like you may not be real clear on how log shipping works. If you have to set it up and then support it, you may want to make sure you fully understand all of it. Otherwise, you could end up breaking your log backup chains and put the company's data at risk in terms of recoverability. And you also want to be able to troubleshoot thing. Here are links to sample scripts but most of that is useless if you don't understand log shipping. And you will have to make changes as needed to any scripts so understanding the process is needed for that as well.
Scripting Log Shipping Automation
Implementing Log Shipping for Many DatabasesSue
- Sue_H - Wednesday, January 17, 2018 12:33 PM
Already looked at those link doesn't seem to be working.
Thanks anyway - desiboy102493 - Wednesday, January 17, 2018 1:27 PM
They work. You'd need to understand log shipping so you can make the necessary modifications to the scripts. Even if they supply things with variables, you need to understand what those are and how they are used and what permissions would be needed. Like the share. You'd need to know what that is, how its used and why and what permissions are needed.
Go through the process of setting one up with the GUI and script it all out. You can make whatever necessarily changes for the other databases. But you will run into the same issues with that script as well. Log shipping can be setup once in a matter of minutes, it's fairly straight forward.
Sue
- Sue_H - Wednesday, January 17, 2018 1:48 PM
I did set up with GUI for one DB..and did LS manually many times
but the goal is here to do multiple dbs at once. So don't have to manually do each of the db because it's more than 100 DBS
Thanks - desiboy102493 - Wednesday, January 17, 2018 1:58 PM
And that's why you use the GUI, generate the script and then use that script to make whatever necessarily changes to use as your template.
The second link is doing exactly that and just reads the databases, shares, backup directories from a table that was created. Somewhere in the process, you need to tell it what databases, what shares, what backup directories, etc. There won't be any script that has those values for those settings in your environment so you will need to provide that information.Sue
- Sue_H - Wednesday, January 17, 2018 2:30 PM
ofcourse lol all the parameters were changed. backup directories, restore path...etc.
this script has error in it
- desiboy102493 - Wednesday, January 17, 2018 2:38 PMofcourse lol all the parameters were changed. backup directories, restore path...etc.
this script has error in itYou just need to use the group id for log files instead of both 0 and 1.
Sue
-
Everything related to log shipping is available to you for direct manipulation and execution, including system tables and sproc and jobs. Script one out using the GUI and create the script for it. Now just modify the system tables and whatnot. Pretty straight forward once you review the sprocs and tables involved. I have done this many times for clients over the years.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service - Sue_H - Wednesday, January 17, 2018 3:13 PMdesiboy102493 - Wednesday, January 17, 2018 2:38 PMofcourse lol all the parameters were changed. backup directories, restore path...etc.
this script has error in itYou just need to use the group id for log files instead of both 0 and 1.
Sue
Sorry for the late response.
when I query the system tables the group id is always 0 for log files
FROM
SYS.Databases S
INNER JOIN dbo.LSDBList LDB
ON S.[name] = LDB.[database]
INNER JOIN sys.sysaltfiles SF
ON S.[database_id] = SF.[dbid]
WHERE SF.groupid <>0 and SF.dbid = @dbid
open cDBFilesSo it would be just 0 for the group id?
- desiboy102493 - Monday, January 22, 2018 8:17 AM
Yes. You should look at modifying that script to use sys.master_files since it is the replacement for sys.sysaltfiles.
Sue
- Sue_H - Monday, January 22, 2018 8:29 AM
Getting this error saying invalid object LSDBlist, however, i created the table already on the MSDB
Thanks
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply