4

I have log shipping enabled on my database. I created a file, with let's say logical name 'AB' of 50 MB at location E:\Databases\AB.ndf. However, I later dropped this file and created another file with logical name 'AB_File' of 10 MB at same location E:\Databases\AB.ndf. Now the server where the backup was moved to is failing with error:

Error: 

1) Could not apply log backup file 'some location of a .trn file' to secondary database

2)The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\Databases\AB.ndf'.

3) File 'AB_File' cannot be restored to 'E:\Databases\AB.ndf'. Use WITH MOVE to identify a valid location for the file.

I have tried running the following script:

RESTORE LOG [db_name] FROM
DISK =  'E:\Log_Shipping\\db_name_20160817070500.trn'
WITH MOVE 'AB_FILE'
TO 'E:\Databases\AB.ndf',
standby = N'E:\Databases\\db_name_20160818.tuf'

I don't seem to be able to fix this. Please help!

The file location on the backup server is the same as that on primary server. Also, the file-group was dropped and the file names are correct.

0

2 Answers 2

2

As you said the error

The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation'

The reason for this is that the name of the database files of the databases you are trying to restore already exists.

This could be for two reasons, either you have used the same create scripts for the same database on all instances or you have already done a restore of the database you are trying to restore now to that instance.

To solve this issue do the following:

  1. In the restore page in SSMS, press the "options" tab.

  2. In the "Restore as" column change the name of the datafile(s) and log file to something unique.

Then hit the restore button and that should be it.

for your ref Here

6
  • SQL Server Management Studio 2012 doesn't have the Options tab. Pressing on Tools drop down menu enlists 'Options' below 'customize' and others but I feel you're trying to tell me something else. Could you please elaborate Commented Aug 18, 2016 at 12:04
  • @Ritesh Bhakre, Right Click on that database , which you want to restore. Over there you shall find out 'Options' there. Commented Aug 18, 2016 at 12:08
  • Right-clicking on the said db provides nothing called as 'options' , however there's 'Tasks', clicking on that further providers 'Restore' and further clicking on that provides 2 options: 'Page' and 'Transaction Log'. Commented Aug 18, 2016 at 12:16
  • @Ritesh Bhakre,Right click the DB->Properties->Options Commented Aug 18, 2016 at 13:30
  • Doing as you said provides drop down options: 'Automatic', 'Containment', 'Cursor', 'Filestream', 'Miscellaneous', 'Recovery', 'Service Broker' and 'State', Recovery option further has 'Page Verify' and 'Target Recovery Time'. I can't use any of these to fix my issue. Please help Commented Aug 18, 2016 at 14:09
0

Check few things

  1. Logical and physical file name
  2. files are still exist in the folder
  3. verify file location

Try

RESTORE LOG [db_name] FROM
DISK =  'E:\Log_Shipping\\db_name_20160817070500.trn'
WITH FILE=1,
    MOVE 'AB_FILE'
    TO 'E:\Databases\AB.ndf',
    standby  N'E:\Databases\\db_name_20160818.tuf',
    NOUNLOAD,  REPLACE, RECOVERY, 
;
GO

Hope it will fix your problem.

1
  • 1
    For log shipping, shouldn't this be NORECOVERY? Otherwise, I don't think you can apply the next log file.
    – RDFozz
    Commented Sep 22, 2017 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.