Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Copy Cat Poll


The Copy Cat Poll

Author
Message
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36150 Visits: 18751
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
My Blog: www.voiceofthedba.com
BenWard
BenWard
SSC-Addicted
SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)

Group: General Forum Members
Points: 437 Visits: 821
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
----------------------------------------
Paul Smith-221741
Paul Smith-221741
SSC-Addicted
SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)

Group: General Forum Members
Points: 444 Visits: 334
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
mrrobsewell
mrrobsewell
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 657
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
http://sqlps.io PASS PowerShell Virtual Chapter
http://sqlps.io/slack SQL Community Slack Channel
http://sqlps.io/vote Help Microsoft prioritise improvements to sqlserver PowerShell module
http://dbatools.io The Best PowerShell Module for the Modern SQL DBA
http://dbareports.io PowerShell, SQL, SSRS, PowerBi and Cortana to report on your estate
http://sqlps.io/video PASS PowerShell Virtual Chapter YouTube Channel - Subscribe to get notified of new videos



SQL South West UK User Grouphttp://sqlsouthwest.co.uk/
EricEyster
EricEyster
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 520
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.
thadeushuck
thadeushuck
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 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
Whistling
Thomas Abraham
Thomas Abraham
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2333 Visits: 2254
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
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4101 Visits: 72512
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
Larry Kruse
Larry Kruse
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 746
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!
krowley
krowley
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

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