SQL Server / Initialize Secondary Database / Generate a full backup of the primary database using SSMS transaction log shipping GUI issue
Posted by Barac in SQL Server, SQL Tips and Tricks on Mar 13th, 2018 | 0 commentsSeveral time, using different SQL Server versions I end up having a problem with initializing secondary database using SSMS transaction log shipping GUI (mainly with large databases).
I got errors similar to:
System.Data.SqlClient.SqlError: Cannot open backup device ‘\\network_path\trnls\db01.bak’. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)
Indicating that you don not have enough permissions.
Even after I confirmed all permissions are OK, and I already have another DB using same network location successfully, still end up with same error.
Solution is simple, you should not use transaction log shipping GUI for initial db backup.
Just manually backup DB and restore with “with norecovery” or “with standby” option to the destination server.
After you finished restoring on secondary location you can initiate log shipping again and choose “Secondary database is initialized”
I managed to find this MSDN post describing similar issue.
Similar Posts:
- SQL Server / Upgrading the Secondary Server Instances (STANDBY)
- SQL Server / SQL Server Management Studio could not delete the Secondary server / Secondary not available
- Log Shipping Issue / Operating system error 32 (The process cannot access the file because it is being used by another process)
- PowerShell / SQL Server / Generate SQL Script to Restore Multiple Transaction Logs
- SQL Server / Monitor Backup and Restore progress
About Me
My name is Zoran, currently living in Auckland, New Zealand. I am a data specialist with more than 15 years of hands-on experience in database administration and optimisation. I am also, Certified Microsoft Trainer (MCT) and Microsoft Certified Solutions Expert (MCSE) with a Master‘s degree in Information Technology. Sharing knowledge and contributing to the SQL Server community is my passion. I am also an organiser of the Auckland SQL User Meetup Group. As well I am active blogger and speaker at different SQL events such as SQL Saturdays, Meetups etc.
read moreAuckland SQL Group
MSDN PROFILE
SQL SERVER UPDATES
- Microsoft SQL Server 2022 Updates / Release Candidate (RC 1) Evaluation Edition (16.0.950.9)
- Microsoft SQL Server 2019 Updates / BETA (15.0.1000.34) / RTM (15.0.2000.5)
- Microsoft SQL Server 2017 Updates / RTM (14.0.1000.169)
- Microsoft SQL Server 2016 Updates / RTM (13.0.1601.5) / SP1 (13.0.4001.0 or 13.1.4001.0) / SP2 (13.0.5026.0 or 13.2.5026.0) / SP3 (13.0.6300.2 or 13.3.6300.2)
- Microsoft SQL Server 2014 Updates / RTM (12.0.2000.0) / SP1 (12.0.4100.1 or 12.1.4100.1) / SP2 (12.0.5000.0 or 12.2.5000.0) / SP3 (12.0.6024.0 or 12.3.6024.0)
- Microsoft SQL Server 2012 Updates / RTM (11.00.2100) / SP1 (11.0.3000.0 or 11.1.3000.0) / SP2 (11.0.5058.0 or 11.2.5058.0) / SP3 (11.0.6020.0 or 11.3.6020.0) / SP4 (11.0.7001.0 or 11.4.7001.0)
Leave a Reply