Blog ala Vidar

Backup ALL databases on one server

--SETTINGS
--copy_only - only takes a full copy, does not truncate log
--stats = 10 - displays a message for each 10% finished. useful if you have large databases

DECLARE @PATH AS NVARCHAR(MAX) = 'C:\SQL\BACKUP\'
DECLARE @SETTINGS AS NVARCHAR(MAX) = 'COPY_ONLY, STATS = 10'



SET NOCOUNT ON

DECLARE 
  @SQL AS NVARCHAR(MAX),
  @DBName AS NVARCHAR(128)

SELECT
  name AS DBName
	INTO #databases
	FROM sys.databases
	WHERE name NOT LIKE 'tempdb'

WHILE EXISTS (SELECT * FROM #databases)
BEGIN
	SELECT TOP 1
	  @DBName = DBName
		FROM #databases

	EXEC ('BACKUP DATABASE [' + @DBName + '] 
		TO DISK = N''' + @PATH + @DBName + '.bak'' 
		WITH ' + @SETTINGS + ', NAME = N''' + @DBName + '-Full Database Backup''')

	DELETE
		FROM #databases
		WHERE DBName = @DBName
END

DROP TABLE #databases

Last usage of ALL databases on one server

WITH IUS AS (
	SELECT 
	  database_id,
	  (SELECT MAX(v) 
		FROM (VALUES (last_user_seek), 
				(last_user_scan), 
				(last_user_lookup)) AS value(v)) AS last_read,
	  last_write
		FROM (SELECT 
			  database_id, 
			  MAX(last_user_seek) AS last_user_seek, 
			  MAX(last_user_scan) AS last_user_scan, 
			  MAX(last_user_lookup) AS last_user_lookup, 
			  MAX(last_user_update) AS last_write
				FROM sys.dm_db_index_usage_stats
				GROUP BY database_id) AS X
)

SELECT 
  D.database_id, 
  D.name, 
  D.create_date AS create_date,
  IUS.last_read,
  IUS.last_write 
	FROM sys.databases AS D
	LEFT OUTER JOIN IUS
		ON IUS.database_id = D.database_id
	WHERE D.name NOT IN ('master', 'model', 'msdb', 'tempdb', 
				'reportserver', 'reportservertempdb')
	ORDER BY D.name