Find SQL Server deatils

Tags

CONNECTIONPROPERTY, IP Address TCP Port Product Name Edition SP, SERVERPROPERTY, SQL Server details

Sometimes DBA’s need to quickly extract few SQL Server details like
SQL Server Name
Instance Name
IP Address
TCP Port
Product Name
Edition
SP Level
Product Version
Collation

Many a times we extract this information by running multiple queries or toggling multiple windows, this T-SQL script will help you to extract same information through a single execution.
You can download T-SQL script from Microsoft gallery.

Basically in this script I have demonstrated how to use SERVERPROPERTY and CONNECTIONPROPERTY functions available in SQL Server 2008 and higher.

Hope this helps…!

SQL Server – Finding Last Backup & Restore Date for All Database

Tags

last backup, last restore, SQL Server

Many a times we need to find when my database last backed up or when it was last restored.
Below is the quick script i use to find last backup and restore dates.

Script to find last backup date
SELECT
db.Name AS DatabaseName,
CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101) AS LastBackUpDate
FROM
sys.sysdatabases db
LEFT JOIN msdb.dbo.backupset bus ON bus.database_name = db.name
GROUP BY
db.Name

Script to find last restored date
SELECT
db.Name AS DatabaseName,
CONVERT(VARCHAR(12), MAX(rh.restore_date), 101) AS LastBackUpDate
FROM
sys.sysdatabases db
LEFT JOIN msdb.dbo.restorehistory rh ON rh.destination_database_name = db.name
GROUP BY db.Name

Hope this helps…!