Solved

How to figure out why MS SQL 2012 DB will not restore.

Posted on 2016-09-19
13
80 Views
Last Modified: 2016-11-03
Hello,

Windows SQL Server 2012 with SP3 is the target.  Windows SQL Server 2012 (SP unknown) is the source.
restoring from a .bak file which is 101GB in size.  

The DB is created in SQL Studio on the target:

2016-09-19 07:26:34.71 spid57      Starting up database 'mydatabase'.
2016-09-19 07:26:35.10 spid57      Setting database option COMPATIBILITY_LEVEL to 110 for database 'mydatabase'.
2016-09-19 07:26:35.10 spid57      Setting database option ANSI_NULL_DEFAULT to OFF for database 'mydatabase'.
2016-09-19 07:26:35.11 spid57      Setting database option ANSI_NULLS to OFF for database 'mydatabase'.
2016-09-19 07:26:35.11 spid57      Setting database option ANSI_PADDING to OFF for database 'mydatabase'.
2016-09-19 07:26:35.11 spid57      Setting database option ANSI_WARNINGS to OFF for database 'mydatabase'.
2016-09-19 07:26:35.11 spid57      Setting database option ARITHABORT to OFF for database 'mydatabase'.
2016-09-19 07:26:35.11 spid57      Setting database option AUTO_CLOSE to OFF for database 'mydatabase'.
2016-09-19 07:26:35.11 spid57      Setting database option AUTO_CREATE_STATISTICS to ON for database 'mydatabase'.
2016-09-19 07:26:35.11 spid57      Setting database option AUTO_SHRINK to OFF for database 'mydatabase'.
2016-09-19 07:26:35.11 spid57      Setting database option AUTO_UPDATE_STATISTICS to ON for database 'mydatabase'.
2016-09-19 07:26:35.11 spid57      Setting database option CURSOR_CLOSE_ON_COMMIT to OFF for database 'mydatabase'.
2016-09-19 07:26:35.11 spid57      Setting database option CURSOR_DEFAULT to GLOBAL for database 'mydatabase'.
2016-09-19 07:26:35.12 spid57      Setting database option CONCAT_NULL_YIELDS_NULL to OFF for database 'mydatabase'.
2016-09-19 07:26:35.12 spid57      Setting database option NUMERIC_ROUNDABORT to OFF for database 'mydatabase'.
2016-09-19 07:26:35.12 spid57      Setting database option QUOTED_IDENTIFIER to OFF for database 'mydatabase'.
2016-09-19 07:26:35.12 spid57      Setting database option RECURSIVE_TRIGGERS to OFF for database 'mydatabase'.
2016-09-19 07:26:35.12 spid57      Setting database option DISABLE_BROKER to ON for database 'mydatabase'.
2016-09-19 07:26:35.12 spid57      Setting database option AUTO_UPDATE_STATISTICS_ASYNC to OFF for database 'mydatabase'.
2016-09-19 07:26:35.12 spid57      Setting database option DATE_CORRELATION_OPTIMIZATION to OFF for database 'mydatabase'.
2016-09-19 07:26:35.13 spid57      Setting database option PARAMETERIZATION to SIMPLE for database 'mydatabase'.
2016-09-19 07:26:35.13 spid57      Setting database option READ_COMMITTED_SNAPSHOT to OFF for database 'mydatabase'.
2016-09-19 07:26:35.13 spid57      Setting database option READ_WRITE to ON for database 'mydatabase'.
2016-09-19 07:26:35.13 spid57      Setting database option RECOVERY to FULL for database 'mydatabase'.
2016-09-19 07:26:35.13 spid57      Setting database option MULTI_USER to ON for database 'mydatabase'.
2016-09-19 07:26:35.13 spid57      Setting database option PAGE_VERIFY to CHECKSUM for database 'mydatabase'.
2016-09-19 07:26:35.13 spid57      Setting database option target_recovery_time to 0 for database 'mydatabase'.
2016-09-19 07:27:28.83 spid55      Setting database option READ_COMMITTED_SNAPSHOT to ON for database 'mydatabase'.
2016-09-19 07:27:28.91 spid55      Starting up database 'mydatabase'.

Restore is done as an Overwrite and the logs option is unchecked.  This fails:

2016-09-19 08:12:54.63 spid54      Error: 824, Severity: 24, State: 2.
2016-09-19 08:12:54.63 spid54      SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x56d0ac06; actual: 0x45d09406). It occurred during a read of page (1:130) in database ID 5 at offset 0x00000000104000 in file 'S:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mydatabase.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

I ran dbcc checkdb:

USE mydatabase
go
dbcc checkdb with data_purity
go

First line of the .rpt file (full rpt file "dbcc-checkdb.rpt" dbcc-checkdb.rpt attached):

Msg 945, Level 14, State 2, Line 1
Database 'mydatabase' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

My questions:

