December 2, 2008 at 8:37 am
Currently, I have a database backup that can be restored in our staging environment. But once we move it from the hosted environment to internal offices the backup then fails. It is a vital part of our development strategy to restore the database so our developers can fix issues our clients see. We seem to be getting a series of errors when restoring:
Error: 3633, Severity: 16, State: 1.
The operating system returned the error '87(The parameter is incorrect.)' while attempting 'SetFilePointer' on '(null)' at 'storage.cpp'(951).
Error: 3634, Severity: 16, State: 1.
The operating system returned the error '87(The parameter is incorrect.)' while attempting 'SetFilePointer' on '(null)'.
Error: 18210, Severity: 16, State: 1.
BackupIoRequest::WaitForIoCompletion: write failure on backup device ''. Operating system error 6(The handle is invalid.).
I have researched the errors and have moved the backup jobs to not occur when our log backups are going on; however, the way in which the error does not occur when not zipped and pulled over the line is pointing to something that is not a SQL Server problem. I have run dbcc checkdb and it found nothing and I have verified the backup.
The production machine is SQL Server 2005 SP2 32-bit clustered enterprise edition.
December 2, 2008 at 8:48 am
December 2, 2008 at 9:20 am
Backup is occurring from a job.
BACKUP DATABASE [' + @dbname + '] TO DISK = N''' + @backuppath + '\' + @@SERVERNAME + '_' + @timestamp + '_' + @dbname + '.BAK'' WITH RETAINDAYS = 1, NOFORMAT, INIT, NAME = N''' + @@SERVERNAME + '_' + @timestamp + '_' + @dbname + ''', SKIP, REWIND, NOUNLOAD, STATS = 10
To restore I am simply using the GUI and creating a new database at time of restore. It has also been tried with scripts and to overwrite an existing database. All attempts end in errors.
December 2, 2008 at 10:05 am
Some additional information to give a better picture. The database is 16 gigs in size. This is the only database suffering from the issues all other backups on this server work in other environments. We are zipping the files and are about to test to see if we pull an unzipped file whether that might be involved.
December 2, 2008 at 10:47 am
Is the backup failing or the restore? If the restore, can we see your restore script?
December 2, 2008 at 4:19 pm
I am basically restoring through the gui but here is a script of whats happening:
RESTORE DATABASE [MYDB_2] FROM DISK = N'C:\MYDB_PROD_DB_BACKUP.BAK' WITH FILE = 1, MOVE N'MYDB_DATA' TO N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MYDB_2.mdf', MOVE N'BuySite_EBO_Log' TO N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MYDB_2_1.ldf', MOVE N'sysft_ftMYDB' TO N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MYDB_2_2.sysft_ftMYDB', NOUNLOAD, STATS = 10
GO
I only get the errors on restore.
December 2, 2008 at 5:03 pm
Try this:
Change the filename of N'sysft_ftMYDB' :
MOVE N'sysft_ftMYDB' TO N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MYDB_2_sysft_ftMYDB.mdf'
Give extension to the physical filename(mdf or ldf) based on type of file.
HTH,
MJ
December 2, 2008 at 7:23 pm
The sys_ft file is a full text catalog. Changing that to an mdf file will break this further
Conan
December 2, 2008 at 10:03 pm
Okay, you said the restore worked the first time, but failed after that? If so, I know why. Here is the SQL for the restore that should work.
RESTORE DATABASE [MYDB_2]
FROM DISK = N'C:\MYDB_PROD_DB_BACKUP.BAK'
WITH FILE = 1,
MOVE N'MYDB_DATA' TO N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MYDB_2.mdf',
MOVE N'BuySite_EBO_Log' TO N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MYDB_2_1.ldf',
MOVE N'sysft_ftMYDB' TO N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MYDB_2_2.sysft_ftMYDB',
NOUNLOAD,
REPLACE, -- You need this if the database already exists
STATS = 10
GO
December 3, 2008 at 5:38 am
No. It works on a different server. I am not replacing the database. I am restoring to a new one and I have tried replacing it as well and it still fails.
December 3, 2008 at 8:01 am
The next piece of information I have is the one server we are able to do the restore on is Service Pack 1. I am currently going to find a test machine to set up so we can do the upgrade and identify if this is the begining of narrowing the issue.
December 3, 2008 at 10:11 am
The new piece of information to add is:
Production server is SP2
Development servers are SP2(Restores will not work here)
Staging server is SP1(Restore will work here)
Testing a development server at SP1 to confirm issue.
December 3, 2008 at 1:01 pm
The Service Pack is not an issue any longer.
We have found that the restore is working on different Servers at different service pack levels both pre and post SP2 and that the deveopers me included are also at a scattering of sp levels. (That will be changing to a standardized level.) The difference I am homing in on now is that all the Servers are just that Windows servers. All the Developers are using Developers Edition SQL Server on XP pro. Any advice?
December 3, 2008 at 1:18 pm
Should not be an issue either. I would start looking a permissions, on the directories you are attempting to restore the databases, as well as that of the user attempting the restore. Also look at disk space, see if maybe that is causing an issue. I'd even look to see if the mdf/ldf files exist even if the database is not showing in the Object Explorer in SSMS.
December 3, 2008 at 2:16 pm
We are going in as full Admins of the disk and SQL Server. I did catch SQL not fully showing the files once but that has been the rare exception and gave a completely separate error.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply