Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Is there a way to force a restore of a SQL database?

Posted on 2014-11-10
17
Medium Priority
?
801 Views
Last Modified: 2015-02-26
I am in the process of restoring many SQL databases (1000+).  Many of them restore with no issue, however some get stuck in the “Restoring” state.  I have read countless articles on the internet regarding this and can’t find something that works.  I figure I would share this with the EE community to see if anyone has any ideas.

Below is an example of the error message that I receive on some of these databases:

Msg 3183, Level 16, State 2, Line 2
RESTORE detected an error on page (0:0) in database "_db_200508070216" as read from the backup set.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database '_db_200508070216', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

I created the stored procedure “RestoreDatabase “to restore the SQL databases.  I have the code for this at the end of the posting.
Below I have an example of what the call to “RestoreDatabase” looks like:
EXEC [dbo].[RestoreDatabase] '_db_200508070216' , '\\Vboxsvr\x_drive\_db_200508070216.BAK'

Open in new window

Code for RestoreDatabase
GO
/****** Object:  StoredProcedure [dbo].[RestoreDatabase]    Script Date: 11/10/2014 13:07:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RestoreDatabase]
	-- Add the parameters for the stored procedure here
	@dataBaseNameVarchar as varchar(250),
	@bakPathVarchar as varchar(max)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	execute(
		'create database [' + @dataBaseNameVarchar + ']')
		
	execute('

		ALTER DATABASE [' + @dataBaseNameVarchar + ']
		SET SINGLE_USER WITH
		ROLLBACK IMMEDIATE')

	execute('
		RESTORE DATABASE [' + @dataBaseNameVarchar + ']
		FROM DISK = ''' + @bakPathVarchar + '''
		WITH REPLACE, RECOVERY')

	execute('ALTER DATABASE [' + @dataBaseNameVarchar + '] SET MULTI_USER')
	
END

Open in new window

0
Comment
Question by:rye004
[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
  • 7
  • 4
  • 4
  • +1
17 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40433798
The error I'm not sure, as there's no "page 0" in a db.  Most likely the backup was copied incorrectly somehow (?).

Is the person running those code a sysadmin in SQL?

You don't need, and don't want, to create the db first, just restore it.  

You also don't need dynamic SQL, although I don't that is causing your problems here:

RESTORE DATABASE @dataBaseNameVarchar
FROM DISK = @bakPathVarchar
WITH RECOVERY
0
 

Author Comment

by:rye004
ID: 40433874
This is being done with full administrative rights.  Other databases have been restored, so I would suspect it is how the backups were restored.

I will try the restore without creating the database first.

I am also using the dynamic SQL code to make the restore process easier for me.  I have a list of all the BAK files in an Excel sheet, I then have the Excel sheet generate my call to RestoreDatabase, which I then copy and paste into SQL manager.
0
 

Author Comment

by:rye004
ID: 40433981
Scott,

One thing to note, that the databases that I am restoring are from the same system at my clients.  They are requesting that I restore each one and look for differences between them over the last 5 years.

Unfortunately, just running the “RESTORE DATABASE” as you suggested, won’t work since they all have the same name.  

Thankfully the SQL backups were appended with a datetime stamp of when they were created.  Therefore I have been using the name of the backup file and why I am calling “CREATE DATABASE” first.  Please let me know if that does not make sense.

Below is an example of what I tried running after reading your posting:

RESTORE DATABASE DATABASE_db_200508070216
FROM DISK = '\\Vboxsvr\x_drive\DATABASE_db_200508070216.BAK'
WITH RECOVERY

Open in new window

I got the following message:

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "D:\SQL_DATA1\DATABASE.mdf" failed with the operating system error 21(The device is not ready.).
Msg 3156, Level 16, State 3, Line 1
File 'user_Data' cannot be restored to 'D:\SQL_DATA1\DATABASE.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\SQL_LOG1\DATABASE_Log.ldf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'user_Log' cannot be restored to 'E:\SQL_LOG1\DATABASE_Log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Open in new window


Also note, to protect my client I changed the real name of their database to “DATABASE”.

I am restoring these databases to a virtual environment which I have created.  On my Virtual Environment the D drive is the DVD rom.  The error indicates it is trying to write to the D drive since that is its original location back when it was on my client’s server.  I can go through and replace the D drive with an actual virtual hard disk.  Please let me know if you think that will make a difference or if there is another way to specify the location of where to write the SQL database to.

Many thanks
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40434485
The errors are because you are trying to restore files over existing ones that belongs to another database.
Use the MOVE option to set a new location for those files.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40435233
I created a function to generate the "MOVE" clauses and thus make restoring to a different db name and/or data path much easier.  If you'd like a copy of that function, just let me know.
0
 

Author Comment

by:rye004
ID: 40438649
I reran the RESTORE DATABASE command and implemented the MOVE option.  Below is what I ran.

RESTORE DATABASE DATABASE_200508070216
FROM DISK = '\\Vboxsvr\x_drive\DATABASE_200508070216.BAK'
WITH RECOVERY,
MOVE 'DATABASE_Data' TO 'F:\DATABASE_200508070216.mdf', 
MOVE 'DATABASE_Log' TO 'F:\DATABASE_200508070216.ldf'

Open in new window


I ended up getting the error message below:

Msg 3183, Level 16, State 2, Line 2
RESTORE detected an error on page (0:0) in database "DATABASE_200508070216" as read from the backup set.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Open in new window

The database is now stuck in the “Restoring” state.  It also created the MDF and LDF files.  The MDF file is 1.7 gigs while the LDF is 40 megs.  Is there any way to force this restore and let me view what was recoverable?

Many Thanks.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40438659
Something's almost certainly damaged and thus corrupted your backup file.

First, try making the db useable:
RESTORE DATABASE <db_name> WITH RECOVERY

If not, try putting the db in Emergency mode:
ALTER DATABASE <db_name> SET EMERGENCY
0
 

Author Comment

by:rye004
ID: 40438737
I did include “WITH RECOVERY” with my restore command above, however I went ahead and ran the following:

RESTORE DATABASE DATABASE_200508070216 WITH RECOVERY

Open in new window


I got the following error message

Msg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Open in new window


What is interesting about this, is the LDF file was restored.  I then ran the following command.

ALTER DATABASE DATABASE_200508070216 SET EMERGENCY

Open in new window


I got the following error message

Msg 5052, Level 16, State 1, Line 1
ALTER DATABASE is not permitted while a database is in the Restoring state.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Open in new window


Thank you for your help Scott.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40438779
Make sure you're using the correct logical names on the MOVE clauses.

Make sure the files you've referenced on the MOVE clauses don't already exist, and esp. that they are not the same file an existing db is currently using.
0
 

Author Comment

by:rye004
ID: 40438899
Scott,

I did confirm both of your concerns.  Each time I re-run the restore, I do confirm that the MDF and LDF files are deleted.
Each time I run the restore command, it does create the MDF file which is about 1.7 gigs.  It would be great if there was a way to make the database usable, even if not all the tables are available.  Any additional thoughts would be greatly appreciated.

Kind Regards
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40439877
Each time I re-run the restore, I do confirm that the MDF and LDF files are deleted.
That only happens if you restore over the same database. If you restore to a new database will give you the error that you posted here.
"Msg 3156, Level 16, State 3, Line 1
File 'user_Data' cannot be restored to 'D:\SQL_DATA1\DATABASE.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\SQL_LOG1\DATABASE_Log.ldf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1"

It would be great if there was a way to make the database usable, even if not all the tables are available.
That's not possible in SQL Server. You are restoring a database not importing tables.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40441230
Looks like a corrupted backup file.  On what medias were databases originally backed up ?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40441249
A little trick that may help you to get some data back and export some data (if you are lucky)...

> Restore WITH MOVE and NORECOVERY options. (See above)
> Create a database snapshot. Please read the following to get some info :
http://technet.microsoft.com/en-us/library/ms175876(v=sql.105).aspx
> Read data from the  snapshot.  
> Export from the snapshot into a new database
0
 

Author Comment

by:rye004
ID: 40443204
Racimo,

Thank you for sending me this article, it is interesting.  I have read through it a few times with no luck.  I am curious if you have any suggestions on how I should run this with my current situation.  I am also a bit confused on the “.SS” files used in the example.  I used my .MDF files instead since I don’t have an .SS file

Any input would be greatly appreciated.

Kind Regards
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40443495
The snapshots are snapshots in time using the source file and storing any changes occurring between the time of creation of snapshot and the present database.  The SS file is comparable to a log file of all transactions that can occur after the creation of the snapshot.  One advantage of snapshots is to allow access to data directly in MDF file without requiring the source database to be in RECOVERY mode .

Once the snapshot is created, it appears a database from which you can query.  Sometime when a restore fails it is a way to save some data.  In your case, I thought you could give it a shot and see.

Starting from a backup file, you could try the following
RESTORE DATABASE DATABASE_200508070216
FROM DISK = '\\Vboxsvr\x_drive\DATABASE_200508070216.BAK'
WITH NO RECOVERY,
MOVE 'DATABASE_Data' TO 'F:\DATABASE_200508070216.mdf', 
MOVE 'DATABASE_Log' TO 'F:\DATABASE_200508070216.ldf';

CREATE DATABASE DATABASE_200508070216_SN ON
( NAME = DATABASE_Data, FILENAME = 
'F:\SNAPSHOTS\DATABASE_200508070216_SN.SS' ) --> create a folder on F:\SNAPSHOTS
AS SNAPSHOT OF DATABASE_200508070216;

SELECT * FROM DATABASE_200508070216_SN..TABLE1;

Open in new window


If you have the MDF file you may try directly and see if you got something to save.

CREATE DATABASE DATABASE_200508070216_SN ON
( NAME = DATABASE_Data, FILENAME = 
'F:\SNAPSHOTS\DATABASE_200508070216_SN.SS' ) --> create a folder on F:\SNAPSHOTS
AS SNAPSHOT OF DATABASE_200508070216;

SELECT * FROM DATABASE_200508070216_SN..TABLE1;

Open in new window


Of course this not guaranteed but it may help. If you can create the snapshot you can read the data or part of it.  You may even try to revert back from the snapshot in an attempt to use only the MDF file and recreate a new log file.  You achieve that by creating a snapshot then run something like:

USE master;
RESTORE DATABASE DATABASE_200508070216_NEW FROM DATABASE_SNAPSHOT = 'DATABASE_200508070216_SN';
GO

Open in new window


Give it a shot and see what happens.

Hope this helps
0
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 2000 total points
ID: 40443506
If the above does not work, you may look at the following link.

https://gallery.technet.microsoft.com/Affordable-Solution-To-43165a47
0
 

Author Comment

by:rye004
ID: 40548314
Community: This question has not been abandoned.  This issue is taking more time than normal.  I will keep everyone updated.
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

661 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