Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I am evaluating log shipping as a solution to keep a permanent failover copy of our live database. It looks ideal.

We are using Filestream which is part of the reason to use it over mirroring.

I have a secondary database in the (Standby / Read-Only) state. In my tests which are on a small database I have been able to take it offline, copy the mdf and ldf files, create a new database and replace the mdf and ldf files.

This then gives me a copy of the database to test that the log shipping is working as expected, and the idea is that I want to test that the database will be in a state that come go back live straight away.

The problem comes when I go back to our live database and use log shipping. To test that it can failover I cannot copy away the mdf and ldf files because it is using Filestream, and those files won't include the Filestream data.

Suggestions for the testing? I would ideally like to keep the secondary database connected to log shipping because it is big and rebuilding it again wouldn't be ideal.

share|improve this question

2 Answers

up vote 3 down vote

I'm not sure why your copying the mdf and ldf files but I'm assuming that you want a copy of the database without breaking the log chain.

This can be done by taking a backup of the database and specifying the COPY_ONLY option which will not effect the log chain http://msdn.microsoft.com/en-us/library/ms191495.aspx

share|improve this answer
Yes exactly, I want a copy of the database without breaking the log chain. – peter Dec 6 '11 at 23:18
What you are suggesting is not working. I do this, backup database blah to disk = 'c:\databases\blah.bak' WITH COPY_ONLY and I get the error 'The database is in warm-standby state and cannot be backed up until the entire restore ...' – peter Dec 6 '11 at 23:19
To take a backup of the standby database it would need to be set to recover with BACKUP DATABASE StandbyDB WITH RECOVERY, before this the tail of the log of the Live database needs to be backed up and applied to the standyby db sqlskills.com/BLOGS/PAUL/post/… – MartinC Dec 7 '11 at 0:40
1  
If you just need to check the contents of the database restoring with the standby option will allow you to query the standby db msdn.microsoft.com/en-gb/library/ms178615.aspx – MartinC Dec 7 '11 at 1:03
up vote 1 down vote accepted

There doesn't seem to be a good answer to this question. When you have a secondary database for log shipping it remains in the 'Standby / Read-only' mode, and you cannot do much about that.

At some stage I will have to set up a second secondary database, leave it running for a week and then break the log shipping to test that it has worked.

Or else I will just have to break the current log shipping and recreate it.

Looking at the files in the Filestream proves that data is being log shipped correctly.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.