SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup of standby database


Backup of standby database

Author
Message
Alex Dess
Alex Dess
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 217
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 BigGrin

- EBH

If brute force is not working you're not using enough.
thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3922 Visits: 939
You can't backup a database while it's restoring.



Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare

Steve Jones
Steve Jones
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: Administrators
Points: 84458 Visits: 19224
If you have Enterprise, potentially you can snapshot it

If you restore it with STANDBY, you could query it and suck out data.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3922 Visits: 939
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.



Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare

Alex Dess
Alex Dess
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 217
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.
thecosmictrickster@gmail.com
thecosmictrickster@gmail.com
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3922 Visits: 939
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?



Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare

Steve Jones
Steve Jones
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: Administrators
Points: 84458 Visits: 19224
If you are running these backups out of sequence, use the COPY_ONLY command to prevent breaking the backup chain.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ray Mond
Ray Mond
SSC Eights!
SSC Eights! (833 reputation)SSC Eights! (833 reputation)SSC Eights! (833 reputation)SSC Eights! (833 reputation)SSC Eights! (833 reputation)SSC Eights! (833 reputation)SSC Eights! (833 reputation)SSC Eights! (833 reputation)

Group: General Forum Members
Points: 833 Visits: 1542
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.

Ray Mond
Get a SQL Size 2.0 single-instance license for FREE
Claim one here!
0808
0808
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 35
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.
mathurar
mathurar
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 120
It does break the chain if differential backups are being performed. Using the COPY_ONLY option is a good idea.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search