vwRestoreHistory

This JOINs the three available tables from msdb that have information on restore history.  One row per file.  If the database has one data and one log file, there will be two rows for this restore.

Use Admin

go

IF OBJECT_ID('dbo.vwRestoreHistory') Is Not Null

       DROP VIEW dbo.vwRestoreHistory

go

CREATE VIEW dbo.vwRestoreHistory

AS

/*     DATE          AUTHOR        REMARKS

       1/25/18       PPaiva        Initial creation.

       SELECT *

       FROM vwRestoreHistory

       ORDER BY ID desc

*/

SELECT  h.restore_history_id ID,

              restore_date RestoreDate,

              destination_database_name DestDB,

              fg.filegroup_name FileGroupName,

              f.destination_phys_name DestPathFile,

              user_name,

              backup_set_id,

              restore_type,

              replace,

              recovery,

              restart,

              stop_at,

              device_count,

              stop_at_mark_name,

              stop_before,

              h.restore_history_id

FROM msdb.dbo.restorehistory h

JOIN msdb.dbo.restorefilegroup fg

       ON fg.restore_history_id = h.restore_history_id

JOIN msdb.dbo.restorefile f

       ON f.restore_history_id = h.restore_history_id

Script for relocating tempdb files

Quick script for relocating tempdb files.


-- Check location of all tempdb files before proceeding 

SELECT *

FROM sys.master_files

WHERE db_name(database_id) = 'tempdb'

-- tempdb (NOTE:  tempdb log goes on same disk as tempdb data for most cases)

ALTER DATABASE tempdb MODIFY FILE ( NAME = 'tempDev', SIZE = 2GB , FILEGROWTH = 1GB,

       FILENAME = 'T:\Sql\Data\tempdb.mdf')

ALTER DATABASE tempdb MODIFY FILE ( NAME = 'tempLog', SIZE = 500MB , FILEGROWTH = 500MB,

       FILENAME = 'T:\Sql\Data\tempdb.ldf')

     

-- You might have to do a service stop/restart before the next 3 lines will work

ALTER DATABASE tempdb REMOVE FILE temp2

ALTER DATABASE tempdb REMOVE FILE temp3

ALTER DATABASE tempdb REMOVE FILE temp4

ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev2', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'T:\Sql\Data\tempdb2.ndf' )

ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev3', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'T:\Sql\Data\tempdb3.ndf' )

ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev4', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'T:\Sql\Data\tempdb4.ndf' )

ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev5', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'T:\Sql\Data\tempdb5.ndf' )

ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev6', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'T:\Sql\Data\tempdb6.ndf' )

ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev7', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'T:\Sql\Data\tempdb7.ndf' )

ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev8', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'T:\Sql\Data\tempdb8.ndf' )

-- Check location of all tempdb files before proceeding – you may or may not have to do a service stop/start for the new properties to take effect.

SELECT *

FROM sys.master_files

WHERE db_name(database_id) = 'tempdb'