Raport informatii despre baza de date

Configurare noua (How To)

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

as

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
Etichetare:

Voteaza

(2 din 10 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?