2005 Restoring Locks MDF and LDF files

• Bernd Linde

  SSC Rookie

  Points: 37

  More actions

  April 2, 2008 at 6:40 am

  #188106

  [font="Courier New"]

  Hey all

  I have a weird problem:

  When performing a normal restore via Management Studio, all is well and happy.

  When i perform the restore via my application, the mdf and ldf files are locked as 'in use' and

  any restores that I try to do on the same db fail.

  My restore script that runs from my application looks like this:

  restore database [NewDBName]

  from disk = 'C:\Backups\NewDBBackup.bak'

  with replace,

  move '*logicaldatafilename*' to 'C:\DBFiles\NewDBName_data.mdf,'

  move '*logicaldatafilename*' to 'C:\DBFiles\NewDBName_Log.ldf'

  the *sections* are naturally replaced with the logical file names within the backup.

  Thanks in advance

• rajankjohn

  SSCertifiable

  Points: 5061

  More actions

  April 2, 2008 at 6:54 am

  #797168

  are u able to take the backup using the same script via ssms query?

• Bernd Linde

  SSC Rookie

  Points: 37

  More actions

  April 2, 2008 at 7:15 am

  #797185

  Yupe, performing the same script in ssms and then trying to restore via my application works (so no locking happens there)

• Bernd Linde

  SSC Rookie

  Points: 37

  More actions

  April 2, 2008 at 9:09 am

  #797317

  I have resolved this issue.

  After a couple of hours of searching and testing, i have finally resolved the issue.

  My restore script gets dynamically created in sql.

  so the parts

  move '*logicaldatafilename*' to 'C:\DBFiles\NewDBName_data.mdf,'

  move '*logicaldatafilename*' to 'C:\DBFiles\NewDBName_Log.ldf'

  were actually created as

  move '*logicaldatafilename*' to 'C:\DBFiles\\NewDBName_data.mdf,'

  move '*logicaldatafilename*' to 'C:\DBFiles\\NewDBName_Log.ldf'

  note the extra \

  That one backslash makes that SqlServer keeps the files locked for some reason.

  I have fixed my code and all is happy now.