2

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:

  1. Do a restore with recovery and take a backup - but this breaks log shipping and we have to set it up again
  2. 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?

Improve this question
3
  • You can take file level backup by third party tools but that would not give you point in time backup
    – Shanky
    Commented Feb 25, 2016 at 5:00
  • There are contradicting requirements in the question and comment to answers. You clearly ask for "...Has anyone had any luck with other options, 3rd party tools etc?" and in the comment to @andrewloree 's anwer you write: " ...The second requirement is not for a third party tool - ...". Could you please modify your question so that it matches your specific requirements?
    – John K. N.
    Commented Jul 19, 2017 at 9:06
  • What is the main purpose of your question? Do you want to have a dev backup or do you want to have a backup of your database?
    – Ionic
    Commented Sep 19, 2017 at 12:47

3 Answers 3

Reset to default
2

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.

Improve this answer
1

By your #2 requirement

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

You are talking about removing data piecemeal from a database. That is 3rd party only if you are trying to use backups only, but that really isn't an ideal scenario for that use-case.

If you are that short on space (so much so that you cannot have a third copy of the full database), create another database (name it "DevDB" for example) on the stand-by server, setup an ETL process to script off the objects of your database, and then import only the data you want into the "DevDB" database.

Improve this answer
1
  • Sorry, I wasn't very clear on that. The second requirement is not for a third party tool - we do this via T-SQL and it is just removing unnecessary into that the devs don't need and it makes the copy , restore and use on their local dev PC's a little more practical. Please ignore that comment for the purpose of the question.
    – hamish
    Commented Mar 12, 2016 at 23:51
1

Does the backup have to come from the secondary? If resources allow, you could perform a copy_only backup of the primary (as to not break the LSN chain) to a UNC and write a script to automate the process of moving the backup to a new UNC (if needed) and then restoring the database to the necessary location.

If this isn't possible for your particular scenario I'll modify my answer.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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