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»»»

The Copy Cat Poll Expand / Collapse
Author
Message
Posted Thursday, August 2, 2012 11:44 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 6:53 PM
Points: 31,177, Visits: 15,623
Comments posted to this topic are about the item The Copy Cat Poll






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1339634
Posted Friday, August 3, 2012 3:24 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 379, Visits: 690
Lots of copies..This is all for 1 database...

production application:
Main server:
primary copy of the database
backup image from previous night

DR/HA server:
copy of the database

UAT server:
2x copy of the database

reports systems:
vm report server:
copy of the database
3x backups

physical report server:
copy of the database
1x backup

vm report server (test system):
copy of the database
1x backup


backup servers:
entire VM report server backed up
entire physical report server backed up
entire vm test system backed up

application servers back up directly to tape so not included in backup servers.


in all 21 copies of the database plus a partial (annonymised) copy on my workstation that I sometimes use for dev.

I think it's about time we reviewed our backup stratergy...


Ben

^ Thats me!


----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Post #1339701
Posted Friday, August 3, 2012 4:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:45 AM
Points: 432, Visits: 313
For each Application we have
1x Copy on Production Server
10x Days Backups on NAS
1x Copy on UAT Server
1x Backup of UAT on NAS
1x Copy on Recovery PC (All DB's restored from NAS BAckups to here and DBCC Checked each day)

Depending on Dev work
1x (anonymised) copy per developer PC
Post #1339718
Posted Friday, August 3, 2012 5:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 2:22 AM
Points: 16, Visits: 459
Its 8 for databases for our main production database

Main Production System
Production Support
QA
UAT
Dev
Internal Training
External Training

and a nightly refreshed Reporting DB

If you include backups

3 x Nightly Full backups on Disk for each DB bar the Reporting DB + 1 months on tape + 2 years of monthly tapes

Which I make (3x7) + (31x7)[as July has 31Days!] + (24x7) + 8 = 414

That was a bit of a surprise for my boss! He had guesstimated 40 odd

Also coming soon a DR solution which will be a mirror of our entire IT Universe and as many nightly snapshots of individual servers as we wish


http://sqldbawithabeard.com Its where I blog

SQL South West UK User Grouphttp://sqlsouthwest.co.uk/
Post #1339735
Posted Friday, August 3, 2012 5:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 288, Visits: 519
We have 66 TB of data. Developers routinely ask for a copy of a production database, but most of the application databases are over 2TB so the request is denied. All non-production areas have a full schema, but limited data.


Backups go directly to tape and should have 3 full copies at the offsite vault.


Post #1339750
Posted Friday, August 3, 2012 6:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 1, 2013 1:47 PM
Points: 167, Visits: 164
1 Dev environment
1 Integration environment
1 Acceptance Testing environment
1 Automated Testing environment
1 Production environment

Backups weekly full, nightly differentials (unless diff is 50% size of database then take fulls), hourly transactionals

We have an ETL out to pre BI staging area which concievably can be reversed to recreate any database in that process.

187TB of data 150 plus servers, 800 plus databases
3 DBA's
Free coffee


Post #1339785
Posted Friday, August 3, 2012 6:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:25 PM
Points: 1,851, Visits: 2,186
1 instance of production database, 3 test databases - one full copy for each developer - taken as rotating snapshots of the production database.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1339790
Posted Friday, August 3, 2012 6:57 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
1 Production Environment
3 Full Backups + 12 Differentials + 36 Transaction log backups on the production server

1 Staging Environment
1 Full Backup copied from Production at some ponit

1 Development Environment
1 Full Backup copied from Production at some point

1 Product Research Environment (SQL 2012)
1 Full Backup copied from Production at some point

Each environment has a copy of the database on the instamce.

All backups are compressed if that matters.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1339791
Posted Friday, August 3, 2012 7:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 3, 2014 10:28 AM
Points: 94, Visits: 676
1 - production
1 - mirror copy
1 - reporting server
2 - test servers
2 - development servers

@6 - backup copies on SAN

Yikes!!! I hadn't really considered the number of these puppies that have been replicated around here! 13+ copies of the production db!!! Holy redundancy batman!!! Even subtracting the copies produced by the backups, 7 copies of it seems pretty heavy - especially at around 64 GB each!
Post #1339807
Posted Friday, August 3, 2012 7:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:35 PM
Points: 113, Visits: 423
Ours is a small shop with a relatively small 3-4 GB database for our primary app.

We have 4 copies of the DB plus backups.

1 Production
1 Development\Testing
1 Training

1 out of date copy on my fully encrypted laptop so I can do development or troubleshooting work on the road if I have to.
Post #1339808
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse