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.
Thnaks,
Rahul

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

]]>