June 20, 2013 at 4:28 am
Hi there,
I have a database called - Oneserve_Stage, which for some reason is showing as (Restoring...) next to the database.
How can I get this back to a normal state.
When I run the following TSQL -
USE master
RESTORE DATABASE [Oneserve_Stage] FROM DISK = N'\\mhsvi-mgmt03\oneserve_DW\ExportFromOneserve\MHS_DataCut_PROD.BAK' WITH FILE = 1, RESTART, RECOVERY
I get the following errors -
Msg 3183, Level 16, State 2, Line 2
RESTORE detected an error on page (0:0) in database "Oneserve_Stage" as read from the backup set.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
I have no idea why this database is now showing like this? Any help will be appreciated.
Thanks
June 20, 2013 at 4:58 am
Either the backup is damaged or the network returned incorrect data for the backup.
Try getting a fresh backup, copying it local and restoring again
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2013 at 5:28 am
That didn't work either.
What I have now done is drop the database and deleted the mdf and log file.
I have now recreated an empty database with the same database name - "Oneserve_Stage".
Now when I try and restore with a .BAK (it does have a different LogicalFile name) but I do point it to write over the exsisting mdf and ldf file.
It starts the restore - gets to about 01% and then throws up this error message - and the database goes back to being in a useless state of (Restoring.....)
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore of database 'Oneserve_Stage' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: RESTORE detected an error on page (0:0) in database "Oneserve_Stage" as read from the backup set. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1325+)&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
June 20, 2013 at 5:35 am
Do you have another backup file?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
June 20, 2013 at 5:40 am
First, did you do as Gail asked and copied the backup file locally to take out network issues. They can be problematic.
If so, then you have a corrupt backup. No good way to repair. As Abu noted, you will need another backup file.
June 20, 2013 at 5:48 am
OK, This is what I have done now.
I have dropped the database again as the database went into a (Restoring......) state. So I have a brand new empty database called Oneserve_Stage.
I have taken a fresh backup of a blank database. Set to Overwrite all exsisting backup sets.
So I now have a backup called "Oneserve_Stage.bak" I will use this to now do a restore. Did the restore successfully.
I will now try and restore the other .BAK file which originates from another system sent to us - which I want to restore into this empty database.
So I right click the database - Restore - Select Device - This defaults to Database "EXTDataCut" On the Destination I change the database to Oneserve_Stage.
On the files I make sure that I point the Restore As to -
E:\Data\MSSQL11.DATAWAREHOUSE\MSSQL\DATA\Oneserve_Stage.mdf
L:\Logs\Oneserve_Stage_log.ldf
(The above being the data and log files to the empty database)
On Options I select Overwrite the exsiting database with REPLACE.
Recovery State is Restore With Recovery.
I kick it off - It gets to 10% and fails again with -
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore of database 'Oneserve_Stage' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: RESTORE detected an error on page (0:0) in database "Oneserve_Stage" as read from the backup set. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1325+)&LinkId=20476
June 20, 2013 at 6:02 am
OK guys. Thanks for your help it must be the.BAK file.
Which is odd as it has been working fine all day....could it become corrupt by just moving it...as I move it via a batch file from an SFTP site to the SQL Sever where I then restore it.
Really odd.
June 20, 2013 at 6:06 am
The larger the file, the more changes that something breaks when it's transferred. I've had issues with downloads from MSDN that won't work, but when I re-download, things are fine.
I'd re-try the SFTP. However when you say it works, do you mean that you've tried to restore this file before the SFTP transfer and that worked?
June 20, 2013 at 6:19 am
I was restoring it when the .BAK was still on the SFTP. I would then move it via a batch file to the SQL Server.
As this will be a daily process of a Datawarehouse database being SFTPed to us I want to restore the database daily (it's a temp staging database) As it's daily I also want to delete the previous night's .bak.
So the process is -
Restore DB from .BAK direct on SFTP
Delete the previous nights .bak on the SQLSERVER
Move the .BAK from the SFTP to the SQL Server.
I was testing tiss all morning and it was working then all of a sudden it started playing up. I'm guessing where I have moved the .BAK multiple times back and firth it may have had some damage?
I'm going to review the order of my SSIS anyway as I think I will move the .BAK then restore it from the SQL Server.
Again thanks for you help.
June 20, 2013 at 6:27 am
You don't need to create a database before restoring a backup. Just do the restore don't waste time creating a DB just to over write it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2013 at 6:47 am
+1 to what Gail said.
It's possible it corrupted during a copy, but that doesn't mean your copies are always going to do this.
June 20, 2013 at 7:23 am
Thanks all for your comments.
I am only restoring the database. I was only creating the database whilst I was trying to get to the bottom of why the restore was failing.
Thanks
May 10, 2018 at 7:38 pm
Because this post comes up in most searches for error 3013, I'd like to add that 3013 can also occur if you don't have enough drive space available to write the data and/or log files.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply