Practice Your Restores

December 30, 2009 at 7:35 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication) (, , , , , )

Steven Jones posted an excellent editorial today all about how your backups are only good if you know that you can restore from them. He couldn’t be more correct. I posted the following thoughts in the comments, but I know not everyone reads the comments in articles & editorials. Although, if it’s a good article, you should read the comments, especially on SQL Server Central. Frequently the discussion about the article can be as enlightening as the article itself. But I digress.

Steve’s point, pretty clearly stated but I’ll repeat it, backups don’t matter, restores do. I’m going to pile on to this point just a bit, because it can’t be emphasized enough. Nothing is more important than verifying backups, except, verifying that you know how to run a restore. You’re absolutely right when you say that backups are no good unless you can restore them, but it goes beyond actually validating that the backup files themselves are valid and accessible. You need to know that you, and any other DBA’s in the organization, can actually run a restore, knows how to read the file header, can do a point in time recovery, etc. Practice restoring databases not only validates that the backups are good, but that you’re good as well.

Advertisements

Permalink 1 Comment

Debuting: Beginning SQL Server 2008 Administration

September 3, 2009 at 9:21 pm (SQL Server 2008) (, , , , , )

The new book is up on Amazon. I only worked on three chapters of Rob Walter’s new book and that was after begging to only work on two, so I can’t take much credit for the effort that went into this book. However, thanks to our editor Jonathan Gennick, I was privileged to work with Rob & Carmen, if pretty indirectly. I know I mentioned the book before when it was put up on the Apress web site, but this is Amazon. Once it’s up on Amazon, it’s real.

Permalink 1 Comment

More PowerShell Basics

May 14, 2009 at 9:22 am (PowerShell, SQLServerPedia Syndication) (, , , , )

Because of the data center move, we have a number of different functions that we’re running on totally different sets of servers on different days. None of this is part of our normal maintenance routines, so I’ve been using it as a great opportunity to stretch a little with PowerShell. I’m still learning, a lot.

The latest task was to get the databases of a list of servers backed up. I initially tried it using SMO. It works great. But it’s serial across all the servers as well as across the databases. I’m fine with serial backups on the databases (very, very fine, I saw a parallel backup of all databases once, pretty sparks, like the Fourth of July) but I really wanted all the servers to get backed up in parallel. So I tossed the idea of having PowerShell run the backups, but I did have PowerShell generate the backup scripts for me.

Again, this is simplistic, I know, but I’m really just getting started. I went to a switch instead of the if conditional for more of an excercise than anything. The last script I posted I’ve updated to a switch, but this one should still be an if, but hey, sue me.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
foreach ($svr in Get-Content "c:\scripts\serverlist.txt")
{
 $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
 
 $query = "SELECT bmf.physical_device_name ,bs.database_name FROM msdb.dbo.backupmediafamily AS bmf JOIN msdb.dbo.backupset AS bs ON bmf.media_set_id = bs.media_set_id where bs.backup_start_date > GETDATE() - 1 AND bs.[type] = 'D'" 

 $table = Invoke-Sqlcmd -Query $query -ServerInstance $svr 

 foreach($rows in $table)
{
  $path = "c:\scripts\bu_" + $svr.Replace("\","_") + ".sql"

  switch ($srv.Information.VersionMajor) 
  {
   10 $val="backup database [" + $rows["database_name"] + "] to disk = '" + $rows["physical_device_name"] + "' with compression"}
   default{$val = "master.dbo.xp_backup_database @database ='[" + $rows["database_name"] + "]', @filename ='" + $rows["physical_device_name"] + "', @threads = 1"}Add-Content -path $path -value $val
  }
 Add-Content -path $path -value $val
}
}

Permalink 4 Comments

Disabling Database Encryption

March 13, 2009 at 10:48 am (SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , , , , )

SQL Server 2008 introduced TDE (either Total Database Encryption or Transparent Database Encryption, I’ve seen both) which is a way to get a passive encryption in place on the database to prevent people from stealing backups or detaching files & stealing them. It works with a Certificate in the server, and there are mechanisms around password protection of the Certificates, management etc. Setting it up & turning it on are very easy. This example is from a chapter on a book I’m working on:

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Chapter8Backup’;

GO

CREATE CERTIFICATE Chapter8Certificate WITH SUBJECT = ‘Chapter 8 Certificate’

GO

USE AdventureWorksLT

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE Chapter8Certificate

GO

ALTER DATABASE AdventureWorksLT

SET ENCRYPTION ON

GO

That sets up the encryption and turns it on. Now when you run a backup, you need to move the certificate files with the backup in order to restore the backup to another machine. Cool stuff. What happens when you realize you don’t need to encrypt a backup? Simple, run the ALTER DATABASE to set encryption off. One of the posters at SQL Server Central, Amit Lohia, asked that same question and had the code to test it. What happens is, the backups taken after you disable encryption fails.

Msg 3283, Level 16, State 1, Line 1
The file “TDEDisabling_log” failed to initialize correctly. Examine the error logs for more details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

 

Examining the logs just shows the same error message. So a little searching around on the web found this on Connect (I do love that site). Apparently the certificate would have to be on the other server unless you removed it prior to backing up. Fine. Let’s drop the certificate.

 DROP CERTIFICATE Chapter8Certificate

Unfortunately, that didn’t work either. You can check the status of encryption using the dynamic management view sys.dm_database_encryption_keys like this:

SELECT

db_name(database_id), encryption_state FROM sys.dm_database_encryption_keys

 

That will show your database is not encrypted, a value of zero in the encryption_state column, but still listed on the encryption list. If you were dealing with a really large database, you can check that view to watch the status of encryptiong being turn on or turned off. Still, it’s that listing on the encryption view that seems to be the problem. I’ve looked and looked and I don’t have an answer. So I posted my own issue on Connect. We’ll see what MS has to say about it.

I’ll post any answers I get. If anyone has any good ideas, I’m all ears.

Permalink 2 Comments