0

Using SQL Server 2012. I need to loop through my table without using a cursor or a temp table. Also, I know the debate re: cursors... :) AND I have script at the bottom of this question using sys.databases and msdb.dbo.restorehistory getting the last restored, but this doesn't fix my immediate need for this specific table.

Scenario: I have a table that I created for restore tests:

CREATE Table RestoreTests (
RestoreTestID int Not NULL, 
ServerName varchar (255) Not NULL, 
DatabaseName varchar (255) Not NULL, 
RestoreDate datetime Not NULL,
BackupFile varchar (1000) Not NULL)

I inserted 8 records with:

  • 2 servers - 4 records/server,
  • .bak and .trn for each DatabaseName, and
  • dates < 30 days and dates > 30 days - 4 records each.

I want to identify all records on my table that are > 30 days. 

SELECT * FROM RestoreTests WHERE RestoreDate DATEDIFF(Day,RestoreDate, GETDATE()) > 30

My syntax:

declare @RestoreTestID int
declare @ServerName varchar(255)
declare @DatabaseName varchar(255)
declare @RestoreDate datetime
declare @BackupFile varchar(1000)
declare @id int

set @id = 0

select top 1 @RestoreTestID = RestoreTestID,
@ServerName = ServerName,
@DatabaseName = DatabaseName,
@RestoreDate = RestoreDate,
@BackupFile = BackupFile
from dbo.RestoreTests
where RestoreTestID > @id and DATEDIFF(DAY,RestoreDate, GETDATE()) > 30
order by RestoreTestID asc

while @@ROWCOUNT > 0
begin

    print 'loop RestoreTestID=' + cast(@RestoreTestID as varchar(255)) +
    ', ServerName=' + @ServerName +
    ', DatabaseName' + @DatabaseName +
    ', RestoreDate' + CAST(@RestoreDate as varchar(255)) +
    ', BackupFile' + @BackupFile

    set @id = @RestoreTestID

    select top 1 @RestoreTestID = RestoreTestID
    from dbo.RestoreTests
    where RestoreTestID > @id and DATEDIFF(DAY,RestoreDate, GETDATE()) > 30
    order by RestoreTestID

end

I'm getting error message: Msg 137, Level 15, State 1, Line 1 Must declare the scalar variable "@RestoreTestID". Msg 137, Level 15, State 2, Line 13 Must declare the scalar variable "@RestoreTestID". Msg 137, Level 15, State 2, Line 19 Must declare the scalar variable "@RestoreTestID". Msg 137, Level 15, State 1, Line 21 Must declare the scalar variable "@RestoreTestID".

When I was dinking around earlier I did get a result set with all eight records and not the 4 that are over 30 days. Any assistance offered is greatly appreciated!!

Last Restore Syntax:
WITH LastRestores AS
(
SELECT
    DatabaseName = [d].[name] ,
    [d].[create_date] ,
    [d].[compatibility_level] ,
    [d].[collation_name] ,
    r.*,
    RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT * 
FROM [LastRestores]
WHERE [RowNum] = 1


select * from [dbo].[RestoreTests]
Mintz
  • 1
  • 3

1 Answers1

0

Ok, I figured out what I needed. Here's the syntax I arrived at:

declare @RestoreTestID int
declare @id int
declare @ServerName varchar(255);
declare @DatabaseName varchar (255);
declare @RestoreDate datetime;
declare @BackupFile varchar (1000);

set @id = 0

select top 1 
    @RestoreTestID = RestoreTestID,
    @ServerName = ServerName,
    @DatabaseName = DatabaseName,
    @RestoreDate = RestoreDate,
    @BackupFile = BackupFile
from dbo.RestoreTests
where RestoreTestID > @id and RestoreDate <= DATEADD(day,-31,getdate())
order by RestoreTestID

while @@ROWCOUNT = 1
begin

    print 'RestoreTestID = ' + cast(@RestoreTestID as varchar(255)) +
    ', ServerName = ' + @ServerName +
    ', Databasename = ' + @DatabaseName +
    ', RestoreDate = ' + cast(@RestoreDate as varchar (255)) +
    ', BackupFile = ' + @BackupFile

    set @id = @RestoreTestID

    select top 1 @RestoreTestID = RestoreTestID 
    from dbo.RestoreTests
    where RestoreTestID > @id and RestoreDate <= DATEADD(day,-31,getdate())
    order by RestoreTestID 

end
Mintz
  • 1
  • 3