T-SQL to create a SQL Server Database Inventory

Problem I had to work on Inventory Management System and log details for various SQL instances in my support. The old way to do that was to log into every instance check the details and copy paste that into excel. Well I am a lazy person and I had to find an easier way to … Continue reading T-SQL to create a SQL Server Database Inventory

Determine the last database backup in SQL Server with T-SQL

Problem: T-SQL code to obtain the last database backup time, the last backup location, the last backup size and check if the file exists on the disk or not. Note: Result will only include database backups that were taken on disk. SELECT A.[Server], A.database_name, A.last_db_backup_date, B.backup_size/1024/1024 as backup_size, B.physical_device_name , 'Yes' as 'file_exists' into dbo.##BackupReport … Continue reading Determine the last database backup in SQL Server with T-SQL

T-SQL to script out logins and permissions of all databases

T-SQL to script out logins and passwords for the SQL server instance. Can be useful for taking backups for your logins and DB permissions and can also be useful for migrating logins and permissions. Note: In SSMS, check the “Maximum number of characters displayed in each column” settings before executing the below script. It is defaulted … Continue reading T-SQL to script out logins and permissions of all databases