Cannot perform a backup or restore operation within a transaction #30902
Replies: 1 comment
-
This is exactly what the message tells you. You cannot restore the DB while you have transaction running. The operator you are running is connecting to your database and running DB query - which opens transaction. So yeah, it would be totally unreasonable to restore/backup DB using this operator. Also - what you need to do you need to - likely run some external tool (MsSQL CLI) via bash operator for example to run backup/restore operation. You would have to make sure that the task you will run it with will have enough permissions to do that (usually in Airflow you would configure permissions in connections to run SQL queries but not to backup/restore the DB). So you need to make sure that whatever runs your bash CLI tool will be also properly configured to have the right credentials. |
Beta Was this translation helpful? Give feedback.
-
Hi!
I'm making DAG which have 3 tasks:
Restore code is:
@task.python
def restore_databases(databases, backup_file_path):
for db in databases:
mssql_hook = MsSqlHook(mssql_conn_id='ms_sql_dev_test')
conn = mssql_hook.get_conn()
conn.autocommit(False)
sql = f"RESTORE DATABASE {db} FROM DISK = N'{backup_file_path}' WITH FILE = 1, MOVE N'UPP_Current' TO N'/mnt/mssqlserver/data/{db}.mdf', MOVE N'UPP_Current_log' TO N'/mnt/mssqlserver/data/{db}_log.ldf', NOUNLOAD, REPLACE, STATS = 5"
mssql_op = MsSqlOperator(task_id='restore_database', mssql_conn_id='ms_sql_dev_test', sql=sql)
mssql_op.execute(context=None)
But a have next error:
pymssql._pymssql.OperationalError: (3021, b'Cannot perform a backup or restore operation within a transaction.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n')
Please help me to resolve it.
Beta Was this translation helpful? Give feedback.
All reactions