-How do I track down if there are inaccessible files or insufficient memory or disk space?    The sql error log (attached as sqlrestore-sqllog.txt )does not have details.  

-Or is there some problem with the sa user?

-"2016-09-19 08:56:10.60 spid55      DBCC CHECKDB (master) WITH data_purity executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.  Internal database snapshot has split point LSN = 000000d4:00000075:0001 and first LSN = 000000d4:00000073:0001."   Does this point to a space problem instead of a data corruption or other issue?

Any step to work through this to get to the actual problem preventing this DB restore is appreciated.

Thank you.

Regards,

Technical Support
0
Comment
Question by:Technical Support
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41805231
Were you able to restore the database or not?
0
 

Author Comment

by:Technical Support
ID: 41805245
No, and that is why I am writing.  Sorry if I wasn't clear.  

The database is not restored.  Next to the database name in the Object Explorer, it says:

(Recovery Pending)

This is the second backup from this source that I have tried to restore.  Trying to restore the original DB .bak file put the database into a "Restoring" loop that could not be broken out of.  I thought it could be a damaged .bak file so I obtained a new one.  The error is now "Recovery Pending" with the new .bak file.  Otherwise I am in the same spot:  The database isn't restored and I need to find out why.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41805291
Was there any error from the restore command?
0
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 

Author Comment

by:Technical Support
ID: 41805315
The restore was done through the SQL Server GUI.

left click on 'mydatabase'. Tasks-restore-database.
pointed to the device and file
checked off "overwrite the existing database".  
checked off "relocate all files to folder" and pointed to the paths where files should go

screen snap
importing-the-db-newbakfile.jpg
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41805342
Clearly the backup file is corrupted. You should let the people who sent you the backup file know and ask them to run a DBCC CHECKDB. ALso they should run their backup with the verify option as well. This can help:

https://www.mssqltips.com/sqlservertutorial/113/checking-to-make-sure-a-sql-server-backup-is-useable/
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41805811
Try and take backup again .. and then try.. Also take full backup
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41806028
What's the version from the source database?
The target is MSSQL 2012 but which edition? If Express Edition you can't restore a 101GB file into this MSSQL instance.

Are the target drive letters and folder names equal from the source ones?
0
 

Author Comment

by:Technical Support
ID: 41806455
Thanks for reminding me of the "verifyonly".  I had tried this on the older .bak file and it said that the file was OK.

I am trying the syntax now on the new .bak file and getting an error:

RESTORE VERIFYONLY FROM DISK = E:\backup.bak
go

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'E:'.
======

it seems that the device path needs to be enclosed in single quotes:
https://msdn.microsoft.com/en-us/library/ms188902.aspx

Anyway, I let this run over night and here are the messages generated:

Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
The path specified by "S:\DATA\mydatabase.mdf" is not in a valid directory.
Directory lookup for the file "S:\DATA\mydatabase_blobs.ndf" failed with the operating system error 2(The system cannot find the file specified.).
Directory lookup for the file "S:\DATA\mydatabase_indx.ndf" failed with the operating system error 2(The system cannot find the file specified.).
Directory lookup for the file "S:\DATA\mydatabase_wcaudit.ndf" failed with the operating system error 2(The system cannot find the file specified.).
Directory lookup for the file "L:\LOGS\mydatabase_log.ldf" failed with the operating system error 2(The system cannot find the file specified.).
The backup set on file 1 is valid.
===============

I am concentrating on that last message "The backup set on file 1 is valid."
 
I would like to see if there is a way to get more info about "Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details."

If there are space issues, I will have to go back to see if more space can be added.  I just need the numbers for justification.

I don't understand the "Directory lookup for the file . . . " messages, unless they are just informational.  I recognize that path as the one presented in the Management GUI when I pointed to the .bak file on the device.  It is the paths from the source.  I am using the copy files to folder option.
0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41806475
Are the target drive letters and folder names equal from the source ones?

Basically you're restoring a backup from a different server to a new one so you need to guarantee that the directory structure is the same and that you have enough disk space to store the new database.

If you want to move the file locations then you'll need to use the WITH MOVE option:
RESTORE DATABASE databaseName 
FROM DISK = 'E:\backup.bak'
WITH MOVE 'databasename_Data' TO 'F:\Data\databasename_Data.mdf',
MOVE 'databasename_Log' TO 'G:\Log\databasename_Log.ldf'

Open in new window

NOTE: Replace the destination path with the correct ones.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41806622
You need to put the file location between quotes:

RESTORE VERIFYONLY FROM DISK = 'E:\backup.bak'
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41828098
@Author:
Did your issue being resolved? If not, tell us what still not working so we can try to help you solving the issue.
0
 

Author Comment

by:Technical Support
ID: 41866835
The only resolution we could find was to copy the database off the network, then back on.  Copying the large file between 2 Windows 2012 VMware VMs always resulted in a corrupted file that was not restorable.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question