June 20, 2012 at 10:07 pm
I am using Redgate sql backup6.
I created a SQL job (redgate sql script) to restore a database which has 8 datafiles.the backup is 8 striped file.Once I ran the job I got the below error on Reggate restore history
Process terminated unexpectedly. Error code: -2139684860
6/20/2012 9:58:50 PM:
6/20/2012 9:58:50 PM: Thread 9 error:
Process terminated unexpectedly. Error code: -2139684860
6/20/2012 9:58:50 PM:
6/20/2012 9:58:52 PM: SQL error 3013: SQL error 3013: RESTORE DATABASE is terminating abnormally.
6/20/2012 9:58:52 PM: SQL error 3202: SQL error 3202: Write on "???" failed: 1117(The request could not be performed because of an I/O device error.)
Pls help me on this
June 20, 2012 at 10:52 pm
Can you post the script?
Are you restoring to the same server or a different server? Are all of the file paths available and not already in use for the restore? Are all of the backup files present?
Can you run a verify only check of the backup file? I know Redgate backup supports it, but I don't know the command for it. I know you can do it with the Redgate GUI.
June 21, 2012 at 1:34 am
"RESTORE DATABASE [XRMWH20] FROM
DISK = 'J:\replication\FULL_SQL4_XRMWH20_20120614_151504_1.sqb',
DISK = 'J:\replication\FULL_SQL4_XRMWH20_20120614_151504_2.sqb',
DISK = 'J:\replication\FULL_SQL4_XRMWH20_20120614_151504_3.sqb',
DISK = 'H:\replication\FULL_SQL4_XRMWH20_20120614_151504_4.sqb',
DISK = 'H:\replication\FULL_SQL4_XRMWH20_20120614_151504_5.sqb',
DISK = 'H:\replication\FULL_SQL4_XRMWH20_20120614_151504_6.sqb',
DISK = 'H:\replication\FULL_SQL4_XRMWH20_20120614_151504_7.sqb',
DISK = 'H:\replication\FULL_SQL4_XRMWH20_20120614_151504_8.sqb',
DISK = 'H:\replication\FULL_SQL4_XRMWH20_20120614_151504_9.sqb',
DISK = 'H:\replication\FULL_SQL4_XRMWH20_20120614_151504_10.sqb' WITH NORECOVERY,
MOVE 'XRMWH20_Data' TO 'H:\MSSQL10_50.SQL2\MSSQL\DATA\XRMWH20_Data.mdf',
MOVE 'XRMWH20_DataPrimary2' TO 'J:\MSSQL10_50.SQL2\MSSQL\DATA\XRMWH20_DataPrimary2.ndf',
MOVE 'XRMWH20_Log4' TO 'I:\MSSQL10_50.SQL2\MSSQL\DATA\XRMWH20_Log4.ldf',
MOVE 'XRMWH20_JobData' TO 'J:\MSSQL10_50.SQL2\MSSQL\Data\XRMWH20_JobData.ndf',
MOVE 'XRMWH20_JobData2' TO 'J:\MSSQL10_50.SQL2\MSSQL\Data\XRMWH20_JobData2.ndf',
MOVE 'XRMWH20_Data2' TO 'H:\MSSQL10_50.SQL2\MSSQL\DATA\XRMWH20_Data2.ndf',
MOVE 'XRMWH20_Data2_2' TO 'J:\MSSQL10_50.SQL2\MSSQL\DATA\XRMWH20_Data2_2.ndf',
MOVE 'XRMWH20_History' TO 'J:\MSSQL10_50.SQL2\MSSQL\Data\XRMWH20_History.mdf',
MOVE 'XRMWH20_History_2' TO 'J:\MSSQL10_50.SQL2\MSSQL\Data\XRMWH20_History_2.ndf',
MOVE 'XPLT_Data' TO 'J:\MSSQL10_50.SQL2\MSSQL\Data\XRMWH20_1.ndf',
MOVE 'XRMWH20_Data3' TO 'J:\MSSQL10_50.SQL2\MSSQL\DATA\XRMWH20_Data3.ndf',
MOVE 'XRMWH20_Data3_2' TO 'J:\MSSQL10_50.SQL2\MSSQL\DATA\XRMWH20_Data3_2.ndf',
MOVE 'XRMWH20_Log' TO 'I:\MSSQL10_50.SQL2\MSSQL\DATA\XRMWH20_Log.ldf',
MOVE 'XRMWH20_Log2' TO 'I:\MSSQL10_50.SQL2\MSSQL\DATA\XRMWH20_Log2.ldf',
MOVE 'XRMWH20_Log3' TO 'I:\MSSQL10_50.SQL2\MSSQL\DATA\XRMWH20_Log3.ldf',
MOVE 'XRMWH20_log5' TO 'I:\MSSQL10_50.SQL2\MSSQL\DATA\XRMWH20_Log5.ldf',
REPLACE, PASSWORD = 'XXXXXXXXXX' "
All paths and files are available
June 21, 2012 at 2:03 am
This is trying to perform a native SQL restore. Is it a native SQL backup? Can Redgate Backups be read natively by SQL Server?
You're reading from and writing to the same disks. I would expect there to be performance problems with the restore as a result. Possibly you overloaded the disk subsystem and it caused a failure. I would move the backup files to dedicated drives.
Did you run the verify only process to check if the backup file was valid?
June 21, 2012 at 2:16 am
These drives are clustered drives.I am not sure SQL native can do the verify only option.
June 21, 2012 at 2:39 am
If these are native SQL backups, you can do a verify only like this:
RESTORE VErifyONLY FROM DISK = 'J:\replication\FULL_SQL4_XRMWH20_20120614_151504_1.sqb',
DISK = 'J:\replication\FULL_SQL4_XRMWH20_20120614_151504_2.sqb',
DISK = 'J:\replication\FULL_SQL4_XRMWH20_20120614_151504_3.sqb',
DISK = 'H:\replication\FULL_SQL4_XRMWH20_20120614_151504_4.sqb',
DISK = 'H:\replication\FULL_SQL4_XRMWH20_20120614_151504_5.sqb',
DISK = 'H:\replication\FULL_SQL4_XRMWH20_20120614_151504_6.sqb',
DISK = 'H:\replication\FULL_SQL4_XRMWH20_20120614_151504_7.sqb',
DISK = 'H:\replication\FULL_SQL4_XRMWH20_20120614_151504_8.sqb',
DISK = 'H:\replication\FULL_SQL4_XRMWH20_20120614_151504_9.sqb',
DISK = 'H:\replication\FULL_SQL4_XRMWH20_20120614_151504_10.sqb'
Otherwise, Redgate backup has verifyonly functionality in it. Use the Redgate GUI if you need to.
June 21, 2012 at 3:25 am
As Robert says, it would probably be a good idea to build the restore script using the SQL Backup GUI. The last step of the wizard will give you the correct syntax you can use in the SQL Agent job.
There is also a scheduled restore wizard in SQL Backup 7 that you can use to create scheduled restores triggered by the SQL Agent.
June 21, 2012 at 3:28 am
verify Only is not working for sql native since it is redgate backup.Not able to see verify option in redgate tool
June 21, 2012 at 3:46 am
You can't verify from the GUI, but you can script it. e.g.
USE master
GO
EXECUTE master..sqlbackup '-SQL "RESTORE VERIFYONLY DISK = "J:\replication\FULL_SQL4_XRMWH20_20120614_151504_1.sqb",
DISK = "J:\replication\FULL_SQL4_XRMWH20_20120614_151504_2.sqb",
DISK = "J:\replication\FULL_SQL4_XRMWH20_20120614_151504_3.sqb",
DISK = "H:\replication\FULL_SQL4_XRMWH20_20120614_151504_4.sqb",
DISK = "H:\replication\FULL_SQL4_XRMWH20_20120614_151504_5.sqb",
DISK = "H:\replication\FULL_SQL4_XRMWH20_20120614_151504_6.sqb",
DISK = "H:\replication\FULL_SQL4_XRMWH20_20120614_151504_7.sqb",
DISK = "H:\replication\FULL_SQL4_XRMWH20_20120614_151504_8.sqb",
DISK = "H:\replication\FULL_SQL4_XRMWH20_20120614_151504_9.sqb",
DISK = "H:\replication\FULL_SQL4_XRMWH20_20120614_151504_10.sqb" "'
GO
The full list of available keywords (from the help):
RESTORE VERIFYONLY
[ FROM { DISK } = { 'physical_backup_device_name' } ] [ ,...n ]
[ WITH
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] MAILTO = { 'recipients' } ]
[ [ , ] MAILTO_NOLOG ]
[ [ , ] MAILTO_ONERROR = { 'recipients' } ]
[ [ , ] MAILTO_ONERRORONLY = { 'recipients' } ]
[ [ , ] MAXTRANSFERSIZE = { 65536 | 131072 | ... | 1048576 } ]
[ [ , ] PASSWORD = { 'password' } ]
[ [ , ] SINGLERESULTSET ]
]
June 21, 2012 at 7:29 am
Version 7 of SQL Backup does have VERIFY ONLY as part of the GUI, just so you know.
But, as was already pointed out, you can script the whole thing and the commands are listed right in the help file under RESTORE.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply