Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Backup of standby database Expand / Collapse
Author
Message
Posted Monday, August 11, 2008 5:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 4, 2013 8:13 AM
Points: 12, 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 :D


- EBH

If brute force is not working you're not using enough.
Post #550669
Posted Monday, August 11, 2008 10:05 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 8, 2014 1:18 PM
Points: 2,664, Visits: 830
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
Post #550729
Posted Monday, August 11, 2008 10:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:34 PM
Points: 31,181, Visits: 15,626
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
Post #550732
Posted Monday, August 11, 2008 10:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 8, 2014 1:18 PM
Points: 2,664, Visits: 830
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
Post #550734
Posted Tuesday, August 12, 2008 7:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 4, 2013 8:13 AM
Points: 12, 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.
Post #551539
Posted Tuesday, August 12, 2008 9:54 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 8, 2014 1:18 PM
Points: 2,664, Visits: 830
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
Post #551562
Posted Wednesday, August 13, 2008 6:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:34 PM
Points: 31,181, Visits: 15,626
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
Post #551799
Posted Wednesday, August 13, 2008 8:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 26, 2014 12:57 AM
Points: 343, Visits: 1,520
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
Yohz Software
Providing SQL Server database tools for 9 years and counting.
http://www.yohz.com
Post #551983
Posted Thursday, August 14, 2008 6:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:32 PM
Points: 22, Visits: 28
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.
Post #552599
Posted Wednesday, June 3, 2009 11:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 9, 2014 2:56 PM
Points: 51, Visits: 107
It does break the chain if differential backups are being performed. Using the COPY_ONLY option is a good idea.


Post #728409
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse