SQL New Blogger Challenge November 2015 Edition – Week 3 Digest

This week’s #sqlnewblogger posts!

@eleightondick[T-SQL Tuesday] Data modeling: The trouble with prefixes | The Data Files
@tomsqlAdventures With TomSQL, aka Tom Staab
@EdDebugAutomatically name primary key constraints in SSDT | the.agilesql.club
@rabrystBorn SQL on Twitter: “Temporal Tables – Under the Covers with the Transaction Log. 
@YatesSQLCommunity Involvement–Why Wait? | The SQL Professor
@cjsommerIdentity Column Increment Value (EVEN/ODD) | cjsommer.com
@DBA_ANDYNebraska SQL from @DBA_ANDY: CHECKDB – The database could not be exclusively locked to perform the operation
@ALevyInROCSelectively Locking Down Data – Gracefully – The Rest is Just Code
@eleightondickSQLNewBlogger, Week 3 | The Data Files
@tomsqlBeing Our Collective Best
@SQLMickeyT-SQL Tuesday #72 Summary – Data Modeling Gone Wrong | Mickey’s T-SQL Ponderings

SQL New Blogger Challenge November 2015 Edition – Week 2 Digest

This week’s #sqlnewblogger posts!

@arrowdriveAnders On SQL: T-SQL Tuesday #72: Data modelling gone extremely wrong
@rabrystTime After Time – An Introduction to Temporal Tables in SQL Server 2016 using a DeLorean
@EdDebugDeploy SSDT INSERTS in Batches | the.agilesql.club
@ALevyInROCDon’t Trust the Wizard
@DBA_ANDYNebraska SQL from @DBA_ANDY: T-SQL Tuesday #72 – Implicit Conversion Problems
@eleightondickSQL New Blogger Challenge: Week 1 recap | The Data Files
@eleightondickSQL New Blogger Challenge: Week 2 ideas | The Data Files
@BeginTrySQL Server 2012 Upgrade: The RPC Server is Unavailable | It’s All Just Electrons

SQL New Blogger Challenge, November Edition, Week 1 Digest

Ed Leighton-Dick has renewed his New Blogger Challenge this month. Here are all (I think) the posts for this week after Ed posted his announcement. If I’ve missed any, please let me know and I’ll update.

@arrowdriveAnders On SQL: First Timer Summit impressions.
@EdDebugDeploy SSDT INSERTS in Batches | the.agilesql.club
@EdDebugLooking at SSDT upgrade scripts | the.agilesql.club
@DBA_ANDYNebraska SQL from @DBA_ANDY: PASS Summit 2015 Recap
@eleightondickPASS Summit 2015 Highlights | The Data Files
@OliverAsmusPASS Summit 2015: My Experience | OliverAsmus.com
@EdDebugScriptDom Visualizer | the.agilesql.club
@eleightondickSQL New Blogger Challenge: Looking back… and a new challenge! | The Data Files
@Clem1029Tearing down the wall | SQLDEV@Clemsplace
@ALevyInROCWhy Ask Why?
@rabrystThe SQL Server Family

SQL New Blogger Challenge Digest – Week 4

This week marks the end of Ed Leighton-Dick’s New Blogger Challenge. It’s terrific seeing everyone sticking with the challenge all month and I’m looking forward to catching up with all the posts. Great job, everyone! Keep going!

@MtnDBA#SQLNewBlogger Week 4 – My 1st SQLSaturday session | DBA With Altitude
@Lance_LT“MongoDB is the WORST!” | Lance Tidwell the Silent DBA
@ceedubveeA Insider’s View of the Autism Spectrum: Autism and Information Technology: Big Data for Diagnosis
@JorrissA Podcast Is Born
@toddkleinhansA Tale of SQL Server Disk Space Trials and Tribulations | toddkleinhans.com
@arrowdriveAnders On SQL: First “real” job with SQL.
@arrowdriveAnders On SQL: Stupid Stuff I have done. 2/?. Sometimes even a dev server is not a good dev environment
@way0utwestApril Blogger Challenge 4–Filtered Index Limitations | Voice of the DBA
@ALevyInROCAre You Backing Everything Up? | The Rest is Just Code
@DesertIsleSQLAzure Data Lake: Why you might want one |
@EdDebugBIML is better even for simple packages | the.agilesql.club
@tpet1433Corruption – The Denmark of SQL Instances – Tim Peters
@eleightondickCreating a Self-Contained Multi-Subnet Test Environment, Part II – Adding a Domain Controller | The Data Files
@MattBatalonCreating an Azure SQL Database | Matt Batalon
@pshore73Database on the Move – Part I | Shore SQL
@pmpjrDo you wanna build a cluster?! | I have no idea what I’m doing
@DwainCSQLExcel in T-SQL Part 1 – HARMEAN, GEOMEAN and FREQUENCY | dwaincsql
@AalamRangiGotcha – SSIS ImportExport Wizard Can Kill Your Diagrams | SQL Erudition
@toddkleinhansHow Do Blind People Use SQL Server? | toddkleinhans.com
@DBAFromTheColdIn-Memory OLTP: Part 4 – Native Compilation | The DBA Who Came In From The Cold
@AaronBertrandIt’s a Harsh Reality – Listen Up – SQL Sentry Team Blog
@GuruArthurLooking back at April – Arthur Baan
@nocentinoMoving SQL Server data between filegroups – Part 2 – The implementation – Centino Systems Blog
@MyHumbleSQLTipsMy Humble SQL Tips: Tracking Query Plan Changes
@m82labsReduce SQL Agent Job Overlaps · m82labs
@fade2blackukRob Sewell on Twitter: “Instances and Ports with PowerShell http://t.co/kwN2KwVDOS”
@DwainCSQLRuminations on Writing Great T-SQL | dwaincsql
@sqlsanctumSecurity of PWDCOMPARE and SQL Hashing | SQL Sanctum
@PittfurgSQL Server Backup and Restores with PowerShell Part 1: Setting up – Port 1433
@cjsommerUsing PowerShell to Export SQL Data to CSV. How well does it perform? | cjsommer.com
@gorandalfUsing SSIS Lookup Transformation in ETL Packages | Gorandalf’s SQL Blog
@nicharshWords on Words: 5 Books That Will Improve Your Writing

Are You Backing Everything Up?

We hear the common refrain among DBAs all the time. Back up your data! Test your restores! If you can’t restore the backup, it’s worthless. And yes, absolutely, you have to back up your databases – your job, and the company, depend upon it.

But are you backing everything up?

Saturday night was an ordinary night. It was getting late, and I was about to put my computer to sleep so I could do likewise. Suddenly, everything on my screen was replaced with a very nice message telling me that something had gone wrong and my computer needed to be restarted.

Uh oh.

In 7 1/2 years of using OS X, I’ve had something like this happen maybe 4 times.

The system finished booting after what felt like an eternity & I got back into my applications. I opened up PowerPoint, as I had it open before the crash so I could work on my SQL Saturday Rochester slide deck whenever inspiration struck. I opened my file, and was greeted by nothingness. I flipped over to Finder and saw zero bytes displayed as the file size.

Uh oh.

“But Andy,” you say, “you use CrashPlan, right? Can’t you just recover the file from there?” Well, you’re half right. I do use CrashPlan. I even have a local, external hard drive (two, actually) that I back up to in addition to CrashPlan’s cloud service. But I couldn’t recover from any of those.

CrashPlan configuration - oops

Because Dropbox is already “in the cloud”, I had opted to not back it up with CrashPlan when I first set it up. After all, it’s already a backup right? It’s not my only copy, it’s offsite, it’s all good.

Not so fast. When my system came back up, Dropbox dutifully synced everything that had changed – including my now-empty file.

Dropbox - 0 bytes

So, now what? Fortunately, Dropbox allows you to revert to older versions, and I was able to select my last good version and restore it.

Screenshot 2015-04-26 21.04.48

Lessons Learned

