How can I change the data in the .bak file backed up in MSSQL to insert data format?

Question

With help from ChatGPT, I received the code below and am checking it, but I don't know why the error occurs. Please help me

import pyodbc

conn_str = 'DRIVER={SQL Server};Server=myserver;Database=mydatabase;Trusted_Connection=True'
# create connection
conn = pyodbc.connect(conn_str)

# backup path
backup_file_path = 'D:\\Test.bak'

cursor = conn.cursor()
cursor.execute("CREATE TABLE #TempTable (D VARBINARY(MAX))")

with open(backup_file_path, 'rb') as f:
    backup_data = f.read()
cursor.execute("INSERT INTO #TempTable (D) VALUES (?)", backup_data)

select_query = "SELECT data FROM #TempTable"
for row in cursor.execute(select_query):
    backup_data = row[0].decode('utf-8') 

    table_data = backup_data.split("INSERT INTO ")
    for table_insert in table_data[1:]:
        table_name = table_insert.split()[0]
        values_start_index = table_insert.find("VALUES") + len("VALUES")
        table_values = table_insert[values_start_index:].strip().strip(";")
        insert_script = f"INSERT INTO {table_name} {table_values};"
        print(insert_script)

cursor.execute("DROP TABLE #TempTable")

cursor.close()
conn.close()

File "D:\Git\temp\convertBakUp2SQL.py", line 18, in cursor.execute("INSERT INTO TempTable (D) VALUES (?)", backup_data) pyodbc.Error: ('HY000', '[HY000] [Microsoft][ODBC SQL Server Driver]Warning: Partial insert/update. The insert/update of a text or image column(s) did not succeed. (0) (SQLPutData); [HY000] [Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, or image pointer value conflicts with the column name specified. (7125)')

The text, ntext, or image pointer value conflicts with the column name specified. (7125)') I heard that the error can be caused by the file size, so I tested it with a small file, but there was no progress at all.

In doing this, the .bak file backed up in the 2018 version of SQL Server cannot be restored in the 2017 version, so the intention is to restore all data by changing the insert statement. Please help if there is anything that needs to be fixed

Answer 1

What exactly are you trying to do? Every backup file (*.bak) is made by:

• One Primary Data File (*.mdf);
• One Transaction Log File (*.ldf);
• One or more Secondary Data File (*.ndf).

You can verify the integrity/structure of these files by doing:

RESTORE FILELISTONLY FROM DISK = 'D:\\Test.bak' 

This will show you how many of the above files your *bak file consists of.

It seems that you want to execute a Python script to do your SELECT code (amongst other things) and I'm not understanding your code. Why are you trying to INSERT INTO a temporary table a full backup (*.bak)? You should INSERT INTO a temp table the data that it's inside another table.

So you'll need to actually do the INSERT INTO command, example:

    `-- Create a temporary table
    CREATE TABLE #TempTable (
        ID INT,
        Name NVARCHAR(50)
    );

-- Insert data into the temporary table
INSERT INTO #TempTable (ID, Name)
VALUES (1, 'John'),
       (2, 'Alice'),
       (3, 'Bob');`

I hope this helps you understanding what to change to move forward with your issue.