Solved

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

Posted on 2016-09-19
13
39 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
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Were you able to restore the database or not?
0
 

Author Comment

by:Technical Support
Comment Utility
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 26

Expert Comment

by:Zberteoc
Comment Utility
Was there any error from the restore command?
0
 

Author Comment

by:Technical Support
Comment Utility
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 26

Expert Comment

by:Zberteoc
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Try and take backup again .. and then try.. Also take full backup
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
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 26

Expert Comment

by:Zberteoc
Comment Utility
You need to put the file location between quotes:

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

Expert Comment

by:Vitor Montalvão
Comment Utility
@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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now