How to find the Database Restore Details in SQL Server 2008

It is often useful to know the details from where the database is restored.The restore history of the database is stored in the msdb table "restorehistory".I recently came across this query which gives the complete information of restore history including the Restored Login , From & To Location of the Files

SELECT
rsh.destination_database_name AS [Database],
rsh.user_name AS [Restored By],
CASE WHEN rsh.restore_type = 'D' THEN 'Database'
WHEN rsh.restore_type = 'F' THEN 'File'
WHEN rsh.restore_type = 'G' THEN 'Filegroup'
WHEN rsh.restore_type = 'I' THEN 'Differential'
WHEN rsh.restore_type = 'L' THEN 'Log'
WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
WHEN rsh.restore_type = 'R' THEN 'Revert'
ELSE rsh.restore_type
END AS [Restore Type],
rsh.restore_date AS [Restore Started],
bmf.physical_device_name AS [Restored From],
rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rsh
INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id


You can apply filter criteria such as the restore date , Database by addding a where clause to the existing query as shown below

WHERE
rsh.restore_date >= DATEADD(dd, "No of Past Days" , GETDATE())
AND destination_database_name = ISNULL( "DB Name", destination_database_name)
ORDER BY rsh.restore_history_id DESC

How to import Database Schema to XML

I often get questions on how to import database schema into XML file for doing activities like Data Comaprison etc. I have written a small script to generate the database schema including the following

• Column Details ( Name, data type ,length etc)
• Primary Key
• Foreign Key
• Indexes

Sample Script

SELECT a.name TableName,
( SELECT
c.name ColumnName,type_name(c.xusertype) DataType,
CASE WHEN type_name(c.xusertype)='NUMERIC' THEN CAST(c.prec AS SMALLINT)
WHEN type_name(c.xusertype)='UNIQUEIDENTIFIER' THEN NULL
WHEN type_name(c.xusertype)='BIGINT' THEN
CASE WHEN colstat =1 THEN CAST(IDENT_SEED(a.name) AS SMALLINT)
END
ELSE CAST(c.prec AS SMALLINT)
END DataLength,
CASE WHEN type_name(c.xusertype)='NUMERIC' THEN c.scale
WHEN type_name(c.xusertype)='BIGINT' THEN
CASE WHEN colstat =1 THEN CAST( IDENT_INCR(a.name) AS INT)
END
ELSE NULL
END Scale,
CAST(c.isnullable AS BIT) As IsNullable,NULL AS DataDefault,NULL AS DefConstraintName,
CASE WHEN colstat=1 THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS IsIdentColumn
FROM SYSColumns c
WHERE c.id = OBJECT_ID(a.name)
and a.id = c.id
AND C.CDEFAULT =0
FOR XML AUTO, TYPE
) columns,
(select 'UID' ColumnName,f.name PrimaryKeyName, f.type_desc PrimaryKeyType
from sys.indexes f
where f.object_id = a.id
AND f.NAME IS NOT NULL
AND f.Is_Primary_Key =1
AND OBJECT_ID > 97
FOR XML AUTO, TYPE
)PrimaryKey,
(Select
object_name(rkeyid) Parent_Table,object_name(fkeyid) Child_Table, object_name(constid) FKey_Name, c1.name FKey_Col,c2.name Ref_KeyCol
From
sys.sysforeignkeys s
Inner join sys.syscolumns c1
on ( s.fkeyid = c1.id And s.fkey = c1.colid )
Inner join syscolumns c2
on ( s.rkeyid = c2.id And s.rkey = c2.colid )
where s.fkeyid = a.id
FOR XML RAW,TYPE
) ForeignKey,
(select f.name IndexName ,DBO.fGetIndexCols (object_NAME(f.object_id), f.index_id ) IndexColumn,
f.type_desc IndexType
from sys.indexes f
where f.object_id = a.id
AND f.NAME IS NOT NULL
AND f.Is_Primary_Key =0
AND OBJECT_ID > 97
FOR XML AUTO, TYPE
) Indexes
from sysobjects a
where a.xtype ='u'
FOR XML PATH('Table'), ROOT('TableDetails')