Am trying to test a restore script for a SQL Anywhere 11 database on our staging server. Here is my batch file:

EECHO restore db started >>db_restore_log.txt
ATTRIB -R D:\DonorCentral\DB\dctemp.db >>db_restore_log.txt 
RENAME D:\DonorCentral\DB\dctemp.db old_dctemp.db >>db_restore_log.txt
COPY C:\DonorCentral\DB\dctemp.log C:\DonorCentral\DB\old_dctemp.log >>db_restore_log.txt 
COPY D:\DonorCentral\Backup\dctemp.db D:\DonorCentral\DB\dctemp.db >>db_restore_log.txt 
"%SQLANY11%\bin32\dbsrv11.exe" -o db_restore_log.txt D:\DonorCentral\DB\dctemp.db -a D:\DonorCentral\Backup\dctemp.log
"%SQLANY11%\bin32\dbsrv11.exe" -o db_restore_log.txt D:\DonorCentral\DB\dctemp.db -a C:\DonorCentral\DB\dctemp.log

Running this successfully does the file operations (i.e. ATTRIB, RENAME, COPY) and opens the database server console. However, it fails when trying to restore the transaction log from the backup. The message is "Cannot open transaction log file -- access is denied". The log from the backup is not read-only. Any ideas on problem and resolution?

Thanks, Tom

asked 13 May '16, 13:40

Tom%20Rolseth's gravatar image

Tom Rolseth
191878
accept rate: 0%

edited 13 May '16, 14:38

Reimer%20Pods's gravatar image

Reimer Pods
4.2k334482

The database server is reading the backup log here ... so a r/o attribute should not hidern in doing that.

But having said that I cannot think of any reason why that should fail at that point. You may need to investigate the failure at a lower level; possibly with the Sysinternals ProcesMonitor utility.

At this point I can only believe the database server is getting some sort of 'Access is denied' error (error number 5) when/while opening that file. One possibility may be that some operation (like maybe a LOAD TABLE operation) is encountering the file system 'permissions' error and maybe that is being reflected as a transaction log 'open time' error. just-a-thought

(13 May '16, 17:32) Nick Elson S...

Just another thought:

Do you need the backup log (D:\DonorCentral\Backup\dctemp.log), or will the current log (C:\DonorCentral\DB\dctemp.log) contain the same operations because the current log was not truncated/renamed when doing the full backup? In case the latter is true, you may skip the backup log...

(14 May '16, 05:56) Volker Barth
2

Show us the db_restore_log.txt file.

(14 May '16, 08:18) Breck Carter

Thanks for the replies. I resolved this by running the batch file as an administrator.

permanent link

answered 16 May '16, 17:17

Tom%20Rolseth's gravatar image

Tom Rolseth
191878
accept rate: 0%

So the previously used account had no access to the backup log file - or to a separate file used by LOAD TABLE or other log operations?

(16 May '16, 18:19) Volker Barth

I had access to the backup file but running batch file normally caused error. As soon as I right-clicked on the batch file and selected 'run as administrator' it worked fine

(16 May '16, 20:14) Tom Rolseth
Replies hidden

What was the Operating System? Windows 10, perchance? And was a shortcut used to run the batch file? If so, it is possible to give the shortcut "run as administrator" rights on a permanent basis.

(16 May '16, 21:09) Breck Carter

It was on a Windows 2012 server -- not windows 10 -- and it was not a shortcut.

(21 May '16, 08:49) Tom Rolseth
Replies hidden
1

FWIW, with Windows 10, I am getting THIS CLOSE to disabling UAC altogether:

HKLM:\Software\Microsoft\Windows\CurrentVersion\Policies\System" -Name "EnableLUA" -Value "0"

On a computer used only by Administrator users it is hard to justify the existence of UAC... of all the hundreds (thousands?) of times it has gotten in my way, it has protected me ... exactly ... zero times.

(21 May '16, 09:27) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×14

question asked: 13 May '16, 13:40

question was seen: 352 times

last updated: 21 May '16, 09:30