Have you ever needed to get a backup of a secondary database running on Log Shipping, or needed to reload that database into a writable state without breaking log shipping?

Well there is an answer. follow the below steps:

First you will need to have log shipping set up and running, I have a database called LogShipping that has a Primary on DEV-SQL01 and the Secondary in Standby mode on DEV-SQL02 (also called LogShipping)

RWDBFromLogShipping01

Next you will need to download hobocopy from http://candera.github.io/hobocopy (This is a free VSS backup command line application)

RWDBFromLogShipping02

Extract the zip file to your C: drive (Note that i used version “hobocopy-1.0.0.0-W2K3-Vista-x64-Release” on a Windows 2012 R2 machine and it worked successfully)

RWDBFromLogShipping03

Now we can use hobocopy to create a copy of mdf and ldf files. and put them into a new location for me thats a folder called C:\Copy, Run the below command (changing the source and destination to match your needs)

.\HoboCopy.exe "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA" "C:\Copy" Log*

RWDBFromLogShipping04

Now you should see your files in the C:Copy like below:

RWDBFromLogShipping05

Now in SSMS Create a new database with a name you want to use as your now Writeable database for me thats LogShippingCopy

CREATE DATABASE [LogShippingCopy]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'LogShipping', FILENAME = N'C:\Copy\LogShippingCopy.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'LogShipping_log', FILENAME = N'C:\Copy\LogShippingCopy_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB)
GO

Take your new database Offline

-- Take the Database Offline
ALTER DATABASE [LogShippingCopy] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO

RWDBFromLogShipping06

Now delete the files N’C:\Copy\LogShippingCopy.mdf’ and N’C:\Copy\LogShippingCopy_log.ldf’ and rename your copied files to those names:

## Remove empty database files once db is offline
Remove-Item C:\Copy\LogShippingCopy.mdf
Remove-Item C:\Copy\LogShippingCopy_log.ldf

## Rename copied files to blank db names
Rename-Item C:\Copy\LogShipping.mdf LogShippingCopy.mdf
Rename-Item C:\Copy\LogShipping_log.ldf LogShippingCopy_log.ldf

RWDBFromLogShipping08

Now bring LogShippingCopy back online

USE [master]
GO

ALTER DATABASE [LogShippingCopy] SET ONLINE

NOTE – You may run into the error:

Msg 5120, Level 16, State 101, Line 4
Unable to open the physical file “C:\Copy\LogShippingCopy.mdf”. Operating system error 5: “5(Access is denied.)”.
Msg 5120, Level 16, State 101, Line 4
Unable to open the physical file “C:\Copy\LogShippingCopy_log.ldf”. Operating system error 5: “5(Access is denied.)”.
Msg 5181, Level 16, State 5, Line 4
Could not restart database “LogShippingCopy”. Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 4
ALTER DATABASE statement failed.

This is of course due to SQL Server not having permissions to access the files, so you need add the SQL Service account

To resolve this im using a Module from the Script center which you can find here (https://gallery.technet.microsoft.com/scriptcenter/1abd77a5-9c0b-4a2b-acef-90dbb2b84e85)

Add-NTFSAccess -Path C:\Copy -Account 'BADSEEDS\DEV-SQL_SQL' -AccessRights FullControl

now your database should be online and you can do what ever you need to!

RWDBFromLogShipping07

Advertisements