backup\restore password?

How and where in the GUI (Enterprise Manager) can I specify a password for
restoring databases?
Like this:
1. Create db backup job
2. set pwd
3. restore db, by specifying the pwd set in step 2
Please, if possible, show me a screen shot.
Thanks!
MagnusYou can't do it through Enterprise Manager. You need to use
T-SQL backup, restore commands. Or use SQL-DMO which also
exposes the password property through the Backup2 object
(MediaPassword property).
-Sue
On Tue, 2 May 2006 15:57:37 +0300, "Magnus sterberg"
<magnus_osterberg@.hotmail.com> wrote:

>How and where in the GUI (Enterprise Manager) can I specify a password for
>restoring databases?
>Like this:
>1. Create db backup job
>2. set pwd
>3. restore db, by specifying the pwd set in step 2
>Please, if possible, show me a screen shot.
>Thanks!
>Magnus
>|||In QA, you can make a T-SQL using password.
BACKUP DATABASE Northwind TO MyNwind_1
WITH PASSWORD='password'
RESTORE DATABASE MyNwind FROM MyNwind_1
WITH PASSWORD='password'
"Magnus ?sterberg"?? ??? ??:

> How and where in the GUI (Enterprise Manager) can I specify a password for
> restoring databases?
> Like this:
> 1. Create db backup job
> 2. set pwd
> 3. restore db, by specifying the pwd set in step 2
> Please, if possible, show me a screen shot.
> Thanks!
> Magnus
>
>|||OK, now I found out how to add this to my SQL Server Agent backup job. But..
.the pwd is stored as a part of the SQL statement in plain text. Any sa-user
can easily check up the pwd by opening the job.
Mayby one can also schedule the job by T-SQL? As in:
BACKUP DATABASE [ABC_XZY] TO DISK = N'C:\ABCDB_BACKUPS\ABC_XZY' WITH I
NIT , NOUNLOAD , NAME = N'ABC_XZY', NOSKIP , STATS = 10, NOFORMAT, PASS
WORD='sjkf7wed8', SCHEDULE=daily, 21:00
...sounds a bit far-fetched. But my idea is that I could execute the T-SQL
statement once, which should store the job in the DBMSS, without allowing an
yone to see the pwd. Possible?
Mayby I have to take care of the scheduling myself?
Magnus
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message news:fjre5254k8q0e06t68tnmrhthsigu70
57u@.4ax.com...
> You can't do it through Enterprise Manager. You need to use
> T-SQL backup, restore commands. Or use SQL-DMO which also
> exposes the password property through the Backup2 object
> (MediaPassword property).
>
> -Sue
>
> On Tue, 2 May 2006 15:57:37 +0300, "Magnus sterberg"
> <magnus_osterberg@.hotmail.com> wrote:
>
>|||anyone got an answer to this one'
"Magnus sterberg" <magnus_osterberg@.hotmail.com> wrote in message news:u2XH
eGobGHA.4040@.TK2MSFTNGP02.phx.gbl...
OK, now I found out how to add this to my SQL Server Agent backup job. But..
.the pwd is stored as a part of the SQL statement in plain text. Any sa-user
can easily check up the pwd by opening the job.
Mayby one can also schedule the job by T-SQL? As in:
BACKUP DATABASE [ABC_XZY] TO DISK = N'C:\ABCDB_BACKUPS\ABC_XZY' WITH I
NIT , NOUNLOAD , NAME = N'ABC_XZY', NOSKIP , STATS = 10, NOFORMAT, PASS
WORD='sjkf7wed8', SCHEDULE=daily, 21:00
..sounds a bit far-fetched. But my idea is that I could execute the T-SQL s
tatement once, which should store the job in the DBMSS, without allowing any
one to see the pwd. Possible?
Mayby I have to take care of the scheduling myself?
Magnus
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message news:fjre5254k8q0e06t68tnmrhthsigu70
57u@.4ax.com...
> You can't do it through Enterprise Manager. You need to use
> T-SQL backup, restore commands. Or use SQL-DMO which also
> exposes the password property through the Backup2 object
> (MediaPassword property).
>
> -Sue
>
> On Tue, 2 May 2006 15:57:37 +0300, "Magnus sterberg"
> <magnus_osterberg@.hotmail.com> wrote:
>
>|||Can't do that. Even if you could execute a statement once and have it
lie dormant inside the DBMS somehow, a sysadmin can still look in the
proc cache and see SQL statements & also at the current processes to see
what statements are running (including any sensitive information
included in the statement batch).
The closest you could come to that with pure SQL (and not some 3rd party
tool) would be to write a stored proc that contains an infinite WHILE
loop (eg. "WHILE 1=1") and in the loop you'd have the backup statement
and a "WAITFOR TIME '2:00:00'" statement (see BOL), which would put the
thread to sleep until 2am for example at which time the thread would
wake up, do the backup, loop and go to sleep again. Then you'd get SQL
Server to run the proc on start up (see sp_procoption in BOL) and you
could create the proc with the WITH ENCRYPTION option to make it a
little harder to see the password embedded in the proc. However, a
sysadmin would still be able to see that T-SQL code by querying the
appropriate system metadata (proc cache and/or current processes).
What's the point of password protecting a backup so that the sysadmins
on the box cannot restore that database when they can see the real
online live version of the DB anyway? Seems like not a very credible
situation. Why would you make a login a sysadmin if they're not allowed
to see the data in one of the databases? Isn't that the point of NDAs
(non-disclosure agreements) for DBAs?
*mike hodgson*
http://sqlnerd.blogspot.com
Magnus sterberg wrote:

> anyone got an answer to this one'
>
> "Magnus sterberg" <magnus_osterberg@.hotmail.com
> <mailto:magnus_osterberg@.hotmail.com>> wrote in message
> news:u2XHeGobGHA.4040@.TK2MSFTNGP02.phx.gbl...
> OK, now I found out how to add this to my SQL Server Agent backup
> job. But...the pwd is stored as a part of the SQL statement in
> plain text. Any sa-user can easily check up the pwd by opening the
> job.
> Mayby one can also schedule the job by T-SQL? As in:
> BACKUP DATABASE [ABC_XZY] TO DISK = N'C:\ABCDB_BACKUPS\ABC_XZY'
> WITH INIT , NOUNLOAD , NAME = N'ABC_XZY', NOSKIP , STATS =
> 10, NOFORMAT, PASSWORD='sjkf7wed8', *SCHEDULE=daily, 21:00*
> **
> ...sounds a bit far-fetched. But my idea is that I could execute
> the T-SQL statement once, which should store the job in the DBMSS,
> without allowing anyone to see the pwd. Possible?
> Mayby I have to take care of the scheduling myself?
>
> Magnus
> "Sue Hoegemeier" <Sue_H@.nomail.please
> <mailto:Sue_H@.nomail.please>> wrote in message
> news:fjre5254k8q0e06t68tnmrhthsigu7057u@.
4ax.com...
> <mailto:magnus_osterberg@.hotmail.com>> wrote:
> password for
>