Press "Enter" to skip to content

Category: T-SQL Tuesday

Use Backup Compression

Thomas Rushton advocates for enabling backup compression:

SQL Server backup compression – does what it says on the tin. Instead of SQL Server taking a backup by reading pages / extents of data from the database file and writing them out to the backup file, it compresses the data before it writes. It’s not the best compression you’ll get, as it won’t read the entire file before compressing it; however, it’s good enough that on OLTP databases that contain normal varchar / numeric data you could see savings of over 75% – indeed, I have some databases that save 90% of disk space. And that’s made my storage guys happy. Well, less unhappy.

One side note:  if you’re also encrypting backups (available in the product since SQL Server 2014), encryption happens after compression, so you can take advantage of both.

1 Comment

It’s Not Just Backups

Dave Mason looks at alternatives to restoring databases:

Database Snapshots

A snapshot creates a read-only static view of a source database. With a snapshot, DML statements can be run on the source database and the snapshot database will preserve the original data. The snapshot can be used to “undo” data changes in the source database. There’d likely be more T-SQL/scripting work involved than a simple database restore. However, a snapshot has less “overhead” than a backup (at first). As noted in the MSDN documentation, “As the source database is updated, the database snapshot is updated. Therefore, the longer a database snapshot exists, the more likely it is to use up its available disk space.” In addition, there are prerequisites for and limitations on database snapshots.

Restoring a database backup should be easy, but it might also tell you that there was a failure somewhere.  If you’re regularly restoring backups because of data entry issues, then it might make sense to keep a history of the data so you have tools to fix issues short of the nuclear option.

Comments closed

Restoring Multiple Differential Backups

Kenneth Fisher crashes his own T-SQL Tuesday party:

This has led to the belief (or at least I believe this is one of the causes) that you can only restore a single differential backup. And up until the last few weeks I’d believed that myself. So, to set up a fairly simple test. I’m going to take a backup, create a table, and make some changes with differential backups in between the changes.

Check it out.  I don’t really see a good case for restoring multiple differentials (because each differential has the complete set of changes since the last full backup, so the differential at X+t is a proper subset of X+2t if your goal is to restore back to X+2t), but having multiple differentials, absolutely.

Comments closed

Multi-Tenant Database Backups

Kennie Nybo Pontoppidan thinks about multi-tenant databases in Azure and how you might back them up:

Backup-restore is not directly supported by standard methods in SQL Server/Azure SQL database. One possible way to backup a tenant could be to have a script, which could bcp data to text files. Restore could similarly be a script, which could bcp from txt files to tables in the destination database. Both scripts could be auto-generated from tenant metadata. If the schema for a tenant has 100 tables, the number of tables in a database in this model grows quickly, and the administrative cost of maintaining scripts and tenant metadata could be high. As a side note, no query execution plans can be reused across tenants, since table names are different.

Thinking about customers which share schema, tables, etc. but need to be handled differently requires some additional effort; pretty much all of the tools around SQL Server assume that you care about things at the table, filegroup, or database level.

Comments closed

Backups To Azure

Andy Mallon shows how to move your backups up to Azure Blob Storage:

Geographically-redundant storage, on a cool access tier currently costs about $0.02/GB. That’s a fraction of what it would cost you to have it on your NAS, let alone having multiple geographically redundant copies, and the effort to set up and maintain your copy to off-site.

And up to a certain size, it’s faster to retrieve the backups from Azure than calling the off-site storage company.

Comments closed

Using DBATools For Backups And Restores

Chrissy LeMaire talks about the dbatools Powershell suite and its cmdlets related to backups and restorations:

Restore-SqlBackupFromDirectory is super useful in a pinch, too, but it’s not quite fleshed out to our standards, so it doesn’t have a corresponding webpage. We expect this will be renamed by the next release.

Again, I usually have all the docs for all of our newly released commands, but I was trynna make it for #tsql2sday.

Check out her post, and then check out dbatools.

Comments closed

Thinking About Backups

Rob Farley has a set of questions you should ask yourself regarding your backups:

Does your disaster testing include a situation where a well-meaning person has taken an extra backup, potentially spoiling differential or log backups?

Does your disaster testing include random scenarios where your team needs to figure out what’s going on and what needs to happen to get everything back?

Something which might be helpful would be to catalog the reason why you restored a particular backup (or when somebody asks you for a backup but you can’t do it), and then have a plan to handle that scenario in the future.

Comments closed

Growing New Speakers

Andy Yun hosted this month’s T-SQL Tuesday and it was a huge success:

Welcome to this month’s T-SQL Tuesday Round-Up! A few weeks ago, I sent out a call for bloggers and must say that I’m utterly blown away by the response. A whopping FORTY bloggers responded last week with contributions for Growing New Speakers!  Four – zero!  You people are all amazing!!!

There’s a lot to read here.  If you’ve ever thought about speaking, give it a try; there are 40 people trying to convince you this month.

Comments closed