Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 880
  • Last Modified:

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

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
rye004
Asked:
rye004
  • 7
  • 4
  • 4
  • +1
1 Solution
 
Scott PletcherSenior DBACommented:
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
 
rye004Author Commented:
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
 
rye004Author Commented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
rye004Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
rye004Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
rye004Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Racim BOUDJAKDJICommented:
Looks like a corrupted backup file.  On what medias were databases originally backed up ?
0
 
Racim BOUDJAKDJICommented:
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
 
rye004Author Commented:
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
 
Racim BOUDJAKDJICommented:
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
 
Racim BOUDJAKDJICommented:
If the above does not work, you may look at the following link.

https://gallery.technet.microsoft.com/Affordable-Solution-To-43165a47
0
 
rye004Author Commented:
Community: This question has not been abandoned.  This issue is taking more time than normal.  I will keep everyone updated.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 7
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now