SAP SQL Anywhere RESTORE with RENAME - What is the correct script for restoring database

[hrrudnytro111]

Dear Colleagues!
I restored  to new space database SQL Anywhere with RENAME clause pointing out to new dbspace example:

RESTORE DATABASE '/new place/restored.db' FROM '/old place/backup.01'
RENAME extra to '/new place/extra.db' RENAME extra2 to '/new place/extra2.db' RENAME extra3 to '/new place/extra3.db' RENAME msg to '/new place/msg.db'

After restore I'm trying to start new database server and I'm getting error message
I. 01/27 08:57:04. Opening dbspace 'extra' in file '/old_place/extra.db' for database 'verif_test_restored'
I. 01/27 08:57:04. Opening dbspace 'extra2' in file '/old_place/extra2.db' for database 'verif_test_restored'
In other words -> system is looking to old DB Space even-though RENAME clause was used
Can you, please, help me with my problem?

[chris_keating]

Can you share the full engine console from the line "Starting Database"?

Archive backups have not changed in many years but is also not exercised much side as backup to tape declined in use.

I set up a testwhere I have a database with a system dbspace and 3 additional dbspaces like 

• system\old\sysDb.db
• dbs1\old\dbs1.dbs
• dbs2\old\dbs2.dbs
• dbs2\old\dbs2.dbs

An archive backup was created based on that directory structure.  To restore, the following was executed

restore database
    'C:\forum\system\new\sysDB.db'
from
    'C:\forum\archive\MyArchiveBackup'
rename dbs1 to 'c:\\forum\\dbs1\\new\\dbs1.dbs'
rename dbs2 to 'c:\\forum\\dbs2\\new\\dbs2.dbs'
rename dbs3 to 'c:\\forum\\dbs3\\new\\dbs3.dbs'

I then start the restored database as follows:

     dbeng17 -n NewDb c:\forum\system\new\sysDB.db

showing the following:

Starting database "sysDB" (C:\forum\system\new\sysDB.db) at Mon Jan 27 2025 09:18:22.353
Performance warning: Database file "C:\forum\system\new\sysDB.db" consists of 5 disk fragments
Opening dbspace 'dbs1' in file 'c:\\forum\\dbs1\\new\\dbs1.dbs' for database 'sysDB'
Opening dbspace 'dbs2' in file 'c:\\forum\\dbs2\\new\\dbs2.dbs' for database 'sysDB'
Opening dbspace 'dbs3' in file 'c:\\forum\\dbs3\\new\\dbs3.dbs' for database 'sysDB'

I would confirm that the database (reported in Starting database) is /new place/restored.db. 

[hrrudnytro111]

Hello, Chris!
I'm using Linux OS and I can share the output from the console where I started the restore:

Opening dbspace 'DBSpace_name2' in file 'Old file name 2.db' for database 'a_restore'
Opening dbspace 'DBSpace_name3' in file 'Old file name 4.db' for database 'a_restore'
Direct IO disabled for file 'Old file name 2.db' because the file system on which it resides does not support it
Cannot open dbspace 'DBSpace_name2' from file 'Old file name 2.db'
Error: Database cannot be started -- Resource temporarily unavailable
SAP SQL Anywhere 

[VolkerBarth]

Just out of curiosity: In my understanding, it's way more familiar to use IMAGE BACKUPS (i.e. each database file stored as separate files) than ARCHIVE BACKUPS, and it's also way easier to restore them by simply copying to the destination path. - Is there a particular requirement to use archive backups for you?

[chris_keating]

As Volker pointed out more directly, I would recommend IMAGE backups. That said, I do not think that the backup is involved in this error. The database should be able to start even if missing a dbspace.

[hrrudnytro111]

Hello, Colleagues! I confirm that there are certain requirements to use ARCHIVE BACKUPS.
So, Please, help me to resolve this issue; I already spent some time, but I didn't find any useful information
I'm providing example of my screen: marked in red -> it is location of the new database file,
marked in black -> system tries to open dbspace on the "old" location of the database.
Overall the question is "What does it mean "a-restore" database?
SAP SQL Anywhere 

[chris_keating]

Redacting and/or changing the context of your responses make it very difficult to help. I would suggest opening a support incident if you are concerned with sharing the exact details of what you are doing. As noted, very few environments use "backup to archive" so you will find limited expertise/experience on this forum for this backup type. The 'a_restore' is the database name used by the engine during a "recovery" of an archive backup. Are the rename restored dbspaces on the same disk? If so, which file is being reported in the direct I/O message?

[hrrudnytro111]

Hello, Chris!
Thanks for your answer! I didn't change any requirements, I just provided screen shot from the server showing the error message.
Can I provide some other content to support this use-case?

[chris_keating]

Please refer to my post for guidance.

• Start with the database setup that is being archived. Include the full directory for each of the database files so we know what the starting point looks like. We will assume that the archive backup is based on that setup.
• Provide the exact BACKUP statement that is used to create the archive.
• Provide the exact RESTORE statement that is being used, including full paths to all of the database files in that statement.
• Provide the console output when starting the "restored" database beginning with Starting database to the last Opening dbspace and any startup errors. If you are unsure of the block to collect, please provide the complete console log for that startup. 

I would prefer no screen caps. Just provide the text from a cut and paste.  Please do not modify any of the details or redact any of the information requested.  If there is information that is sensitive, you could recreate the issue in a "test" location and use it to share information otherwise open a support case where you can share that information securely.

[hrrudnytro111]

Hello, Chris!

