MCITP

MCITP

Thursday, September 27, 2012

How to find backup status of databases which are online

We need to find the backup status of all database which are currently in online state . This script with not include database that is offline or in recovery state.

DECLARE @Baksql VARCHAR(8000) DECLARE @BackupFolder VARCHAR(100) DECLARE @BackupFile VARCHAR(100) DECLARE @BAK_PATH VARCHAR(4000) DEclare @BackupDate varchar(100)  
-- Setting value of  backup date and folder of the backup
SET @BackupDate =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') -- 20110517_182551 SET @BackupFolder = 'D:\DBBackups\' SET @Baksql = ''  
-- Declaring cursor
DECLARE c_bakup CURSOR FAST_FORWARD READ_ONLY FOR   SELECT NAME FROM SYS.DATABASES  WHERE state_desc = 'ONLINE'
-- Consider databases which are online
AND database_id > 4
-- Exluding system databases    
-- Opening and fetching next values from sursor
OPEN c_bakup  FETCH NEXT FROM c_bakup INTO @BackupFile    WHILE @@FETCH_STATUS = 0 BEGIN   SET @BAK_PATH = @BackupFolder + @BackupFile  
-- Creating dynamic script for every databases backup
SET @Baksql = 'BACKUP DATABASE ['+@BackupFile+'] TO DISK = '''+@BAK_PATH+'_FullBackup_'+@BackupDate+'.bak'' WITH INIT;'  
-- Executing dynamic query
PRINT (@Baksql) EXEC(@Baksql)  
-- Opening and fetching next values from sursor
FETCH NEXT FROM c_bakup INTO @BackupFile  END  
-- Closing and Deallocating cursor
CLOSE c_bakup DEALLOCATE c_bakup

No comments:

Post a Comment

Followers