Sql Installation

Code to check Sql Server Installation Date & Time :

SELECT @@SERVERNAME SERVERNAME, CREATE_DATE 'INSTALALTIONDATE'

FROM SYS.SERVER_PRINCIPALS

WHERE SID = 0X010100000000000512000000

Code For Truncating Table with foreign Key Constraints:

Use <DATABASE>
Go
/*
Created By Vimal Lohani
*/
Declare @data nvarchar(max)
-- RECREATE CONSTRAINTS
SELECT ROW_NUMBER() over(order by f.object_id) 'rno', 'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id)+ ']' +
' ADD CONSTRAINT ' + '[' +  f.name  +']'+ ' FOREIGN KEY'+'('+COL_NAME(fc.parent_object_id,fc.parent_column_id)+')'
+'REFERENCES ['+OBJECT_NAME (f.referenced_object_id)+']('+COL_NAME(fc.referenced_object_id,
fc.referenced_column_id)+')' as Scripts
into #tempcreate
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

-- DROP CONSTRAINTS table
SELECT ROW_NUMBER() over(order by f.object_id) 'rowno','ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+
' DROP  CONSTRAINT ' + '[' + f.name  + ']' as dropscript
into #tempdrop
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
--select * from #tempdrop
--Drop Constraints process
declare @max int=(Select max(rowno) from #tempdrop)
while(@max>0)
Begin
Select  @data=dropscript from #tempdrop where rowno=@max
Exec(@data)
--PRINT @data
Set @max=@max-1
End

--Type your all Truncate Code Here

truncate table [dbo].[fact_Employee]



--Complete Truncate Code


--Create Constraints Process
declare @maxx int=(Select max(rno) from #tempcreate)
while(@maxx>0)
Begin
Select  @data=Scripts from #tempcreate where rno=@maxx
Exec(@data)
--PRINT @data
Set @maxx=@maxx-1
End

drop table #tempdrop
drop table #tempcreate

Sql Logs :

Backup Log Code :

SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
   END AS backup_type,
   msdb.dbo.backupset.backup_size,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
--WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
--where database_name like '<dbname>'
ORDER BY
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date asc