Raport informatii despre baza de date

Situatie

Pentru situatiile in care avem nevoie sa vedem un mic raport despre proprietatile si statusul unei baze de date, am facut o procedura sql , fara parametru, care intoarce pentru baza pe care e rulata informatiile:

– id-ul bazei

– data cand a fost creata baza de date

– numarul de zile de la crearea bazei de date

– locatia fisierului de date al bazei

– tipul de recovery

– statusul

– data ultimului backup al bazei de date

– userii ce au acces pe baza de date

Solutie

create Proc BD_InfoDB_prc

declare @dbname Varchar(20)

select @dbname = db_name()

Set nocount on

Declare @dbid int
Declare @crdate datetime
Declare @difdate datetime
Declare @filename varchar(300)
Declare @months varchar(2)
Declare @days varchar(2)
Declare @hours varchar(2)
Declare @years varchar(4)
Set @dbid=(Select dbid from master.dbo.sysdatabases where name=@dbname)
Set @crdate=(Select crdate from master.dbo.sysdatabases where name=@dbname)
Set @difdate=(Select getdate()-@crdate)
Set @hours=Substring(Convert(varchar(100),@difdate,120),12,2)
Set @days=Substring(Convert(varchar(100),@difdate,120),9,2)
Set @months=Substring(Convert(varchar(100),@difdate,120),6,2)
Set @years=Substring(Convert(varchar(100),@difdate,120),3,2)
If @hours<24 And @days=1
Begin
Set @months=0
Set @days=0
End

If @days<30 And @months=1
Begin
Set @months=0
End

If @months<12 and @years=’1900′
Begin
Set @years=0
End

Set @filename=(Select filename from master.dbo.sysdatabases where name=@dbname)
Print ‘Information for Database ‘+@dbname+’:’
Print ”
Print ‘Database Id: ‘+Convert(Varchar(4),@dbid)
Print ‘Creation date: ‘+Convert(varchar(100),@crdate,120)
Print ‘Days since creation: ‘+@years+’ years, ‘+@months+’ months, ‘+@days+’ days and ‘+@hours+’ hours.’
Print ‘Directory of primary file: ‘+@filename
Print ”

declare @RecoveryModel varchar(max)
declare @Status varchar(max)
select
@RecoveryModel = recovery_model_desc ,
@Status = state_desc
from master.[sys].[databases] where database_id = @dbid

Print ‘Recovery model of database: ‘+@RecoveryModel
Print ”

Print ‘Database status: ‘+@Status
Print ”

declare @LastBackupDate datetime

SELECT

@LastBackupDate = MAX(msdb.dbo.backupset.backup_finish_date)
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = ‘D’ and msdb.dbo.backupset.database_name = @dbname
GROUP BY
msdb.dbo.backupset.database_name
ORDER BY
msdb.dbo.backupset.database_name

Print ‘Last database backup: ‘+convert(varchar(max),@LastBackupDate)
Print ”

Declare @select varchar(300)
Set @select=’select Substring(name,1,34) as “Users with permissions in DB” from ‘+@dbname+’.dbo.sysusers where uid not in (0,2,4) and name not like ‘+””+’db_%’+””+’ and name<>’+””+’INFORMATION_SCHEMA’+””
EXEC (@select)

Tip solutie

Permanent

Follow Us