Setup Custom Log Shipping in SQL Server
When it comes to Disaster Recovery Setup, every DBA has its own preference. I personally prefer Log Shipping since it involves very little effort to setup. Log shipping has many benefits as stated below:-
- Multiple Secondary copies with different delay and uses
- Can be used as Disaster Recovery Technique
- Can be used for Limited Reporting Workload
- Can be used to migrate data to a new location with minimum downtime
- SQL Server Upgrade
- No additional load on Primary instance
- Works well in combination with other HADR features like Mirroring, AlwaysOn, Clustering etc.
But, like in my current organization, if you use Domain Account for SQL Services, then Log Shipping using Custom scripts could be established in much easier way than the default log shipping method. For this purpose, I have created by own Log Shipping procedure [dbo].[usp_DBAApplyTLogs]. Code for this procedure can be found at my github repository SQLDBA-SSMS-Solution. Below is the url:-
https://github.com/imajaydwivedi/SQLDBA-SSMS-Solution/blob/master/LogShipping/usp_DBAApplyTLogs.sql
For more information on how to setup Log Shipping using above script, Watch below video:-
Friends! Please feel free to connect with me using comments. If you like the article, do Like & Share. Happy Coding 🙂
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.
Leave a Reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Hey Ajay love the script was just what I needed to do LS with out access to the primary. One thing I was wondering is there a way to include a delete after the trn files have been applied?
Hi Scott,
Logically, the job which generates Log Backups at Source should be the one to perform maintenance cleanup (removing backups). So, putting code to remove backups from source path is not appropriate.
Still, if you want to do so, you could use below powershell code from my github.
https://github.com/imajaydwivedi/SQLDBA-SSMS-Solution/blob/master/Misscellaneous%20Queries/Maintainence%20-%20Delete%20folder-files%20older%20than%20120%20hours.sql
Thanks & Regards,
Ajay
Hi Ajay,
Can you please help me with the script that it will restore from the storage account