none
sql server express 2005 9.0.2047 - problem RESTORE VERIFYONLY FROM DISK

    Question

  • Hi all

    every time I try to verify the backup file I get the error:

    backup set on file '1' is not valid (translated from italian).

    this is my backup script:

    BACKUP DATABASE [ahr_sistema] TO DISK = N'C:\Programmi\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ahr_sistema\ahr_sistema_backup_1.bak' WITH FORMAT, INIT, NAME = N'ahr_sistema-Completo Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    declare @backupSetId as int

    select @backupSetId = position from msdb..backupset where database_name=N'ahr_sistema' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'ahr_sistema' )

    if @backupSetId is null begin raiserror(N'Verifica non riuscita. Impossibile trovare le informazioni di backup per il database ''ahr_sistema''.', 16, 1) end

    RESTORE VERIFYONLY FROM DISK = N'C:\Programmi\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ahr_sistema\ahr_sistema_backup_1.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

    GO

    Monday, March 05, 2007 10:23 AM

Answers

  • The following script works to perfection on SQL Server 2008.  Let us know if helpful.

    use msdb;
    declare @backupSetId as int
    
    select @backupSetId = position from msdb..backupset where database_name=N'AdventureWorks' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'AdventureWorks' )
    
    if @backupSetId is null begin raiserror(N'No backup history on ''AdventureWorks''.', 16, 1) end
    
    RESTORE VERIFYONLY FROM DISK = N'K:\data\backup\AW.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
    -- The backup set on file 2 is valid.
    
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 26, 2010 11:41 AM
    Moderator

All replies

  • I'm going to move this to the T-SQL forum. This isn't Express specific and the T-SQL gurus will have a better answer for this.

    Mike

    Tuesday, March 06, 2007 7:19 PM
  • Anyone has an idea about this one? I'm having the same problem.

    thks

     

    Wednesday, January 23, 2008 3:04 PM
  • Anyone has an idea about this one? I'm having the same problem.

    thks

     


    Hi, about the restore verifyonly problem, did you fond the solution?

    Thankyou

    Paolo

    Thursday, October 21, 2010 8:30 AM
  • The following script works to perfection on SQL Server 2008.  Let us know if helpful.

    use msdb;
    declare @backupSetId as int
    
    select @backupSetId = position from msdb..backupset where database_name=N'AdventureWorks' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'AdventureWorks' )
    
    if @backupSetId is null begin raiserror(N'No backup history on ''AdventureWorks''.', 16, 1) end
    
    RESTORE VERIFYONLY FROM DISK = N'K:\data\backup\AW.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
    -- The backup set on file 2 is valid.
    
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, October 26, 2010 11:41 AM
    Moderator