How to restore encrypted backups?

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: JonathanWatts, Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

How to restore encrypted backups?

Postby agarg » Mon Apr 01, 2013 4:54 pm

I posted this question few minutes ago in a discontinued forum and then realized my mistake. So, posting in the correct forum now.


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
agarg
 
Posts: 9
Joined: Tue May 22, 2012 4:10 pm

Postby petey » Tue Apr 02, 2013 1:24 am

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
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Postby garga1 » Tue Apr 02, 2013 3:20 am

Thanks for your reply. As you can see in the backup code I posted below, I passed the value for password as abc12defLHG. I think what you are saying is that since this value is enclosed between <ENCRYPTEDPASSWORD> and </ENCRYPTEDPASSWORD> tags, this is the encrypted form of the password. Well, in that case, I don't have the non-encrypted password. So, to fix my code for future, this is what I need to run to take 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 = ''abc12defLHG''"'


And then use abc12defLHG as password to restore when needed. Is my understanding correct?

Ajay
garga1
 
Posts: 3
Joined: Wed Mar 07, 2012 6:43 pm

Postby petey » Tue Apr 02, 2013 7:06 am

Yes.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 1 guest