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.
Mr. X. (nospam....@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
Mr. X. (nospam....@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
Mr. X. (nospam....@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.
-- Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
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.
Mr. X. (nospam....@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.
-- Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Mr. X. (nospam....@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?
-- Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
