Press "Enter" to skip to content

Category: Backups

Multi-Tenant Database Backups

Published 2016-12-14 by Kevin Feasel

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

Published 2016-12-14 by Kevin Feasel

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

Published 2016-12-14 by Kevin Feasel

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

Published 2016-12-13 by Kevin Feasel

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

Backup Encryption

Published 2016-12-08 by Kevin Feasel

Daniel Jones shows how to use backup encryption in SQL Server:

The backup encryption in SQL server is needed due to following reasons:

  • Way to Keep Database File Secure: Users need to encrypt SQL server database backup files because this procedure provides complete security to copy of SQL server data. This security measure will keep transaction logs, tables, and other server data safe from any person, who wants to make use of these data in wrong manner.

  • Accessed Only By Authorized Person: It is impossible to restore an encrypted backup file, if a person is not having certificate or asymmetric key for decryption. Therefore, it means that only authorized persons who are knowing credentials of encrypted backup file can restore data with its full access.

Encrypting backups (and storing the encryption key somewhere independent of the backups themselves) can help prevent a very bad day.

Comments closed

Database Restoration In Linux Via SSMS

Published 2016-11-30 by Kevin Feasel

Andrew Peterson walks through the easy way of restoring a database backup to a Linux installation of SQL Server:

But my Backup file is still not visible in the wizard!

Permissions.  If you drill down into the folders in Linux, we found that the files already present in the /data/ folder are owned by the user mssql.  Our recently copied backup file is NOT owned by mssql, and it not accessible to other users. So, our wizard cannot see the file.

The whole process is pretty straightforward.

Comments closed

Scheduling VM Backups

Published 2016-11-28 by Kevin Feasel

Jens Vestergaard shows how to schedule Azure VM backups:

In this wizard we are presented with three (3) areas of configuration; First we need to decide if it’s in Azure or On-Premises. By selecting Azure, we are left with only Virtual Machine as the only option for the backup. On-Premises has more options, SQL Server, Sharepoint and Hyper-V VM’s among others. This example will be about Azure VM’s, hence we selected accordingly.

Step 2 is about the backup policy, or in other words frequency and retention. I am going with the default settings here, but options are great as you can configure retention range for weekly, monthly and yearly backups in parallel.

It’s easy and like any other backups, might save your bacon later.

Comments closed