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

• 
  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.

  petey February 08, 2011 21:32

  - 0 +
• 
  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.

  tcarlisle February 09, 2011 10:53

  - 0 +
• 
  Just to clarify, is 'C:\Backup\13Software.sqb' located on the machine that SQL Server is running on?
  
  Thanks.

  petey February 09, 2011 11:16

  - 0 +
• 
  Yes.

  tcarlisle February 09, 2011 11:39

  - 0 +
• 
  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'' " '
  

  petey February 09, 2011 11:52

  - 0 +
• 
  I am running that script now, 00:03:13 and counting...

  tcarlisle February 09, 2011 12:13

  - 0 +
• 
  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

  tcarlisle February 09, 2011 12:17

  - 0 +
• 
  Excuse me, those were the Messages that were returned, the tabular result returned no records.

  tcarlisle February 09, 2011 12:20

  - 0 +
• 
  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

  tcarlisle February 09, 2011 13:33

  - 0 +
• 
  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.

  petey February 09, 2011 21:39

  - 0 +
• 
  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.

  tcarlisle February 10, 2011 09:13

  - 0 +
• 
  Everything permissions related that I have tried has not resolved the issue. What Next?
  
  Tim

  tcarlisle February 10, 2011 11:03

  - 0 +
• 
  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

  tcarlisle February 14, 2011 14:46

  - 0 +
• 
  Thanks for the update.

  petey February 14, 2011 20:56

  - 0 +

Add comment

Please sign in to leave a comment.