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

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,
                  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 */
MOVE @data_logical TO @datapath, 
MOVE @log_logical TO @logpath

SQL Server ku5zi-bj.mdf aka The Resource Database

9 07 2014

Starting in SQL Server 2005 Microsoft introduced a hidden database called the Resource Database. The resource database contains all the system objects included with SQL Server.

The addition of the resource database was done to expedite the upgrade process, as opposed to adding, modifying, or removing system objects during the upgrade a new resource database is simply copied in and replaces the original resource database.

The objects in the resource database are made available through logical links in the sys schema of all databases.  I must point out that from experience, performing, extreme security hardening, not all of the objects are available to be seen by the mere mortal.

Want a copy of the resource database to play around with?

The easiest way to accomplish this is to grab it from the installation media, although it may be out of date if you have applied any service packs or updates.

I found the file ku5zi-bj.mdf buried deep with in the installation media folder structure. On SQL Server 2014 it is located here x86\Setup\sql_engine_core_inst_msi\PFiles\SqlServr\MSSQL.X\MSSQL\Binn.

Simply copy the database file ku5zi-bj.mdf to where ever you may be keeping your data.

Attach the database through SSMS and be sure to update the .mdf location, remove the log file, and change the name of the database to something else using the AttachAs option.