January 14, 2009 at 8:36 am
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?
January 14, 2009 at 8:53 am
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.
January 14, 2009 at 9:48 am
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.
January 14, 2009 at 9:57 am
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.
January 14, 2009 at 10:03 am
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.)
June 29, 2009 at 8:55 am
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 29, 2009 at 6:12 pm
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.
May 7, 2010 at 5:46 am
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply