July 15, 2008 at 7:22 am
I need to take the latest backup of a database and restore the data to another database on a different server.
I am using REXX to copy the latest .bak file of database1 from server1 to server2 and then run a stored procedure to restore database2 on server2. The stored procedure is in the Master database on server2.
I am getting sqlcode 3021 '[ODBC SQL Server Driver][SQL Server] Cannot perform a backup or restore operation within a transaction'
Is there a way around this or is it really not possible to restore a database without using SQL Server?
July 15, 2008 at 7:37 am
Hi, what is the SP that you are using to restore?
you can move the backup from server 1 to server 2 using a shared folder, or by using the XP_cmdshell command. once the database backup is moved, restore it with the restore command.:P
July 15, 2008 at 7:42 am
Adding to that, you can restore the database with SQL Server EM:), you can use the TSM backup and restore process. Also there would be few third party tools to backup and restore, like redgate, visit:
http://www.red-gate.com/products/SQL_Backup/index.htm 😀
July 15, 2008 at 7:48 am
The copying of the .bak file is not a problem. The problem is the restore.
I am using REXX to connect to the server using SQLConnect and execute the stored procedure. The stored procedure is:
RESTORE DATABASE Live_dw
FROM DISK = 'e:\Live_restore\altair_live.bak'
WITH REPLACE
The procedure runs ok when I am logged on to SQL Server.
July 15, 2008 at 7:56 am
Hi, I think you may be having some permissions issue...not sure though!:)
July 15, 2008 at 8:26 am
It sounds as though your REXX/SQLConnect is automatically starting a transaction. If you can't stop it from doing this, try adding this to the beginning of your stored procedure
if @@trancount <> 0
rollback tran
July 15, 2008 at 9:01 am
Thank you, Thank you, Thank you Ian Scarlett. That has solved my problem:kiss:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply