Restoring DB.

Mr. X.

unread,

Nov 10, 2012, 1:35:08 PM11/10/12

to

Hello,
I have two database servers (Version SQL Server 2008 r2) on same machine.
I have back upped the files like this:

(Recovery model is full).

BACKUP DATABASE AdventureWorks TO DISK =
C:\dba\TEST_PRINCIPLE\AdventureWorks.bak';
BACKUP LOG AdventureWorks TO DISK =
C:\dba\TEST_PRINCIPLE\AdventureWorks.trn';

copy those files to another folder : C:\dba\TEST_MIRROR.
Open the mirror database, and write following:

RESTORE DATABASE AdventureWorks FROM DISK =
'C:\dba\TEST_MIRROR\AdventureWorks.bak' WITH NORECOVERY;
RESTORE LOG AdventureWorks FROM DISK =
'C:\dba\TEST_MIRROR\AdventureWorks.trn' WITH NORECOVERY;

What I have got is the following error (Why, and what shall I do?)
Also, I have realized that the error include the path MSSQL10_50.TESTDBA,
while my database is MSSQL10_50.MIRROR_TESTDBA.
Thanks :)

Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '5(failed to retrieve text for this
error. Reason: 15105)' while attempting
'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft
SQL Server\MSSQL10_50.TESTDBA\MSSQL\DATA\AdventureWorks.mdf'.
Msg 3156, Level 16, State 8, Line 1
File 'AdventureWorks2008R2_Data' cannot be restored to 'C:\Program
Files\Microsoft SQL
Server\MSSQL10_50.TESTDBA\MSSQL\DATA\AdventureWorks.mdf'. Use WITH MOVE to
identify a valid location for the file.
Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '5(failed to retrieve text for this
error. Reason: 15105)' while attempting
'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft
SQL Server\MSSQL10_50.TESTDBA\MSSQL\DATA\AdventureWorks.ldf'.
Msg 3156, Level 16, State 8, Line 1
File 'AdventureWorks2008R2_Log' cannot be restored to 'C:\Program
Files\Microsoft SQL
Server\MSSQL10_50.TESTDBA\MSSQL\DATA\AdventureWorks.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.
Msg 3118, Level 16, State 1, Line 2
The database "AdventureWorks" does not exist. RESTORE can only create a
database when restoring either a full backup or a file backup of the primary
file.
Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.

Erland Sommarskog

unread,

Nov 11, 2012, 1:19:26 AM11/11/12

to

Mr. X. (nospa...@gmail.com) writes:
> Msg 3634, Level 16, State 1, Line 1 The operating system returned the
> error '5(failed to retrieve text for this error. Reason: 15105)' while
> attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program
> Files\Microsoft SQL
> Server\MSSQL10_50.TESTDBA\MSSQL\DATA\AdventureWorks.mdf'.

Error 5 is "Access is denied". I would suspect that your two instances has
different service accounts, and the service account for the second instance
does not have permissions to the files generated by the service account
for the first instance. The simplest would be to right-click the backup
file, select properties and change permissions.

> Msg 3156, Level 16, State 8, Line 1
> File 'AdventureWorks2008R2_Data' cannot be restored to 'C:\Program
> Files\Microsoft SQL
> Server\MSSQL10_50.TESTDBA\MSSQL\DATA\AdventureWorks.mdf'. Use WITH MOVE to
> identify a valid location for the file.

Best is to say when you move a database:

RESTORE DATABASE db FROM DISK = 'path'
WITH MOVE 'logname1' TO 'path1',
MOVE 'logname2' TO 'path2',
REPLACE

Where logname1 and logname2 is the logical names for the database files.
You find these if you run sp_helpdb on the source database. The names are in
the first column.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Mr. X.

unread,

Nov 11, 2012, 1:09:56 PM11/11/12

to

Maybe I have configured the db service account wrong.
Where is the db service account, and how can I see it and configure it
again?

Thanks :)

"Erland Sommarskog" wrote in message
news:XnsA1084A813...@127.0.0.1...

Erland Sommarskog

unread,

Nov 11, 2012, 5:14:43 PM11/11/12

to

Mr. X. (nospa...@gmail.com) writes:
> Maybe I have configured the db service account wrong.
> Where is the db service account, and how can I see it and configure it
> again?

SQL Server Configuration Manager. Find the server, right-click, select
Properties and then the Log On tab. You need to restart the server for
the vhange to take effect.

Mr. X.

unread,

Nov 12, 2012, 12:42:58 PM11/12/12

to

That's what I have checked out before,
but both SQL Servers (Which are on the same machine) has the same login
(Network service), so I don't understand what's wrong.

Thanks :)

"Erland Sommarskog" wrote in message

news:XnsA108EC771...@127.0.0.1...

Erland Sommarskog

unread,

Nov 13, 2012, 10:12:36 AM11/13/12

to

Mr. X. (nospa...@gmail.com) writes:
> That's what I have checked out before,
> but both SQL Servers (Which are on the same machine) has the same login
> (Network service), so I don't understand what's wrong.

As I said error 5 is "Access is denied", and this error can occur because
of two reasons: 1) Lack of permission. 2) File is in use.

The error message says that the error is with the file

'C:\Program Files\Microsoft
SQL Server\MSSQL10_50.TESTDBA\MSSQL\DATA\AdventureWorks.mdf'

You also indicate that this is not the expected path for the instance
you are trying to restore to. I would guess that this is the path
for the source database (that is, your other instance is called TESTDBA)
and this file is locked by the other instance.

Did you try the syntax with MOVE that I outlined in my first reply?

ellisw...@gmail.com

unread,

Aug 26, 2014, 1:57:08 AM8/26/14

to

A perfect solution has been mentioned in the below

http://www.sqlrecoverysoftware.net/blog/sql-error-15105.html