0

I have a script file (actually a series of them..) which when opened in Query Analyzer, and executed works perfectly.

When I execute it via the OSQL command, it fails saying it can't find a DB that I know exists...

Yet when I execute the same script with the DB in the same starting state in Q/A it works 100%..

One other thing, the line that OSQL says is failing is Line 54 the "BACKUP..."

    DECLARE @DEVICENAME VARCHAR(1000)
    DECLARE @SYSDATABASE VARCHAR(1000)
    DECLARE @backupSetId INT
    -- MD 
    DECLARE @POS INT
    DECLARE @VERSIONNUMBER INT
    -- NEed to Get The Version it somes to us as MMM.mmm.vvvv and the 1st decimal can         be in position 2 or 3.. 
    -- So we cast the Version to a varchar cast(SERVERPROPERTY('ProductVersion')as varchar(50))
    -- then we find the CHARiNDEX OF THE DECIMAL (IT'LL ALWAYS BE THERE. 
    SET @POS=CHARINDEX('.',cast(SERVERPROPERTY('ProductVersion')as varchar(50)))-1  
    Set @VERSIONNUMBER = left(cast(SERVERPROPERTY('ProductVersion')as         varchar(50)),@POS)

--if left (cast((SERVERPROPERTY('productversion')) as varchar(25)),1) ='8'
    IF @VERSIONNUMBER='8' 
    BEGIN
        SET @DEVICENAME = (SELECT TOP 1 REPLACE(phyname,'master.mdf','') + 'GenericDBName_Archive_Backup.dat' AS DEVICENAME  FROM master..sysdevices WHERE name ='master')
    END
ELSE
    BEGIN
        SET @DEVICENAME = (SELECT TOP 1 REPLACE(physical_name,'master.mdf','') + 'GenericDBName_Archive_Backup.dat' AS DEVICENAME FROM sys.master_files WHERE name ='master')
    END 

    print @devicename

    if exists(select * from master..sysdatabases where name = 'GenericDBName_Archive' and CONVERT(varchar(24), crdate, 102) <= CONVERT(varchar(24), getdate(), 102))
        BEGIN
            if not exists(select * from master..sysdatabases where name = 'GenericDBName_Archive_Backup' and CONVERT(varchar(24), crdate, 102) <= CONVERT(varchar(24), getdate(), 102))
                BEGIN
                    USE [MASTER]
                    BACKUP DATABASE [GenericDBName] TO  DISK =@DEVICENAME WITH NOFORMAT, INIT,  NAME = N'GenericDBName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
                    select @backupSetId = position from msdb..backupset where database_name=N'GenericDBName' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'GenericDBName' )

                    if @backupSetId is null 
                    BEGIN 
                        raiserror(N'Verify failed. Backup information for database ''GenericDBName'' not found.', 16, 1) 
                    END

                    RESTORE VERIFYONLY FROM  DISK = @DEVICENAME WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
                END
            else
                BEGIN
                    RAISERROR (N'The GenericDBName_Archive_Backup already exists. Is not neccesary to make backup again.', -- Message text.
                           10, -- Severity,
                           1, -- State,
                           N'number', -- First argument.
                           5); -- Second argument.
                END 

        END

    ELSE
        BEGIN
            USE [MASTER]
            BACKUP DATABASE [GenericDBName] TO  DISK =@DEVICENAME WITH NOFORMAT, INIT,  NAME = N'GenericDBName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
            select @backupSetId = position from msdb..backupset where database_name=N'GenericDBName' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'GenericDBName' )

            if @backupSetId is null 
                BEGIN 
                    raiserror(N'Verify failed. Backup information for database ''GenericDBName'' not found.', 16, 1) 
                END

            RESTORE VERIFYONLY FROM  DISK = @DEVICENAME WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
        END     

        BEGIN

            RAISERROR (N'Backup is created to restore on new archive database.', -- Message text.
                10, -- Severity,
                1, -- State,
                N'number', -- First argument.
                5); -- Second argument.
        END
Mike D
  • 159
  • 1
  • 1
  • 13
  • In line 54 you have `=@DEVICENAME`. Not sure if it shouldn't be separated to `= @DEVICENAME` – BartekR Feb 08 '12 at 18:16
  • Nope... Also I missed this from the previous run thru, but it alos returns the following: Could not find database table ID 6, name 'FakeName' (the DB really does exist too!) – Mike D Feb 08 '12 at 20:20
  • `SET @DEVICENAME = (SELECT TOP 1 REPLACE(physical_name,'master.mdf','') + 'GenericDBName_Archive_Backup.dat' AS DEVICENAME FROM sys.master_files WHERE name ='master')` - you didn't set context of database as in other statements. When you use osql you authorize with some username. What default database does it use? – BartekR Feb 09 '12 at 07:09

0 Answers0