Press "Enter" to skip to content

Category: Backups

Test Restores

Published 2016-10-12 by Kevin Feasel

Steve Jones implores you to test those database backups by restoring them somewhere:

What do you do? Hopefully you recognize the issue and can fix the issue. Maybe more importantly, you have a backup of the missing certificate.

Most people don’t deal with encryption, but you never know when your backup job might start failing, perhaps writing to a damaged file that appears to work (if you write as a device) but really isn’t capturing the backup file. Perhaps you don’t know that your backups are being written to a location and deleted a day later, but the process that is supposed to copy them to tape or a remote file share is broken.

Any number of things can happen. The point is that you want to be sure that you are actually getting useable backup files.

That means testing restores.

Read the whole thing.

Comments closed

Speeding Up Database Restores

Published 2016-09-23 by Kevin Feasel

Paul Popovich gives tips on how to speed up restores from a DataDomain device:

Recently we had to restore our 5TB prod database to rebuild an AG node. Here’s some things we learned along the way to hopefully help you speed up your restores.

Backup your VLDB to multiple files. We found 12 to be the sweet spot in our setup. Make sure you’re going to 10gig NICs on both ends of the transfer.

In terms of folder directories on that thing we learned to go wide and small. Let me explain. Our setup is this:
\\datadomain\sql\sqlbu\\\Full or Tlog

Click through for more tips.  This is independent of optimizing your restore scripts themselves.

Comments closed

Restoring All Databases

Published 2016-09-16 by Kevin Feasel

Kevin Hill builds a script to reload all databases at once:

We are doing a major upgrade this weekend, so like any good DBA, I have planned for a full backup the night before and needed the ability to quickly restore if it goes sideways and needs to roll back.

The catch is that there are close to 300 identical databases involved.

This is easy enough to do if you know where info is stored in MSDB related to the last backup.

Click through for the script.

Comments closed

Restoring An Azure SQL Database

Published 2016-09-16 by Kevin Feasel

Arun Sirpal discusses ways to restore a database within Azure SQL Database:

You won’t have the ability to use the same name of the restoring database and the database that you want to replace; if you try you get the screen shot below: To get around this I think you would need to drop the old one once the new one has restored then do a rename.

This is a big difference compared to the on-prem version, so be sure to practice this before you find yourself in a crisis.

Comments closed

Migrating To Azure SQL Database

Published 2016-09-16 by Kevin Feasel

Mike Fal discusses BACPACs, DACPACs, and migrating on-prem databases to Azure SQL Database:

SQL Server Data Tools(SSDT) have always had a process to extract your database. There are two types of extracts you can perform:

  • DACPAC – A binary file that contains the logical database schema and possibly the data. This file retains the platform version of the database (i.e. 2012, 2014, 2016).

  • BACPAC – A binary file that contains the logical database schema and the data as insert statements. This stores the platform version, but is not locked into it.

Mike also walks through SqlPackage.exe.

Comments closed

Creating BACPAC Files

Published 2016-09-14 by Kevin Feasel

Kenneth Fisher needs a new BACPAC:

Why are we talking about it?

Well there are two reasons. First because I’m studying how to move databases from SQL Server to Azure SQL Database and back. My first blog on the subject was using the Deploy Database to Microsoft Azure SQL Database option to move a SQL Server database to Azure SQL Database.

Kenneth shows you how to do this through the UI as well as through SqlPackage.exe.

Comments closed

Distributed File System Replication And Backups

Published 2016-09-06 by Kevin Feasel

James Anderson discusses an interesting setting within Distributed File System Replication:

Ideas of a cmd job step (after the backup step) that renamed the .bak files to .BTFU started to form, but a quick search showed that there is a default filter on DFSR folders.

  • Files starting with ~ (temporary files created by programs like Word)

  • Files with .tmp extension

  • Files with a .bak extension.

Read on to learn what you can do to remove extension filters within DFSR.

Comments closed

Log Chains

Published 2016-08-12 by Kevin Feasel

Dave Mason discusses broken log chains:

There are a handful of activities that have broken my SQL Server backup routines. Sometimes a DIFFERENTIAL (or LOG) backup would fail because there was no FULL backup. Other times, a LOG backup would fail because the log chain was broken. Some of those activities include:

  1. Creating a new database.

  2. Changing the recovery model to SIMPLE.

  3. Restoring/reverting a database from a snapshot.

  4. Any other activity that breaks the transaction log chain.

Dave discusses an easy way of figuring out if you’ve just performed an activity which breaks the log chain.

Comments closed

Amazon RDS Supports Native Backup And Restore

Published 2016-07-28 by Kevin Feasel

Brent Ozar reports that Amazon now allows you to use native SQL Server backups for getting data in and out of RDS:

Until now, the single biggest problem has been that both Azure SQL DB and Amazon RDS SQL Server don’t give you access to backup files. If you wanted to get your data out, you were hassling with things like import/export wizards, BCP, or sync apps.

Today, Amazon RDS SQL Server announced support for native backup/restore to Amazon S3.

This is a really, really, really big deal, something Azure SQL DB doesn’t support (and I dearly wish it did). I get even more excited reading this because now Microsoft has to do it in order to remain competitive, and that’ll make Azure SQL DB a much more attractive product for traditional DBAs.

This makes the migration strategy to and from RDS significantly easier.  Brent gives a few examples of how this will help customers.

Comments closed

Parallel Backups Using Powershell Workflows

Published 2016-07-28 by Kevin Feasel

Sander Stad wants to use Powershell workflows to back up databases in parallel:

It’s possible to use a workflow to execute your backups. You have to take into consideration that there is a downside. If you execute all the backups at once you’ll probably get issues with throughput if you’re dependent on a slow network for example.

You could always add another parameter to filter out specific databases to make sure you execute it as a specific set.

Sander does include his script, so check that out.

Comments closed