Problem in database restore

Question

Problem in database restore

DURGESH-769360

SSC Eights!

Points: 968
More actions
August 16, 2009 at 11:03 pm

#156111

Hi all,
I am getting following message when trying to restore a newly created database with existed backup file file. I am getting the follow in messages
RESTORE DATABASE TestDB
FROM DISK = '\\xyz\Databackups\ddd.BAK'
WITH MOVE 'ddd_Data' TO 'E:\Microsoft SQL Server\MSSQL\data\TestDB_Data.MDF',
MOVE 'ddd_Log' TO 'E:\Microsoft SQL Server\MSSQL\data\TestDB_Log.LDF'
GO
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Thanks in Advance
Regards
Durgesh
September 27, 2018 at 11:15 pm

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Based on the error message, I'd say you have a user accessing the database. You may want to run an SP_WHO2 and see who is using the database.

DURGESH-769360 SSC Eights! Points: 968 More actions · Answer 2

Hi,

I stopped the user who is accessing the database and now i am getting the following message

Server: Msg 3201, Level 16, State 2, Line 1

Cannot open backup device '\\xyz\Databackups\ddd.BAK'. Device error or device off-line. See the SQL Server error log for more details.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Sarab_SQLChamp Hall of Fame Points: 3882 More actions · Answer 3

The problem is with the backup set, either it is not available or your ID is not authorized to use that.

In place you can copy the backup to a local drive and then try to restore it.

Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 4

Restore doesn't run under a user ID. It runs under the service account. That account might not have rights to that path.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Steve Jones - Editor (8/17/2009)
Restore doesn't run under a user ID. It runs under the service account. That account might not have rights to that path.

I have to agree with Steve, this is a permissions issue.

Check to be sure the service account has access to source share for the backup file.

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 6

DURGESH-769360 (8/16/2009)
Hi,
I stopped the user who is accessing the database and now i am getting the following message
Server: Msg 3201, Level 16, State 2, Line 1
[highlight=#ffff11]Cannot open backup device '\\xyz\Databackups\[/highlight]ddd.BAK'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

You will most likely find that the account the SQL runs under doesn't have permissions on your backup folder.

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Please note: 4 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 8

GilaMonster (12/11/2013)
Please note: 4 year old thread.

It was one of "those" days -- I missed the date all together... :ermm:

Hope this helps...

Ford Fairlane
Rock and Roll Detective