Sometimes we need a clear picture for a project or perhaps we have just taken over a new server to administer. We need to know certain things like what the heck is going on with all of these databases and why is the disk full?
select d.name, d.recovery_model_desc , d.is_encrypted, mf.name, mf.physical_name, ((mf.size * 8) / 1024) as 'File Size (MB)', mf.type_desc as 'type' FROM sys.databases d
Inner join sys.master_files mf on d.database_id = mf.database_id
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
Chris Blogs 