December 28, 2011 at 11:51 am
I have a backup of my transaction logs and it is locked by some process. I have shut down the server and restarted and the file is still being used by another process. I can not delete the file because it is being used by another process. What do I need to do to make this file usable?
December 28, 2011 at 12:19 pm
Did you do a restore with NORECOVERY? Is the transaction log part of log shipping?
December 28, 2011 at 12:28 pm
yes with norecovery and how do i check if it is a part of log shipping
December 28, 2011 at 12:32 pm
Well there you go. Pretty sure the restore process will lock the log file until you issue a with recovery statement.
Never mind the log shipping, that doesn't seem to be the issue here. I was just fishing for answers is all.
December 28, 2011 at 12:41 pm
ok i restored the dbase from backup and then tried to restore from my transaction log backup and it is still saying that the file can not be processed because another process is using it.
December 28, 2011 at 12:46 pm
So you're trying to restore a database with this transaction log backup and after doing so the file remains locked? How are you doing the restore? Can you post scripts if you have them?
December 28, 2011 at 1:10 pm
i restore from a backup with recovery and then i try to restore from transaction log file back up which i named transactionLogBackup.bak. It is that file that is locked. Following are scripts
RESTORE DATABASE [LOS_DocumentationSQLTest2] FROM DISK = N'C:\data\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BtcSystems.mdf' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
RESTORE LOG [LOS_DocumentationSQLTest2] FROM DISK = N'C:\data\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BtcSystems_log.ldf' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
gives the following errors:
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\data\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BtcSystems.mdf'. Operating system error 32(The process cannot access the file because it is being used by another process.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\data\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BtcSystems_log.ldf'. Operating system error 32(The process cannot access the file because it is being used by another process.).
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
December 28, 2011 at 1:28 pm
Those are not backup files. Those are the data and log files for a BtcSystems database. That's why you're getting that error message. You need to restore from a proper database backup. This may help you.
December 28, 2011 at 1:35 pm
Ok thank you for all of your help. This script
RESTORE DATABASE [LOS_DocumentationSQLTest2] FROM DISK = N'C:\data\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\backup\BtcSystem_backup_2011_12_23_190003_9638931.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
RESTORE LOG [LOS_DocumentationSQLTest2] FROM DISK = N'C:\data\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\backup\transactionLogFileBackup.bak' WITH FILE = 1, NOUNLOAD, STATS = 10
GO gives this outcome
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 15904 pages for database 'LOS_DocumentationSQLTest2', file 'BtcSystems' on file 1.
Processed 4 pages for database 'LOS_DocumentationSQLTest2', file 'BtcSystems_log' on file 1.
RESTORE DATABASE successfully processed 15908 pages in 3.697 seconds (33.615 MB/sec).
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\data\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\backup\transactionLogFileBackup.bak'. Operating system error 32(The process cannot access the file because it is being used by another process.).
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I think that we are almost there. Thank you
December 28, 2011 at 1:54 pm
OK that looks better. Something still seems to be using your transaction log backup though. Is it possible that you tried to restore this database at an earlier time and didn't specify the recovery? Can you look at that LOS_DocumentationSQL2 database in SSMS? Does it show a green arrow and say something like (restoring...) next to it?
December 28, 2011 at 1:58 pm
no it does not have a green arrow by it. if i want to do a point in time in the script would it look like STOPAT = 'mm/dd/yyyy hh:mm:ss'
I backed up transaction logs and used that file in restore script and this error occurred
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 768000001132100001, which is too recent to apply to the database. An earlier log backup that includes LSN 768000001125600001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I then try to go to the former backup of transaction log and get the same error about a process using it.
December 28, 2011 at 2:37 pm
OK well the only other thing I can think of is the backup is corrupt. Try running the below statement and let me know what it returns.
restore verifyonly from disk = 'C:\data\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\backup\transactionLogFileBackup.bak'
December 28, 2011 at 2:40 pm
bill.akin (12/28/2011)
no it does not have a green arrow by it. if i want to do a point in time in the script would it look like STOPAT = 'mm/dd/yyyy hh:mm:ss'I backed up transaction logs and used that file in restore script and this error occurred
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 768000001132100001, which is too recent to apply to the database. An earlier log backup that includes LSN 768000001125600001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I then try to go to the former backup of transaction log and get the same error about a process using it.
Yes, if you wanted to stop at a certain point in the transaction log backup you would use STOPAT.
And the reason you got the above error message is because you tried skipping the log file that is locked by a process. SQL Server won't let you do this since transaction logs are sequential and you can't break the chain of backups.
December 29, 2011 at 8:10 am
Ok I stopped sql services and the file became unlocked. I deleted it and then restarted sql services and backed up my live transaction logs and another file by the same name of the one i deleted is now showing and it is locked.
I am about ready to give up restoring to point of time should not be this difficult. I am going to stop sql services again and delete this one file and then find a step by step tutorial of restore database to point in time. Thanks for all of your help.
December 29, 2011 at 9:31 am
Not sure what else could be causing that. Hard to say without knowing your backup strategy. When was the last time you took a full backup?
The link I posted earlier in this thread should provide all the content you need in backing up and restoring a database.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply