About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Thursday, January 25, 2018

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

Tuesday, January 2, 2018

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'