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?