Incremental control field - Cursor

Question

I'm trying to create a simple restore cursor that will be used when I have the need to restore multiple databases.

Each database have simple recovery model and all are named using the following format: DBname_AAAAMMDD_FULL

I have came up with a dynamic script that will do the job for a single istance, but it's not incrementing the data field on my cursor.

Here is what I have tried so far:

DECLARE @SQL VARCHAR(MAX)
DECLARE @DATE DATETIME  = '20170926'
DECLARE @DATE_CHAR CHAR(9) =  CONVERT(CHAR(9),@DATE,112)
DECLARE @DBName VARCHAR(MAX) = 'MyDB_'
DECLARE @DBFileName VARCHAR(MAX) 
SELECT @DBNAME = (@DBNAME + @DATE_CHAR)
SELECT @DBName
SELECT @DBFileName = ('' + @DBNAME+'' +'_FULL_FRM.BAK') --This should match the physical file name
SELECT @DBFILENAME

WHILE (SELECT @DATE ) <= '20170930'
BEGIN  
   PRINT @DATE
   SET @DATE = (@DATE +1)
   IF (SELECT @DATE) > '20170930'
      BREAK  
   ELSE  
      CONTINUE  
END

And this piece of code, but haven't got either to work.

DECLARE @SQL VARCHAR(MAX)
DECLARE @DATE DATETIME  = '20170926'
DECLARE @DATE2 DATETIME
DECLARE @DATE_CHAR CHAR(9) =  CONVERT(CHAR(9),@DATE,112)
DECLARE @DBName VARCHAR(MAX) = 'MYDB_'
DECLARE @DBFileName VARCHAR(MAX) 
SELECT @DBNAME = (@DBNAME + @DATE_CHAR)
--23 = aaaa-mm-dd
--112 = aaaammdd
SELECT @DBName
SELECT @DBFileName = ('' + @DBNAME+'' +'_FULL_FRM.BAK') --This should tch the physical file name
SELECT @DBFILENAME

DECLARE Employee_Cursor CURSOR FOR  
SELECT @DATE
WHERE @DATE < '20170930'
OPEN Employee_Cursor;  
FETCH NEXT FROM Employee_Cursor;  
WHILE @DATE <= '20170930'
   BEGIN  
   SELECT @DATE = @DATE+1
   PRINT @DATE
       FETCH NEXT FROM Employee_Cursor;  
    END;  
CLOSE Employee_Cursor;  
DEALLOCATE Employee_Cursor;  
GO

score 1 · Accepted Answer · answered Oct 25 '17 at 06:56

Got my query to work, without using a cursor, thanks to GarethD and his useful information provided here: While loop in SQL Server 2008 iterating through a date-range and then INSERT

What I needed was a loop that increased date by 1 day so I could get the correct names of our backups. This query may not be the best optimized, but it works.

It's still a WIP and is useless as now, but i'll finish tomorrow:

DECLARE @SQL VARCHAR(8000)
DECLARE @DATE DATETIME  = '20170926'
DECLARE @TARGET_DATE DATETIME = '20171001'
DECLARE @CURRENTDATE DATETIME = @DATE
DECLARE @DATE_CHAR CHAR(9)
DECLARE @DBName VARCHAR(255) = 'MyDB_'
DECLARE @DBFileName VARCHAR(255) 
DECLARE @DB_PATH VARCAR(8000)

SET @CurrentDate = @Date


WHILE ( @DATE < @TARGET_DATE)
BEGIN  
        BEGIN
            SET @DBName = 'MyDB_'
            SET @DATE = (@DATE +1)
            SET @DATE_CHAR =   CONVERT(CHAR(9),@DATE,112)
            SELECT @DBNAME = (@DBNAME + @DATE_CHAR)
            SELECT @DBFileName = ( @DBNAME +'_FULL_FRM.BAK') --This should match the physical file name
            SELECT @SQL = 'USE ''[master]'' RESTORE DATABASE ' + @DBNAME + 'FROM DISK ''D:\BKP_PATH\' + @DBFileName + 'WITH FILE =1 '
        END

SELECT @SQL
END 
GO

Here what I've got so far: results

Incremental control field - Cursor

1 Answers1