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
Advertisements




Error initializing transaction logs on restore

8 03 2014

An issue created through the use of Transparent Data Encryption (TDE)

To sandbox a set of databases for a specific application a named instance was created on the SQL server. The Original instance had TDE encryption enabled and all databases were encrypted. After creating the new instance, the databases to be moved were decrypted.

ALTER DATABASE <DATABASE NAME>  SET ENCRYPTION OFF

A backup of each database was then taken to be restored on to the new instance. This practice was used over a detach / re-attach method as testing was needed against the new instance in preparation to migrate the application to the new instance.
Attempting to restore the databases I received the following errors in the SQL error log, the restored databases did not become functional due to this issue.

Error: 3283, Severity: 16, State: 1.
The file “XXXXX_log” failed to initialize correctly. Examine the error logs for more details.

After investigation it was reported that this was a known error that occurred on databases that were encrypted by TDE upon restoring them to a new server or instance. Microsoft recommends applying Cumulative Update 4 to resolve the issue. After applying the latest Cumulative Update from Microsoft, Update 11, the problem still persisted.
Further research indicated that although the databases had been decrypted they would require the original certificate they were encrypted with to be restored into the new instance or server. The solution is to implement TDE on the new instance. In implementing TDE on the new instance the standard procedure was followed except with the cvaveat of the certificate. A new one was not genereated, instead the originating database servers certificate was was imported during the configuration.
It is important to note that when TDE is used, backups of keys and certificates are necessary. Store those backups in multiple locations. Failure to take appropriate precautions will result in catastrophic data loss.

/* create a backup of the service master key */
BACKUP SERVICE MASTER KEY TO FILE = 'C:\temp\service_master_key_YYYY-MM-DD.bak' ENCRYPTION BY PASSWORD = ''
GO

/* Create master key for the new instance */
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
GO

/* Backup the new master key */
BACKUP MASTER KEY TO FILE = ' C:\temp\master_key_YYYY-MM-DD.bak' ENCRYPTION BY PASSWORD = ''
GO

/* Here we import the certificate from the original server */

CREATE CERTIFICATE
FROM FILE = ' C:\temp\certificate.cer'

/* We need to use the original private key we used to encrypt the certificate */
WITH PRIVATE KEY (FILE = ' C:\temp\private_key.pvk', DECRYPTION BY PASSWORD = '');
GO

/* Backup the certificate for the new instance or server */
BACKUP CERTIFICATE TO FILE = ' C:\temp\certificate_YYYY-MM-DD.cer' WITH PRIVATE KEY (FILE = ' C:\temp\private_key_YYYY-MM-DD.pvk', ENCRYPTION BY PASSWORD = '')
GO

After configuring TDE and using the original certificate restores from the original database server to the new instance work without any issue.