Solved

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

Posted on 2014-11-10
17
328 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
  • 7
  • 4
  • 4
  • +1
17 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
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
 
LVL 45

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 69

Expert Comment

by:ScottPletcher
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 69

Expert Comment

by:ScottPletcher
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
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.

 
LVL 69

Expert Comment

by:ScottPletcher
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 45

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 500 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 to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sort by Month and Year - SQL 3 23
Access Migration to Sql Server 2 22
mySQL Syntax 7 12
Syntax using Declare 3 16
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

14 Experts available now in Live!

Get 1:1 Help Now