Restore latest backup of database to same server as new database

30 07 2014

Some, in order to save money, do not purchase a second server for development. If development activities do not require changes to the database system then it is not a horrible idea to just use the same server. Other usage of this script is to have a copy of the production database for a sandbox, reporting, or testing. Here I wrote this script that will run in a maintenance plan to update or create a development database based off of the latest full backup of the source or production database.

/*
Name: Backup_Restore_Maintenance_Plan
Desc: Allows for a single database, the source, to be restored as a new database,
	the target, on the same server
	
Author: Christopher Provolt
Date Created: 29JULY2014

Notes:
Ensure source / target databases and the paths for the data / log files are set correctly 
prior to executing this script

*/
USE master
DECLARE @name			varchar(100)
DECLARE @location		varchar(500)
DECLARE @log_path		varchar(500)
DECLARE @data_path		varchar(500)
DECLARE @log_logical	varchar(100)
DECLARE @data_logical	varchar(100)
DECLARE @target			varchar(100)
DECLARE @source			varchar(100)
DECLARE @datapath		varchar(300)
DECLARE @logpath		varchar(300)

/* Define source and target databases */
set @source = 'AdventureWorks2012'
set @target = 'AW2'

/* define paths and file names for data and log files for target db */
set @datapath = 'E:\MSSQL12.MSSQLSERVER\MSSQL\Data\' + @target +'.mdf'
set @logpath = 'F:\MSSQL12.MSSQLSERVER\MSSQL\Logs\' + @target + '_log.ldf'

/* determine the location of the latest backup for the source DB */
SELECT  @name = x.database_name,
        @location = x.physical_device_name
        
FROM (  SELECT  bs.database_name,
                bs.backup_start_date,
                bmf.physical_device_name,
                  Ordinal = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.[type] = 'D'
                  AND bs.is_copy_only = 0 ) x
WHERE x.Ordinal = 1 AND x.database_name = @source

 /* get logical names of the source database files for recovery */
select @data_logical = name from sys.master_files where database_id = (SELECT database_id from sys.databases where name = @source) AND type_desc = 'ROWS'
select @log_logical = name from sys.master_files where database_id = (SELECT database_id from sys.databases where name = @source) AND type_desc = 'LOG'

/*Restore the database to its new home */
RESTORE DATABASE @target FROM DISK=@location
WITH REPLACE,
MOVE @data_logical TO @datapath, 
MOVE @log_logical TO @logpath




SQL Server 2008 R2 Disaster Recovery After Transparent Data Encryption (TDE) Implementation Re-Encrypt Master Key

16 07 2013

Being new to TDE I decided that my first task after encrypting all the data is to test restoring the data. First I restored the Service Master Key and the Master Key without issue. When I attempted to restoer the Certificate I recieved an error:

“Please create a master key in the database or open the master key in the session before performing this operation”
Microsoft SQL Server, Error: 15581

A quick google search revealed that I needed to issue the following command

OPEN MASTER KEY DECRYPTION BY PASSWORD='<STRONG PASSWORD>'

After accomplishing this I was able to restore the certificate to the server.

When I attempted to restore, using the GUI, the first database again I recieved the same error previously encountered. I ran the command to open the master key again to no avail.

Next I performed a SQL Command restore

USE master
RESTORE DATABASE dbname
FROM DISK = ''
WITH RECOVERY,
MOVE '' TO '',
MOVE '' TO ''
GO

This approach did restore the database and I was able to select data out of it.

This query was then ran to determine the encryption status of the restored database:

SELECT sys.databases.name, sys.dm_database_encryption_keys.encryption_state FROM sys.dm_database_encryption_keys 
JOIN sys.databases ON sys.dm_database_encryption_keys.database_id = sys.databases.database_id

The result was a little disapointing as it did not list the database as being encrypted, and I was still unable to use the GUI to perform restores!

As it turns out the Master key nust be re-encrypted by the service master key

OPEN MASTER KEY DECRYPTION BY PASSWORD='<STRONG PASSWORD>'
GO
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
CLOSE MASTER KEY
GO

Once these commands were issued I was able to restore again from the GUI and the databases restored, by either SQL or GUI, restored encrypted.

Apparently once the master key is re-encrypted after a restore it will automatically be opened as opposed to manually having to be opened. It appears the msater key should be re-encrypted prior to restoring the certificate.