SQL Server Database Backup Getting Skipped

Why some of the Microsoft SQL Server Databases are skipped while performing a backup using T-SQL

I created one stored procedure to take the backups of all my databases. I executed the same through SQL job and it was executing. Since total databases size is around 300 GB, I just left this running and was monitoring the status of the job. Once the job got executed successfully I thought my backups are completed. I went to the location where I took the backup and I found backups are not available for 3 of my databases. Strange!!!!.

 

I looked into my stored procedure again and again and could not find anything wrong there. Just I was asking SQL server to loop through my all user databases through CURSOR and back up all of them to a network location.

Here is the Stored Procedure script that I used.

 

CREATE PROCEDURE [dbo].[DBA_DBs_Copy_Only_Backup]

      -- Add the parameters for the stored procedure here

AS

BEGIN

      SET NOCOUNT ON;

      DECLARE @name VARCHAR(50) -- database name 

      DECLARE @path VARCHAR(256) -- path for backup files 
      DECLARE @fileName VARCHAR(256) -- filename for backup 
      DECLARE @fileDate VARCHAR(20) -- used for file name

      -- specify database backup directory
      SET @path = 'Specify your local/network path here' 

       -- specify filename format

      SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

      DECLARE db_cursor CURSOR FOR 

      SELECT name
      FROM master.dbo.sysdatabases
      WHERE name NOT IN ('tempdb')  -- exclude these databases

      OPEN db_cursor  

      FETCH NEXT FROM db_cursor INTO @name  

      WHILE @@FETCH_STATUS = 0  

      BEGIN  
            SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

            BACKUP DATABASE @name TO DISK = @fileName 

            WITH COPY_ONLY, SKIP, NOINIT
            FETCH NEXT FROM db_cursor INTO @name  

      END  

      CLOSE db_cursor  

      DEALLOCATE db_cursor

END

 

Finally I found there is problem with the cursor. To resolve this issue I made the CURSOR STATIC in my code and after that no databases were getting skipped from backup. This is what I did to make the CURSOR static.

 

DECLARE db_cursor CURSOR STATIC FOR

 

Remember by default cursor is dynamic and it may change the value dynamically when your database state changes to any state. By making cursor STATIC we make sure that the data retrieved from the table gets stored in tempdb as reference and it never change till the completion of your script.