Maintenance/Auditing Scripts for SQL Server

[robboll]

Maybe I am asking for the world, but it seems to me that I should be
able to run some scripts against a SQL Server to derive everything of
interest to a database administrator. Things like:

Server Name
Database Name
Database Owner (DBO)
File Group(s)
Users and Permissions
Database Size/Log File Size and Space Available
Recovery Model
Maintenance Plan w/Backup Location
Last successful backup date/time
etc.

Any suggestions greatly appreciated!!!

RBollinger

[Roy Harvey (SQL Server MVP)]

Re: Maintenance/Auditing Scripts for SQL Server

All the information is available, though you have to ask a lot of
different questions. Some things to look into include @@servername,
sp_helpdb (both with and without a specific database name),
sp_helplogin, sp_helpuser, sp_helprotect. Note that you can inspect
the source of all the system stored procedures so that you can figure
out where the information came from.

For backup information I query tables in msdb, mostly msdb..backupset .

Roy Harvey
Beacon Falls, CT

On Fri, 19 Sep 2008 06:16:51 -0700 (PDT), robboll
<robboll@hotmai l.comwrote:

>Maybe I am asking for the world, but it seems to me that I should be
>able to run some scripts against a SQL Server to derive everything of
>interest to a database administrator. Things like:
>
>Server Name
>Database Name
>Database Owner (DBO)
>File Group(s)
>Users and Permissions
>Database Size/Log File Size and Space Available
>Recovery Model
>Maintenance Plan w/Backup Location
>Last successful backup date/time
>etc.
>
>Any suggestions greatly appreciated!!!
>
>RBollinger

[robboll]

Re: Maintenance/Auditing Scripts for SQL Server

Thanks Roy