Red Gate forums :: View topic - Can I restore encrypted backup created on different server?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 6
SQL Backup 6 forum

Can I restore encrypted backup created on different server?

Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
jasolution



Joined: 07 Oct 2008
Posts: 8

PostPosted: Mon Nov 23, 2009 5:09 pm    Post subject: Can I restore encrypted backup created on different server? Reply with quote

If I have an encrypted .SQB file created on one server using Backup Pro 6.2, can I restore it to a different database server using a different install of Backup Pro 6.2 on the destination server?

This is what I am trying to do, but when I attempt to do this, I am unable to. It never enables the "Decrypt" input, but it also is not explicitly stating an error in the GUI.

Thanks,

Jason
Back to top
View user's profile Send private message
jerryhung



Joined: 28 Dec 2007
Posts: 116

PostPosted: Mon Nov 23, 2009 9:17 pm    Post subject: Reply with quote

Yes you can, at least I just did it today

I have encrypted .sqb backup file from production
restored it to the QA server via script (or GUI) today

RESTORE .............
WITH PASSWORD = 'xxxxxx'


I'm using SQL Backup 6.3 already though
_________________
Jerry Hung
DBA, MCITP
Back to top
View user's profile Send private message
jasolution



Joined: 07 Oct 2008
Posts: 8

PostPosted: Wed Jun 15, 2011 7:26 pm    Post subject: Reply with quote

Were you able to figure out how to do this? I am trying the same thing with 6.4 right now. When I copy an encrypted .sqb file to a different server and attempt to restore it, the Decrypt option never becomes enabled so I can enter the encryption password.
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2306

PostPosted: Thu Jun 16, 2011 3:06 am    Post subject: Reply with quote

SQL Backup is fully backwards compatible, so you can restore backup files created with older versions of SQL Backup with all newer versions of SQL Backup.

Could you please run a small test for us:

- back up a small database e.g. model
Code:
EXEC master..sqlbackup '-sql "BACKUP DATABASE model TO DISK = [c:\model.sqb] WITH PASSWORD = [test]"'


- copy the file to the other server

- run and send us the output from the following commands on the other server:
Code:
EXEC master..sqlbackup '-sql "RESTORE HEADERONLY FROM DISK = [...] "'

Code:
EXEC master..sqlbackup '-sql "RESTORE HEADERONLY FROM DISK = [...] WITH PASSWORD = [test]"'

where [...] is the fully qualified name of the copied file.

Thank you.
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
agarg



Joined: 22 May 2012
Posts: 9

PostPosted: Mon Apr 01, 2013 4:48 pm    Post subject: Decrypt Password during restore Reply with quote

I am having some issues with the restore command when trying to restore encrypted backup. This is what I ran to take a backup:

EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE AjayDB TO DISK = ''Z:\WrkFolder\AjayDB_201304011011_full.bak'' WITH COMPRESSION = 1, THREADCOUNT = 4, KEYSIZE = 256, PASSWORD = ''<ENCRYPTEDPASSWORD>abc12defLHG</ENCRYPTEDPASSWORD>''"'


Now, when I try to restore it via GUI, I supply the same password as above: abcd12defLHG. But it says: bad password. I tried to do it thru script as well, but did not help. I tried both of the following commands - none helped.

EXECUTE master.dbo.sqlbackup N'-SQL "RESTORE DATABASE [AjayDB] FROM DISK = ''Z:\WrkFolder\AjayDB_201304011011_full.bak'' WITH DISCONNECT_EXISTING, PASSWORD = ''abc12defLHG'', REPLACE, MOVE ''AjayDB'' TO ''Z:\WrkFolder\AjayDB.mdf'', MOVE ''AjayDB_log'' TO ''Z:\WrkFolder\AjayDB.ldf'', RECOVERY"'

EXECUTE master.dbo.sqlbackup N'-SQL "RESTORE DATABASE [AjayDB] FROM DISK = ''Z:\WrkFolder\AjayDB_201304011011_full.bak'' WITH DISCONNECT_EXISTING, PASSWORD = ''<ENCRYPTEDPASSWORD>abc12defLHG</ENCRYPTEDPASSWORD>'', REPLACE, MOVE ''AjayDB'' TO ''Z:\WrkFolder\AjayDB.mdf'', MOVE ''AjayDB_log'' TO ''Z:\WrkFolder\AjayDB.ldf'', RECOVERY"'


This is the msg generated:

SQL Backup v7.0.6.2
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Restoring AjayDB (database) from:
Z:\WrkFolder\AjayDB_201304011011_full.bak

Error 710: Wrong password entered.

SQL Backup exit code: 710

(6 row(s) affected)

name value
---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
exitcode 710
sqlerrorcode 0
filename001 Z:\WrkFolder\AjayDB_201304011011_full.bak

(3 row(s) affected)




Can you help?

Thanks.
Ajay
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2306

PostPosted: Tue Apr 02, 2013 1:24 am    Post subject: Reply with quote

You need to provide the plain text (non-encrypted) password in order to restore the database.

E.g. when you back up a database or set up a backup job using the GUI, it encrypts the password you entered so that anyone looking at the script will not know the plain-text password.

If SQL Backup allowed users to restore the database using the same encrypted value, it defeats the purpose of encrypting the password. Thus, you need to know the plain text password to restore the database.
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group