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

tcarlisle · February 8, 2011 5:28PM

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

petey · February 8, 2011 9:32PM

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.

tcarlisle · February 9, 2011 10:53AM

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.

petey · February 9, 2011 11:16AM

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

Thanks.

tcarlisle · February 9, 2011 11:39AM

Yes.

petey · February 9, 2011 11:52AM

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'' " '

tcarlisle · February 9, 2011 12:13PM

I am running that script now, 00:03:13 and counting...

tcarlisle · February 9, 2011 12:17PM

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 9, 2011 12:20PM

Excuse me, those were the Messages that were returned, the tabular result returned no records.

tcarlisle · February 9, 2011 1:33PM

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

petey · February 9, 2011 9:39PM

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.

tcarlisle · February 10, 2011 9:13AM

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 11:03AM

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

Tim

tcarlisle · February 14, 2011 2:46PM

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

petey · February 14, 2011 8:56PM

Thanks for the update.

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

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk