Restore (add new DB) from password protected BAK file

  • Jim Russell-390299

    SSCrazy Eights

    Points: 9333

    I have been given two .BAK files from another SQL Server, and am attempting to get those DBs installed on a local SQL Server using the RESTORE command. If i had .MDF files, I would simply attach them, but having only .BAK files I am trying to accomplish the same thing via RESTORE.

    As near as I can tell, SSMS does not provide for passwords in the "Databases\Restore Database..." UI dialogs. I can locate the BAK files (with some limitations), but simply get an indication that the backup file is password protected.

    Trying to resort to T-SQL (and perhaps not understanding what I am doing) I tried:

    RESTORE DATABASE DB1 FROM DISK='\\MyServer\MyDirectory\DB1_Full20081022.BAK'

    WITH MEDIAPASSWORD='pwd1'

    RESTORE DATABASE DB2 FROM DISK='\\MyServer\MyDirectory\DB2_Full20081022.BAK'

    WITH MEDIAPASSWORD='pwd2'

    (Paths and passwords are dummies for the example)

    My results:

    Msg 3279, Level 16, State 4, Line 1

    Access is denied due to a password failure

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    Msg 3279, Level 16, State 4, Line 3

    Access is denied due to a password failure

    Msg 3013, Level 16, State 1, Line 3

    RESTORE DATABASE is terminating abnormally.

    Trying to be optimistic, I am having the passwords verified by the owner. But it may just be that I am trying to do something wrong, or that should not be done.

    Should the above work? Any clues how I am screwing up?

  • Mike - CI

    SSCarpal Tunnel

    Points: 4146

    I just did a simple test with the following on my own machine and it worked fine.

    Backup Database test

    to disk='c:\Backups\test.bak'

    with Password = 'pwd1'

    Restore database test1

    from disk='c:\Backups\test.bak'

    With Password = 'pwd1',

    move 'test' to 'c:\data\test1.mdf',

    move 'test_log' to 'c:\data\test1_log.ldf'

    So I would guess your passwords are somehow wrong. You said you were verifying those, and that seems like the correct step to me.

  • SQL_Easy_btn?

    Right there with Babe

    Points: 770

    Have you tried...

    PASSWORD = { password | @password_variable }

    instead of

    MEDIAPASSWORD = { mediapassword | @mediapassword_variable }

    It may be a long shot but I am sure it wouldnt hurt to try it. Yes I know it should be exactly the same thing, but it is Microsoft we are talking about.

  • Mike - CI

    SSCarpal Tunnel

    Points: 4146

    SQL_Easy_btn? (1/14/2009)


    Have you tried...

    PASSWORD = { password | @password_variable }

    instead of

    MEDIAPASSWORD = { mediapassword | @mediapassword_variable }

    It may be a long shot but I am sure it wouldnt hurt to try it. Yes I know it should be exactly the same thing, but it is Microsoft we are talking about.

    Definitely not the same thing and I apologize for not realizing that he had mediapassword in their to begin with.

    In my example, I used password and it worked fine but substituting mediapassword will definitely fail.

    Good advice by SQL_Easy_btn.

  • Jim Russell-390299

    SSCrazy Eights

    Points: 9333

    Eureka!! Using PASSWORD = did the trick.

    What would I do without you guys!

    (Now I need to figure out a WITH MOVE clause, but that got me over hurdle #1)

    Thanks very much!

    (In hindsight MEDIAPASSWORD was dumb, but I can stand a little public shame to get the job done.)

  • sgmunson

    SSC Guru

    Points: 110437

    Just wondering, if one came across the password protected BAK file, and did not have the password, is one out of luck, or are there utilities out there that can reveal the password or perhaps render it moot?

    Steve

    (aka sgmunson)

    :-):-):-)

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • SQL_Easy_btn?

    Right there with Babe

    Points: 770

    You will need to go and buy a password cracker software that can sit and hammer at the password.

    But you will need the password when it comes down to it, SQL Server will have to pass it along to the pw protected file.

  • sqldbanaren

    SSC Rookie

    Points: 43

    I used following T-SQL Query to restore database which has password back up and it is successfully restored

    Restore database AdventureWorksDW

    from disk='D:\test.bak'

    With Password = 'pwd1'

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply