I’ve been working on Databases Inventory and wrote this small query to get information for our SQL Server database repositary. This is a very basic query and I was hoping to get something from Google, but couldn’t find anything that includes all the essential information about databases such as Data and Log files sizes, last backup time and size, etc. So, I had to write it by myself and decided to share it with anybody who wants to save 5 minutes of their time by googling it. This T-SQL query will work on any version starting from SQL Server 2005.
SELECT sd.name AS 'DatabaseName'
, SUSER_SNAME(sd.owner_sid) AS 'DbOwner'
, sd.compatibility_level AS 'Compatibility'
, sd.recovery_model_desc AS 'Recovery'
, sd.page_verify_option_desc AS 'ChecksumLevel'
, sd.snapshot_isolation_state_desc AS 'Snapshot'
, sd.state_desc AS 'Status'
, sd.user_access_desc AS 'UserAccess'
, CAST(DATABASEPROPERTYEX(sd.name, 'Updateability') AS varchar(256)) AS 'Updateability'
, CAST(mfdata.DataSize*8/1024 AS int) AS 'DataSizeMB'
, CAST(mflog.LogSize*8/1024 AS int) AS 'LogSizeMB'
, CAST(b.backup_size/1024/1024 AS int) AS 'LastBackupSizeMB'
, m.physical_device_name AS 'BackupDevice'
, b.backup_start_date AS 'LastBackupStart'
, b.backup_finish_date AS 'LastBackupFinish'
, sd.create_date AS 'DbCreateDate'
, sd.collation_name AS 'Collation'
, sd.is_auto_close_on AS 'IsAutoClose'
, sd.is_auto_shrink_on AS 'IsAutoShrink'
, sd.is_auto_create_stats_on AS 'IsAutoCreateStatistics'
, sd.is_auto_update_stats_on AS 'IsAutoUpdateStatistics'
, sd.is_fulltext_enabled AS 'IsFulltextEnabled'
, sd.is_parameterization_forced AS 'IsParameterizationForced'
FROM master.sys.databases AS sd
LEFT JOIN (SELECT database_id, SUM(size) DataSize FROM master.sys.master_files WHERE type = 0 GROUP BY database_id, type) mfdata
ON mfdata.database_id = sd.database_id
LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM master.sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog
ON mflog.database_id = sd.database_id
LEFT OUTER JOIN msdb.dbo.backupset b
ON (sd.name = b.database_name) AND b.backup_start_date =
(SELECT MAX(backup_start_date) FROM msdb.dbo.backupset WHERE database_name = b.database_name AND type = 'D')
LEFT OUTER JOIN msdb.dbo.backupmediafamily m
ON (b.media_set_id = m.media_set_id)
WHERE sd.database_id > 4
--Last condition omits all system databases. It can be changed to include them as well
You can find a lot of resources describing how to enable remote access to MS SQL Server, but I couldn’t find anything describing how to restrict remote access.
You might need to disable ability to connect to SQL Server instance from network altogether if you place SQL Server in the DMZ (screened subnet) and the web/application server located on the same box is the only service that needs an access to the database.
There are several ways we can do that:
1. Using Windows Firewall. Just make sure that Firewall is enabled and firewall rules deny an access to all ports your SQL Server uses. We don’t need to do anything if all rules are defined by default and haven’t be changed.
2. Using IPSec. We can define rules for IPSec driver to drop any pockets that are related to SQL Server ports. This is the best option if you need to restrict access from specific networks or devices. There are a lot of documentation and blog posts on how to set up IPSec, so you should be able to find this information.
3. If we cannot enable Firewall or IPSec for whatever reason then we can disable remote communication in SQL Server itself by using Network Configuration Manager.
- Open TCP/IP Properties in SQL Server Network Configuration. One of the options would be disabling all protocols except for Shared Memory. However, many application require TCP/IP even if they run on the same box with SQL Server. That’s why I recommend to leave TCP/IP enabled and restrict access to local host only.
- To do that we need to disable listening on all network adapters
- Choose network adapters and port numbers that SQL Server will be listening on. In our case we want to leave only local loppback for IPv4 and IPv6. That means that local applications and services will be able to access SQL Server by using ‘localhost’ name and loopback IP address 127.0.0.1 (::1 for IPv6)
- Restart SQL Server service and check SQL Server logs to make sure that server is listening on locahost only (127.0.01 and ::1). Make sure that no other IP addreses are on the current log.
Now your SQL Server available only on the local computer and unavailable from the network.
The only downside of this configuration is that you need to run SQL Server Management Studio and SQLCMD as any other application from the server itself. Hopefully you have remote desktop access to the server.
By the way, be aware that unchecking ‘Allow remote connection to this server’ under Server Properties in SSMS doesn’t prevent client connections. This option is for server-to-server communication only and controls execution of stored procedures from local or remote servers.
The method described above should work in SQL Server 2000, 2005, 2008, 2012. If you experienced any issues with that approach please share your experience in the comments.