August 11, 2008 at 5:47 pm
OK, before I ask if and how to do this please let me explain why I’m attempting to do this. We have our primary MS-SQL server (server sql01) at an externally hosted site (site A). We also have an internal MS-SQL server for internal operations onsite (sql02, site B).
Currently some operational reporting is done on SQL01 but this is no longer viable due to an increase in transactions. As this reporting needs to be near, but not quite real time I am setting up log shipping between sql01 & sql02 so that we have a read-only version of database that is only slightly out of date.
It would be useful if we could take backups of this standby database for Q&A of development changes. Is there a way to do this? I don’t mind if the standby database is unavailable for reporting during the time it takes to complete that backup. Alternatively I don’t mind using a 2nd standby database just for the purposes of creating this backup. I just need something that is going to be reliable, require no ongoing manual intervention. Otherwise I’ll just backup the primary database and retrieve that file.
I have a feeling that is what I’m going to have to do and I’m just trying to be too tricky by far 😀
- EBH
If brute force is not working you're not using enough.
August 11, 2008 at 10:05 pm
You can't backup a database while it's restoring.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
August 11, 2008 at 10:09 pm
If you have Enterprise, potentially you can snapshot it
If you restore it with STANDBY, you could query it and suck out data.
August 11, 2008 at 10:12 pm
Steve beat me to it - I just thought of the same. If you restore the DB with standby, you can use bcp or probably SSIS to pull the data out. Presumably you already have a copy of the schema in your QA environment. If not, easy enough to use SSMS to generate scripts from the production server.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
August 12, 2008 at 7:56 pm
Unfortunately the database is developed entirely in house for our own purposes. As such there is a higher frequency of schema changes. Not a lot but enough so that using SSIS or BCP to move the data across is not going to be enough for a QA database. There is going to be the risk that there is schema difference between production & QA and the effort required to mitigate that risk is such that it is just not worth it.
I thought things were working out too easy. I’ll just backup the production database once a week and ship that back internally and keep a week’s worth of log file backups.
Thanks for people’s suggestions, they were good but are just not going to work in our particular situation.
- EBH
If brute force is not working you're not using enough.
August 12, 2008 at 9:54 pm
Surely any schema changes would be going through QA first? Assuming software development lifecycle of DEV, TEST, QA, Production is being used. What's the lead time between schema changes going into QA and schema changes going into production? Does it matter if the QA data doesn't get refreshed in that interim period?
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
August 13, 2008 at 6:08 am
If you are running these backups out of sequence, use the COPY_ONLY command to prevent breaking the backup chain.
August 13, 2008 at 8:48 am
If you are running these backups out of sequence, use the COPY_ONLY command ...
Not required for the full database backup, as it does not break the log restore chain in any way.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 14, 2008 at 6:10 am
Here's what we did: Setup a daily scheduled OS task (not Agent!) that runs a .BAT file.
This .BAT file does this:
1. runs a SQL script that:
- sets single user
- drops the development DB
- creates an empty development DB
2. uses "net stop" commands to stop SQL Server services. Order is important, e.g. stop Agent before stopping sql engine)
3. does an OS copy of the read only database files (MDFs and LDFs). e.g.:
- copy RO_database.mdf Dev_database.mdf
- copy RO_database.ldf Dev_database.ldf
4. restarts SQL services (in reverse order from #4)
5. runs a sql script to "activate" the dev database
- EXEC SP_DBOPTION 'Dev_database', 'read only', 'false'
- EXEC SP_DBOPTION 'Dev_database', 'dbo use only', 'false'
- EXEC sp_change_users_login 'Auto_Fix', 'username'
- ALTER DATABASE Dev_database SET RECOVERY SIMPLE
Note that I was having some timing problems on some of the steps above, with certain tasks sometimes failing because the previous step wasn't completely finished. Those problems disappeared once I put a 5 second sleep/delay between every step.
It was a bit of a hassle to setup, but requires no ongoing maintenance. My Dev database is refreshed from production every day. A potential drawback is that the SQL server is completely down for a while each night. This wasn't a problem for us.
June 3, 2009 at 11:43 am
It does break the chain if differential backups are being performed. Using the COPY_ONLY option is a good idea.
April 28, 2011 at 1:19 pm
0808 (8/14/2008)
Here's what we did: Setup a daily scheduled OS task (not Agent!) that runs a .BAT file.This .BAT file does this:
1. runs a SQL script that:
- sets single user
- drops the development DB
- creates an empty development DB
2. uses "net stop" commands to stop SQL Server services. Order is important, e.g. stop Agent before stopping sql engine)
3. does an OS copy of the read only database files (MDFs and LDFs). e.g.:
- copy RO_database.mdf Dev_database.mdf
- copy RO_database.ldf Dev_database.ldf
4. restarts SQL services (in reverse order from #4)
5. runs a sql script to "activate" the dev database
- EXEC SP_DBOPTION 'Dev_database', 'read only', 'false'
- EXEC SP_DBOPTION 'Dev_database', 'dbo use only', 'false'
- EXEC sp_change_users_login 'Auto_Fix', 'username'
- ALTER DATABASE Dev_database SET RECOVERY SIMPLE
Note that I was having some timing problems on some of the steps above, with certain tasks sometimes failing because the previous step wasn't completely finished. Those problems disappeared once I put a 5 second sleep/delay between every step.
It was a bit of a hassle to setup, but requires no ongoing maintenance. My Dev database is refreshed from production every day. A potential drawback is that the SQL server is completely down for a while each night. This wasn't a problem for us.
I followed the steps except I did not run the 4 commands at the end and it works.
May 31, 2011 at 8:28 am
Hello, What you describe is exactly what I'm looking for, it would work perfectly in our environment. Is it possible to get a copy of the bat file? I know how much effort it takes to create it and I would really appreciate it if anyone could post an the actual working bat file. I would reallly appreciate it . Phil
May 24, 2012 at 8:48 am
Have you ever tested recovering from a standby database backed up in this manner? I'm working on a very similar backup strategy. The one thing I'm doing differently is that instead of stopping the SQL Server services and copying the raw database files all at once, I've written a tsql script that runs through the secondary databases one by one and...
1) disables the recovery job
2) offlines the db
3) xcopy the db's files to the backup directory
4) onlines the db
5) enables the recovery job
This works. So far so good.
I have 100+ standby databases on my secondary server and by doing it this way I can continue to allow the logs to be copied and applied on other secondary databases while I'm backing up the one.
Here's my question though and it pertains to how to use these backups to restore the primary database.
Full recovery - this is a piece of cake. I end recovery on the standby (recover database ... with recovery), take a backup, and restore the backup on the primary.
Point in time recovery - Is this possible and if so how?
Lets say I need to restore to a point in time prior to when the last log was applied. I can restore an older copy of the mdf and ldf files, as well as the necessary log backups up to the point where I want to recover. How do I attach the mdf/ldf files (presumably under a different DB name) to begin rolling forward the logs? If you try to do this you get the "You cant attach a database that was being recovered" error and the attach operation fails.
May 24, 2012 at 9:07 am
What about using transactional replication to do this? To minimise load on the production, set up the distributor on a separate server (even the subscriber) and have one-way transactional replication from production to your non-production box (if performance is important, use pull rather than push replication). Both DBs will be online. Then you can run your reports from your non-production box knowing it's up-to-date. You can also backup this database too.
I think there's a stairway about it on this site.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
May 24, 2012 at 9:25 am
I'm not a big fan of replication in any of its forms. I want this to be a worry free backup solution.
Log shipping copies everything. Unless I am mistaken, replication does not. For example changes in table structures, stored procedures, functions, views, indexing, etc. Those things can't be published as an article AFAIK, but log shipping covers it all.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply