SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automate LogSHipping for multiple databases


Automate LogSHipping for multiple databases

Author
Message
desiboy102493
desiboy102493
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 33
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.
RandomStream
RandomStream
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1301 Visits: 591
desiboy102493 - Wednesday, January 17, 2018 11:54 AM
I need to set up log shipping for about 10 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.

Just follow the SSMS logshipping GUI for one db, script out the whole thing, and modify the scripts for the remaining 9 db's.
desiboy102493
desiboy102493
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 33
RandomStream - Wednesday, January 17, 2018 12:14 PM
desiboy102493 - Wednesday, January 17, 2018 11:54 AM
I need to set up log shipping for about 10 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.

Just follow the SSMS logshipping GUI for one db, script out the whole thing, and modify the scripts for the remaining 9 db's.

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

desiboy102493
desiboy102493
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 33
Something like this here
http://www.sqlservercentral.com/articles/Log+Shipping/75042/
but one of the script (4th one) is not working for me.
Sue_H
Sue_H
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36398 Visits: 10377
desiboy102493 - Wednesday, January 17, 2018 11:54 AM
I need to set up log shipping for about 10 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.


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 Databases

Sue



desiboy102493
desiboy102493
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 33
Sue_H - Wednesday, January 17, 2018 12:33 PM
desiboy102493 - Wednesday, January 17, 2018 11:54 AM
I need to set up log shipping for about 10 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.


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 Databases

Sue

Already looked at those link doesn't seem to be working.
Thanks anyway

Sue_H
Sue_H
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36398 Visits: 10377
desiboy102493 - Wednesday, January 17, 2018 1:27 PM
Sue_H - Wednesday, January 17, 2018 12:33 PM
desiboy102493 - Wednesday, January 17, 2018 11:54 AM
I need to set up log shipping for about 10 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.


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 Databases

Sue

Already looked at those link doesn't seem to be working.
Thanks anyway

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



desiboy102493
desiboy102493
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 33
Sue_H - Wednesday, January 17, 2018 1:48 PM
desiboy102493 - Wednesday, January 17, 2018 1:27 PM
Sue_H - Wednesday, January 17, 2018 12:33 PM
desiboy102493 - Wednesday, January 17, 2018 11:54 AM
I need to set up log shipping for about 10 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.


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 Databases

Sue

Already looked at those link doesn't seem to be working.
Thanks anyway

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

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

Sue_H
Sue_H
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36398 Visits: 10377
desiboy102493 - Wednesday, January 17, 2018 1:58 PM
Sue_H - Wednesday, January 17, 2018 1:48 PM
desiboy102493 - Wednesday, January 17, 2018 1:27 PM
Sue_H - Wednesday, January 17, 2018 12:33 PM
desiboy102493 - Wednesday, January 17, 2018 11:54 AM
I need to set up log shipping for about 10 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.


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 Databases

Sue

Already looked at those link doesn't seem to be working.
Thanks anyway

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

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


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



desiboy102493
desiboy102493
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 33
Sue_H - Wednesday, January 17, 2018 2:30 PM
desiboy102493 - Wednesday, January 17, 2018 1:58 PM
Sue_H - Wednesday, January 17, 2018 1:48 PM
desiboy102493 - Wednesday, January 17, 2018 1:27 PM
Sue_H - Wednesday, January 17, 2018 12:33 PM
desiboy102493 - Wednesday, January 17, 2018 11:54 AM
I need to set up log shipping for about 10 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.


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 Databases

Sue

Already looked at those link doesn't seem to be working.
Thanks anyway

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

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


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

ofcourse lol all the parameters were changed. backup directories, restore path...etc.
this script has error in it



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search