I broke The Computer Backup Rule of Three and very nearly regretted it. For my presentation:

  • I had copies in two different formats – Dropbox & my local (internal) hard drive
  • I had one copy offsite (Dropbox)
  • I only had two copies, not three (local and Dropbox).

Even scarier, if Dropbox didn’t have a version history or it had taken me more than 30 days to realize that this file had been truncated, I’d have lost it completely.

Everything else on my computer was in compliance with the Rule Of Three; I just got lazy with the data in my Dropbox and Google Drive folders. I’ve since updated my CrashPlan settings to include my local Dropbox and Google Drive folders so that my presentation should now be fully protected:

  • Five copies
    • Local drive
    • Two external drives w/ CrashPlan
    • CrashPlan cloud service
    • Dropbox/Google Drive (different content in each)
  • Three formats
    • Spinning platters in my possession
    • Dropbox/Google Drive
    • Crashplan
  • Two copies offsite
    • CrashPlan cloud
    • Dropbox/Google Drive

And don’t forget to test those backups before you need to use them. Dropbox, Google Drive and other online file storage/sync solutions are very useful, but you cannot rely upon them as backups. I don’t think you’ll ever regret having “extra” backups of your data, as long as that process is automatic.

SQL New Blogger Digest – Week 3

Here are the posts collected from week three of the SQL New Blogger Challenge. It’s been compiled the same way previous weeks’ posts were. Everyone’s doing a great job keeping up with the challenge!

@MtnDBA#SQLNewBlogger Week 3 – PowerShell Aliases | DBA With Altitude
@ceedubveeA Insider’s View of the Autism Spectrum: Autism and Information Technology: New Efforts for Kids to Code
@arrowdriveAnders On SQL: Stupid Stuff I have done. 2/?. Sometimes even a dev server is not a good dev environment
@way0utwestApril Blogger Challenge 3 – Filtered Indexes | Voice of the DBA
@eleightondickCreating a Self-Contained Multi-Subnet Test Environment, Part I – Networking | The Data Files
@ceedubveeEmpower Individuals With Autism Through Coding | Indiegogo
@MattBatalonEXCEPT and INTERSECT… | Matt Batalon
@cjsommerFollow the yellow brick what? My road to public speaking. | cjsommer.com
@DBAFromTheColdIn-Memory OLTP: Part 3 – Checkpoints | The DBA Who Came In From The Cold
@MattBatalonIntroduction to Windowing Functions | Matt Batalon
@nocentinoMoving SQL Server data between filegroups – Part 1 – Database Structures – Centino Systems Blog
@Lance_LTMy first year as a speaker | Lance Tidwell the Silent DBA
@MyHumbleSQLTipsMy Humble SQL Tips: Tracking Page Splits
@ALevyInROCPadding Fields for Fixed-Position Data Formats
@tpet1433Sir-Auto-Completes-A-Lot a.k.a. how to break IntelliSense, SQL Prompt and SQL Complete – Tim Peters
@pmpjrstats, yeah stats. | I have no idea what I’m doing
@DwainCSQLStupid T-SQL Tricks – Part 3: A Zodiacal SQL | dwaincsql
@cathrinewTable Partitioning in SQL Server – Partition Switching – Cathrine Wilhelmsen
@gorandalfThe MERGE Statement – One Statement for INSERT, UPDATE and DELETE | Gorandalf’s SQL Blog
@SQLJudoThe Road to SQL Server 2014 MCSE | Russ Thomas – SQL Judo
@GGreggBT-SQL Tuesday #65: FMT_ONLY Replacements | Ken Wilson
@AalamRangiWhat is the RetainSameConnection Property of OLEDB Connection in SSIS? | SQL Erudition
@EdDebugWhat Permissions do I need to generate a deploy script with SSDT? | the.agilesql.club
@_KenWilsonWindowing using OFFSET-FETCH | Ken Wilson
@DesertIsleSQLWhat Does Analytics Mean?
@DesertIsleSQLAzure ML, SSIS and the Modern Data Warehouse
@DesertIsleSQLMusing about Microsoft’s Acquisition of DataZen and Power BI
@GuruArthurCheck for database files not in default location