Please, find the details below:
Linux OS

Point #1. 
/abc/test_database.db
/abc/test_database_p0.db
/abc/test_database_p2.db
/abc/test_database_p3.db
/abc/msg.db
/abc/test_database.LOG

DBSpaces:
/abc/test_database.db system
/abc/test_database_p0.db extra
/abc/test_database_p2.db extra2
/abc/test_database_p3.db extra3
/abc/msg.db msg
/abc/sub-folder/tmp_000005 temporary

Point #2.
dbisql -c "uid=user;pwd=111111" -host 10.10.10.10 -port 9999 -nogui "BACKUP DATABASE TO '/backup_place/test_database_full.01' size 500000000 TRANSACTION LOG TRUNCATE" >> /backup_place/email_part.log

Point #3.

dbisql -nogui -c "UID=dba;PWD=password;DBN=utility_db;Host=10.10.10.10;Server=TestSrv" /restore_files/restore.sql

file "/restore_files/restore.sql" is below:

RESTORE DATABASE '/new_restored_place/test_database_restored.db' FROM '/backup_place/test_database_full.01'
RENAME extra to '/new_restored_place/extra_rest0.db'
RENAME extra2 to '/new_restored_place/extra_rest2.db'
RENAME extra3 to '/new_restored_place/extra_rest3.db'
RENAME msg to '/new_restored_place/msg_rest.db'

After the restore process starts -> there are no error messages.
Restore process creates db files specified in the restore command.

At the end of restore process OS generates next error message:
"Could not execute statement.
  Unable to start specified database: unable to start database
  "/new_restored_place/test_database_restored.db"
  SQLCODE=-82, ODBC 3 State = "08001"

At the separate process where I start utility_db server I'm getting error message related to "a_restore". Also if I would like to start newly restored database then I'm getting the same error message.
  

[chris_keating]

Some things to consider

• you might not want to RESTORE a database on the same server as the source database is currently running.  This could result in a naming conflict. 
• if you are running a network server, you have to ensure that you have given permissions to start a database with the -gd switch.  I would suggest RESTORE on a personal server to avoid that requirement. 

I would likely use, where ENG is a unique server name on the machine running the restore. This will autostart the personal server.

dbisql -nogui -c "UID=dba;PWD=password;ENG=MyBURestore;DBN=utility_db" /restore_files/restore.sql

[hrrudnytro111]

Hello, Chris!
Thanks for your answer!
Let me comment your considerations.

Consideration #1. 
That's why I use different naming conventions. I can share my experience that I managed to restore database on similar server while running another database, the only difference was in successful case - there was only one database file. And there is business requirement to implement my use-case, I mean not to stop running database server.
Consideration #2.
It is the same machine. So the requirement is restoring with such set up
Feel free to provide reasons why "a_restore" error message can appear

Thanks!

[chris_keating]

You glossed over the details of the "a_restore" error.  As I noted, this is a temporary connection that is used during the restore. It may be related to some of the usage issues for the RESTORE. I do not think it is critical in explaining the issue.

During the restore process, the SYSTEM dbspace is loaded on the server performing the restore.  If you are already running the "old" SYSTEM dbspace, the restore cannot load the restored SYSTEM dbspace because each database name must be unique and I do not see a way to make that happen with a restore.  I do not think that the SYSTEM dbspace can be renamed during the RESTORE.   Doing so, the error -717 RESTORE unable to open file %1 is reported. You may be able to provide a distinct database name to the source database so that when the restored database is loaded, the database name will not be in conflict. I suspect that you are simply relying on the default naming rules. i.e., dbsrv17 -n MyServ c:\myPath\MyDatase.db will use the default database name which is the file without pathing and extension or in this case MyDatabase.  Instead, you can explicitly name the database dbsrv17 -n MyServ c:\myPath\MyDatabase.db -n MyExplicitDBN.  

I have misspoke as you only need the -gu option as its scope is limited to the utility_db. The -gd defines the rule for any connection. The -gu option is required when running a network server and you want to start a database on it. 

• I still recommend doing the restore on a separate server, moreso now that the source database must stay running. 
• If you need to rename the backup system dbspace, that cannot be done with the RESTORE statement.  I would us xp_move_file or something at the OS level to perform that rename.
• Once the restore is done, you can connect to the utility_db on the server hosting the source database and then use the START DATABASE Statement to start the backup and if needed use the AS to set the database name. 

[hrrudnytro111]

Hello, Chris!
Thanks for providing the reply

Two comments from my side:
Comment #1.

• If you need to rename the backup system dbspace, that cannot be done with the RESTORE statement.  I would us xp_move_file or something at the OS level to perform that rename - I don't want to rename "dbspace" I want to map to newly created files with another name

Comment #2. I'm trying to understand what "a_restore" means -> because it looks like this error happens after restore is completed and database server is trying to complete it.
My next steps will be - Stop database server which is running and try to run my restore with changing file names

[hrrudnytro111]

Hello, Colleagues!
Maybe it makes sense to register my use-case as the valid use-case for the database management, and implement improvement if there is no solution for it.
As I'm following manual and it looks like current version of restore process doesn't support it!
Thanks!
SAP SQL Anywhere 

[chris_keating]

You cannot perform the restore on the same database server where the source database is currently running. You either need to stop the source database -or- run a different engine during the restore. in short, if you are restoring a database from backup, the source database would be expected to be in a bad state and not be running.  There are viable ways to achieve your requirements in the existing implementation, either archive or the more modern image backup methods.