What are the challenges you face when working across database platforms? Take the survey
Options

Restoration of SQL Server 2000 DB to new SQL Server 2008 R2

tcarlisletcarlisle Posts: 15
edited February 14, 2011 8:56PM in SQL Backup Previous Versions
I am attempting to restore a Redgate SQL Backup from a SQL Server 2000 instance to its repalcement, a SQL Server 2008 R2 instance. On browsing to a backup file and selecting it as the restoration source, all I see for every column in the file list for the backup are "Unknown". In hovering over the red X under the OK column, the pop-up message is, "Could not retrieve information from the server about C:\Backup\13Software.sqb"

I have completed the same restoration of the same db from that server to a SQL Server 2005 instance running on Windows Server 2003, many times before without any issue. What could be the problem now? I know Windows 2008 is real picky about file permissions, perhaps it could be that. Any thoughts?

TeeCee

Comments

  • Options
    peteypetey Posts: 2,358 New member
    Could you please check if the SQL Backup Agent service startup accounts have rights to access the folder on the remote server where the backup file is stored? It looks like the SQL Server service startup account has the necessary rights, since you were able to browse to that folder, but perhaps not the SQL Backup Agent service startup account.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    I checked that, and on first look it was set to .\administrator (Local Admin). I tried three or four different accounts, Domain Admins, Network Service, Local Admin, etc. I checked the SQL 2005 instance SQL Backup Service account, and it is set to Local Admin. I am going to try tweaking some more.
    The backup file has actually been copied from another server location to a directory on the local machine.
  • Options
    peteypetey Posts: 2,358 New member
    Just to clarify, is 'C:\Backup\13Software.sqb' located on the machine that SQL Server is running on?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    peteypetey Posts: 2,358 New member
    Does the following complete successfully when ran against the SQL Server instance that you want to restore the database to?
    EXEC master..sqlbackup '-sql "RESTORE FILELISTONLY FROM DISK = ''C:\Backup\13Software.sqb'' " '
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    I am running that script now, 00:03:13 and counting...
  • Options
    The script result was as follows:

    Msg 1, Level 15, State 1, Line 0
    Reading filelist of "C:\Backup\13Software.sqb"

    Warning 485: File does not exist: C:\Backup\13Software.sqb
    Validating file (C:\Backup\13Software.sqb): attempt 1
    Warning 485: File does not exist: C:\Backup\13Software.sqb
    Validating file (C:\Backup\13Software.sqb): attempt 2
    Warning 485: File does not exist: C:\Backup\13Software.sqb
    Validating file (C:\Backup\13Software.sqb): attempt 3
    Warning 485: File does not exist: C:\Backup\13Software.sqb
    Validating file (C:\Backup\13Software.sqb): attempt 4
    Warning 485: File does not exist: C:\Backup\13Software.sqb
    Validating file (C:\Backup\13Software.sqb): attempt 5
    Warning 485: File does not exist: C:\Backup\13Software.sqb
    Validating file (C:\Backup\13Software.sqb): attempt 6
    Warning 485: File does not exist: C:\Backup\13Software.sqb
    Validating file (C:\Backup\13Software.sqb): attempt 7
    Warning 485: File does not exist: C:\Backup\13Software.sqb
    Validating file (C:\Backup\13Software.sqb): attempt 8
    Warning 485: File does not exist: C:\Backup\13Software.sqb
    Validating file (C:\Backup\13Software.sqb): attempt 9
    Warning 485: File does not exist: C:\Backup\13Software.sqb
    Validating file (C:\Backup\13Software.sqb): attempt 10
    Warning 485: File does not exist: C:\Backup\13Software.sqb
    File validation failed: C:\Backup\13Software.sqb

    SQL Backup exit code: 840
  • Options
    Excuse me, those were the Messages that were returned, the tabular result returned no records.
  • Options
    FYI,

    I ran a backup on the 13Software DB on my failover server (SQL Server 2005) using the built-in SQL Server backup utility. I then copied that to the new SQL 2008 R2 server and was able to restore that backup using the SQL Server 2008 R2 backup restore utility sucessfully.

    That indicates to me, that there is something going on under the hood with the RedGate SQL Backup versions. I don't know what that might be. I really need this to work, because I will have to make RedGate backup files available to our case management software vendor that uses RedGate Backup for restoration to the 13Network Data online databases (country-wide) and in turn they provide Bankruptcy data to the National Data Center.

    Still looking for a clue...

    Tim
  • Options
    peteypetey Posts: 2,358 New member
    If SQL Backup cannot find the file, it means that either the SQL Backup Agent service startup account does not have adequate rights to read the file, or that the file does not exist.

    I'm aware that I'm repeating things here, but please note that the "C:\Backup\13Software.sqb" file must exist on the SQL Server 2008 R2 server, and that the SQL Backup Agent service startup account has at least read rights to that folder and file.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    I will repeat too, the file does exist in that directory, and the Local System Account has Full access to that directory and file.

    I am trying some other things with permissions to try to get this issue resolved. I will let you know how that goes.

    Thanks.
  • Options
    Everything permissions related that I have tried has not resolved the issue. What Next?

    Tim
  • Options
    Here is the latest on this thread. After getting the new database restored using a backup generated with the SQL Server backup utility (ugh!) on the old SQL Server 2000 data server, I was able to restore that backup on the new SQL Server 2008 R2 instance.

    I then created a backup on the new server using RedGate backup rev. 6.4. That ran sucessfully. I then used that backup to run a restoration using Redgate backup aftert dropping the database and then recreating its DB shell. That restoration was sucessful.

    I do not doubt that there may be permissions issues on the files created on the Windows 2003 server location of the Redgate rev 3.4 backup file. I have seen unexplanable permissions issues with files in a mixed 2003 - 2008 Windows server environment previously, and this might just be another one of those. I have a work around, although it is not what I wanted to do in the original project plan. My real concern was that i would not be able to produce the Redgate backup files that will be used to spread the info into the cloud. I have teste that and it will be possible.

    Thank you, Peter, for your input.

    Tim Carlisle
    Systems Manager
    Office of the Chapter 13 Trustee
    Columbus, GA, USA
  • Options
    peteypetey Posts: 2,358 New member
    Thanks for the update.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.