SQL Server Information | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Server Information

Hi All, I need a script that can generate the information below from the SQL Server Prespectives. 1. What are the Databases resides on the server.
2. The user information with rights.
3. Roles exist on SQL Server.
4. Database LDF and MDF location on Server.
5. Backup Information like location and last time backup taken.
You need take help of Script wizard on the Management studio. 1. SP_HELPDB
2. SP_HELPROTECT and catalog view sys.database_permissions
3. sys.database_permissions DMV
5. Query against sysjobhistory (not recommended everytime) orhttp://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1662 Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
in addition to Satya’s post 1. What are the Databases resides on the server.
you can also query sys.databases catalog view 2. The user information with rights.
you might also want to look at "Has_Perms_By_Name" function — Refer BOL 3. Roles exist on SQL Server.
Select *from sys.database_principals where Type=’R’ — Read more about this in BOL 4. Database LDF and MDF location on Server.
Select physical_name from sys.database_files 5. Backup Information like location and last time backup taken.
search for Backup and Restore Tables in BOL Madhu

<pre id="code"><font face="courier" size="2" id="code">SELECT @@servername as ServerName, db_name(database_id) as DatabaseName, Name as LogiCalFileName, <br />Physical_Name [Physical file], (size*<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />/1024 [Size_MB] <br />FROM sys.master_files <br /><br /><br />set nocount on<br />– drop table #t<br />if object_id(‘tempdb.dbo.##mbackup’) is not null<br />drop table tempdb.dbo.##mbackup<br />if object_id(‘tempdb.dbo.#Report’) is not null<br />drop table tempdb.dbo.#Report<br />if object_id (‘tempdb.dbo.#dir’) is not null<br />drop table #dir<br />create table #Dir ( DriveSize Varchar(8000))<br />select <br />database_name as ‘Database_Name’ ,<br />c.physical_name as ‘Database_File_Location’ , <br />(b. physical_device_name) as ‘Backup_Location’ , <br />d.media_family_count ‘BackupFileCount’,<br />Convert( numeric(20,2), ((backup_size)/1024)/1024 )as ‘Backup_size_MB’,<br />DATEDIFF ( mi , MIN(backup_start_date) , MAX(backup_finish_date)) as ‘BackupTime_in_Minutes’,<br />Convert( numeric(20,2), (((backup_size)/1024)/1024 )/ case when DATEDIFF ( ss , (backup_start_date) , (backup_finish_date))= 0 then 1 else<br /> DATEDIFF ( ss , (backup_start_date) , (backup_finish_date))end ) as ‘Thoughput_MB_Sec’,<br />backup_start_date,<br />backup_finish_date,<br />DATEDIFF ( dd , (backup_start_date) , (getdate())) ‘Backup_No_Days_Old’,<br />’Comments’ = Convert(Varchar(10),DATEDIFF ( dd , (backup_start_date) , (getdate())))+’ days old backup….'<br />into ##mbackup<br />FROMmsdb.dbo.backupset a (nolock)<br />join msdb.dbo.backupmediafamily b (nolock) on a.media_set_id = b.media_set_id<br />join msdb.dbo.backupfile c (nolock) on a.backup_set_id = c.backup_set_id<br />join msdb.dbo.backupmediaset d (nolock) on a.media_set_id = d.media_set_id<br />where backup_start_date = (SELECT MAX(backup_start_date)<br />FROM msdb.dbo.backupset (nolock)<br />WHERE database_name = a.database_name<br />AND type = ‘D’)– full database backups only, not log backups<br />and type = ‘d’ and server_name = @@servername –‘ABTSQL3WEBCONTROL1′<br />and c.file_type = ‘d’ and c.physical_name like ‘%.mdf'<br />group by database_name, backup_size, backup_finish_date, backup_start_date,b.physical_device_name,c.physical_name,<br />backup_start_date, backup_finish_date, d.media_family_count <br /><br />select <br />@@servername as ‘SQLServer_instance_Name’ <br />, a.name as ‘Database_Name'<br />, DATABASEPROPERTYEX (a.name, ‘Recovery’) as ‘Recovery_Model'<br />–‘Simple’ as ‘Recovery_Model’,<br />, a.filename as Database_Files_Location<br />, isnull (b.BackupFileCount,0) as BackupFileCount<br />, isnull (b.Backup_Location, ‘***** Backup Never Started*****’) as Backup_File_Location<br />, null as ‘BackupFileSize'<br />, isnull (b.Backup_size_MB,0) as Backup_size_MB<br />, isnull (b.BackupTime_in_Minutes,0) as BackupTime_in_Minutes<br />, isnull (b.Thoughput_MB_Sec,0) as Thoughput_MB_Sec<br />, isnull (b.Backup_start_date, ‘1/1/1900′) as Backup_start_date<br />, isnull (b.Backup_finish_date,’1/1/1900′) as Backup_finish_date<br />, isnull (b.Backup_No_Days_Old,’99999’) as Backup_No_Days_Old<br />, isnull (b.Comments, ‘***** Backup Never Started *****’) as Comments<br />into #Report<br />FROM master.dbo.sysdatabases a (nolock)<br />left join ##mbackup b on b.database_name = a.name<br />where a.name not in (‘model’,’pubs’,’tempdb’, ‘Northwind’,’Lumigent’) <br />and DATABASEPROPERTYEX( a.name, ‘Status’) = ‘ONLINE’ and DATABASEPROPERTYEX( a.name, ‘Updateability’) = ‘READ_WRITE’ <br />select * from #Report<br /><br /></font id="code"></pre id="code"><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />