We have a secondary transaction log shipped database configured for standby mode. Its primary purpose is for data recovery and for select only queries for reports etc.
As the database is very big we were hoping to be able to do a READ_WRITE file backup (the larger tables like audit trail history etc. are on a secondary file group) for us by developers who need a copy of live when investigating recent issues.
After trial and error and researching so far the only options I can see are:
- Do a restore with recovery and take a backup – but this breaks log shipping and we have to set it up again
- Take the secondary DB offline and physically take a copy the actual underlying databases data and log files – but then I still have to run a truncate table on the data we don't want/need and shrink to get it down to a manageable size
Has anyone had any luck with other options, 3rd party tools etc?
Best Answer
You could restore the same backup that was used to create the log shipped database on another server, and then apply all the transaction log backups. There is no limit to the number of log-shipped databases you can have. I don't know if Management Studio supports this option, but it is fairly straightforward process to implement manually (T-SQL, powershell, etc.) as you are simply copying files and restoring a database. The database will need to stay read-only mode if you plan on restoring any more transaction logs.