2nd Try: Restore Database Dialog missing Backups (Archive BIT)

I have a strange problem with restoring files. I have one SQL server
installation that does not show any .BAK files that no longer have the
archive bit set. Is there a switch somewhere that I can throw to
change this back?
I have 3 other SQL server installations in different places that do
not have this issue.
Thanks,
Doug
"Doug Crabtree" <not@.gonna.tell> wrote in message
news:GPKdnbA9tLgxur_cRVn-rw@.comcast.com...
> I have a strange problem with restoring files. I have one SQL server
> installation that does not show any .BAK files that no longer have the
> archive bit set. Is there a switch somewhere that I can throw to
> change this back?
> I have 3 other SQL server installations in different places that do
> not have this issue.
You can change the archive bit from a command prompt with the ATTRIB
command. It sounds like you may have backup software running that is
resetting the bit. This should not prevent you from restoring any backup
files, perhaps there is something else going on here?
Steve
|||I can reset the bit, our backup is indeed changing it.
But the problem remains that the dialog that shows the backups does not show
those without the bit. Only 1 server does it. All our other servers show
it regardless of the archive bit. I was just hoping somebody out there knew
why this was happening.
Thanks,
Doug
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:%23qtYktIhEHA.2908@.TK2MSFTNGP10.phx.gbl...
> You can change the archive bit from a command prompt with the ATTRIB
> command. It sounds like you may have backup software running that is
> resetting the bit. This should not prevent you from restoring any backup
> files, perhaps there is something else going on here?
> Steve
>
|||AFAIK, the restore dialog doesn't care at all whether the backup files archive bit is set or not.
Actually, it doesn't even care whether the file in question *exists*. My guess is that your problem
is elsewhere. You might want to troubleshoot this by doing a backup, verify that EM show this, set
the archive bit and check EM again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Doug Crabtree" <not@.gonna.tell> wrote in message news:tcydnc2FZ7rZ_r_cRVn-vQ@.comcast.com...
> I can reset the bit, our backup is indeed changing it.
> But the problem remains that the dialog that shows the backups does not show
> those without the bit. Only 1 server does it. All our other servers show
> it regardless of the archive bit. I was just hoping somebody out there knew
> why this was happening.
> Thanks,
> Doug
> "Steve Thompson" <stevethompson@.nomail.please> wrote in message
> news:%23qtYktIhEHA.2908@.TK2MSFTNGP10.phx.gbl...
>
|||I have tried that. It dissapears from the selection dialog when the archive
bit is not set. When set, it appears again.
Strange...
Thanks anyway,
Doug
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ePeJe3OhEHA.3272@.TK2MSFTNGP11.phx.gbl...
> AFAIK, the restore dialog doesn't care at all whether the backup files
archive bit is set or not.
> Actually, it doesn't even care whether the file in question *exists*. My
guess is that your problem
> is elsewhere. You might want to troubleshoot this by doing a backup,
verify that EM show this, set
> the archive bit and check EM again.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
|||Strange indeed. Can you point me to exactly what dialog you refer to (how to you "click" to get
there) and I'll give it a spin. I've never encountered this, or even thought about the archive bit
being relevant...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Doug Crabtree" <not@.gonna.tell> wrote in message news:nOOdnVUDjKWVzr7cRVn-qA@.comcast.com...
> I have tried that. It dissapears from the selection dialog when the archive
> bit is not set. When set, it appears again.
> Strange...
> Thanks anyway,
> Doug
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:ePeJe3OhEHA.3272@.TK2MSFTNGP11.phx.gbl...
> archive bit is set or not.
> guess is that your problem
> verify that EM show this, set
>
|||In Enterprise Manager, Right click on Databases, and select All Tasks ->
Restore Database...
"Restore Database" Dialog Box
Restore as database: <<DBNAME>>
From Device selected
Restore backup set (Database - complete) selected
Click Select Devices button
"Choose Restore Devices" Dialog Box
Disk selected
Click Add button
"Choose Restore Destination" dialog box:
File Name (click the ...)
"Backup Device Location - <<SERVERNAME>>" Dialog box:
They don't show.
Interesting, though ... I copied one of the backups to another disk, turned
off the archive bit, and it showed there. Something about that drive?
Permissions? We have the Server running as an administrator's account.
Would that have anything to do with it?
Thanks,
Doug
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OtgmcOShEHA.1156@.TK2MSFTNGP10.phx.gbl...
> Strange indeed. Can you point me to exactly what dialog you refer to (how
to you "click" to get
> there) and I'll give it a spin. I've never encountered this, or even
thought about the archive bit
> being relevant...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
|||I tried your steps and I could not reproduce, the file did show, regardless of what I set the "the file is
ready for ..." option in Explorer (I assume this is the ol' archive bit).
I did a Profiler trace and it seems like EM is executing xp_dirtree to get this list. This might give you
something to go on when searching KB, Google etc.
Also, opening a case with MS might be an option.
I doubt is has anything to do with permissions. But who know...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Doug Crabtree" <not@.gonna.tell> wrote in message news:np-dnVZYZaxf9b7cRVn-ig@.comcast.com...
> In Enterprise Manager, Right click on Databases, and select All Tasks ->
> Restore Database...
> "Restore Database" Dialog Box
> Restore as database: <<DBNAME>>
> From Device selected
> Restore backup set (Database - complete) selected
> Click Select Devices button
> "Choose Restore Devices" Dialog Box
> Disk selected
> Click Add button
> "Choose Restore Destination" dialog box:
> File Name (click the ...)
> "Backup Device Location - <<SERVERNAME>>" Dialog box:
> They don't show.
> ----
> --
> Interesting, though ... I copied one of the backups to another disk, turned
> off the archive bit, and it showed there. Something about that drive?
> Permissions? We have the Server running as an administrator's account.
> Would that have anything to do with it?
> Thanks,
> Doug
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OtgmcOShEHA.1156@.TK2MSFTNGP10.phx.gbl...
> to you "click" to get
> thought about the archive bit
>
|||Interesting problem indeed -- can you use the "native" T-SQL RESTORE to
perform the database restore?
Steve
"Doug Crabtree" <not@.gonna.tell> wrote in message
news:np-dnVZYZaxf9b7cRVn-ig@.comcast.com...
> In Enterprise Manager, Right click on Databases, and select All Tasks ->
> Restore Database...
> "Restore Database" Dialog Box
> Restore as database: <<DBNAME>>
> From Device selected
> Restore backup set (Database - complete) selected
> Click Select Devices button
> "Choose Restore Devices" Dialog Box
> Disk selected
> Click Add button
> "Choose Restore Destination" dialog box:
> File Name (click the ...)
> "Backup Device Location - <<SERVERNAME>>" Dialog box:
> They don't show.
> ----
--
> --
> Interesting, though ... I copied one of the backups to another disk,
turned
> off the archive bit, and it showed there. Something about that drive?
> Permissions? We have the Server running as an administrator's account.
> Would that have anything to do with it?
> Thanks,
> Doug
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:OtgmcOShEHA.1156@.TK2MSFTNGP10.phx.gbl...
(how
> to you "click" to get
> thought about the archive bit
>
|||I can acutally just type in the name of the file in the "Choose Restore
Destination" Dialog Box. It will restore it, they just don't show in the
dir tree.
Doug
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:uxhb6pUhEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Interesting problem indeed -- can you use the "native" T-SQL RESTORE to
> perform the database restore?
> Steve

2nd Try: Restore Database Dialog missing Backups (Archive BIT)

I have a strange problem with restoring files. I have one SQL server
installation that does not show any .BAK files that no longer have the
archive bit set. Is there a switch somewhere that I can throw to
change this back?
I have 3 other SQL server installations in different places that do
not have this issue.
Thanks,
Doug"Doug Crabtree" <not@.gonna.tell> wrote in message
news:GPKdnbA9tLgxur_cRVn-rw@.comcast.com...
> I have a strange problem with restoring files. I have one SQL server
> installation that does not show any .BAK files that no longer have the
> archive bit set. Is there a switch somewhere that I can throw to
> change this back?
> I have 3 other SQL server installations in different places that do
> not have this issue.
You can change the archive bit from a command prompt with the ATTRIB
command. It sounds like you may have backup software running that is
resetting the bit. This should not prevent you from restoring any backup
files, perhaps there is something else going on here?
Steve|||I can reset the bit, our backup is indeed changing it.
But the problem remains that the dialog that shows the backups does not show
those without the bit. Only 1 server does it. All our other servers show
it regardless of the archive bit. I was just hoping somebody out there knew
why this was happening.
Thanks,
Doug
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:%23qtYktIhEHA.2908@.TK2MSFTNGP10.phx.gbl...
> You can change the archive bit from a command prompt with the ATTRIB
> command. It sounds like you may have backup software running that is
> resetting the bit. This should not prevent you from restoring any backup
> files, perhaps there is something else going on here?
> Steve
>|||AFAIK, the restore dialog doesn't care at all whether the backup files archive bit is set or not.
Actually, it doesn't even care whether the file in question *exists*. My guess is that your problem
is elsewhere. You might want to troubleshoot this by doing a backup, verify that EM show this, set
the archive bit and check EM again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Doug Crabtree" <not@.gonna.tell> wrote in message news:tcydnc2FZ7rZ_r_cRVn-vQ@.comcast.com...
> I can reset the bit, our backup is indeed changing it.
> But the problem remains that the dialog that shows the backups does not show
> those without the bit. Only 1 server does it. All our other servers show
> it regardless of the archive bit. I was just hoping somebody out there knew
> why this was happening.
> Thanks,
> Doug
> "Steve Thompson" <stevethompson@.nomail.please> wrote in message
> news:%23qtYktIhEHA.2908@.TK2MSFTNGP10.phx.gbl...
> > You can change the archive bit from a command prompt with the ATTRIB
> > command. It sounds like you may have backup software running that is
> > resetting the bit. This should not prevent you from restoring any backup
> > files, perhaps there is something else going on here?
> >
> > Steve
> >
> >
>|||I have tried that. It dissapears from the selection dialog when the archive
bit is not set. When set, it appears again.
Strange...
Thanks anyway,
Doug
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ePeJe3OhEHA.3272@.TK2MSFTNGP11.phx.gbl...
> AFAIK, the restore dialog doesn't care at all whether the backup files
archive bit is set or not.
> Actually, it doesn't even care whether the file in question *exists*. My
guess is that your problem
> is elsewhere. You might want to troubleshoot this by doing a backup,
verify that EM show this, set
> the archive bit and check EM again.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/|||Strange indeed. Can you point me to exactly what dialog you refer to (how to you "click" to get
there) and I'll give it a spin. I've never encountered this, or even thought about the archive bit
being relevant...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Doug Crabtree" <not@.gonna.tell> wrote in message news:nOOdnVUDjKWVzr7cRVn-qA@.comcast.com...
> I have tried that. It dissapears from the selection dialog when the archive
> bit is not set. When set, it appears again.
> Strange...
> Thanks anyway,
> Doug
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:ePeJe3OhEHA.3272@.TK2MSFTNGP11.phx.gbl...
> > AFAIK, the restore dialog doesn't care at all whether the backup files
> archive bit is set or not.
> > Actually, it doesn't even care whether the file in question *exists*. My
> guess is that your problem
> > is elsewhere. You might want to troubleshoot this by doing a backup,
> verify that EM show this, set
> > the archive bit and check EM again.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
>|||In Enterprise Manager, Right click on Databases, and select All Tasks ->
Restore Database...
"Restore Database" Dialog Box
Restore as database: <<DBNAME>>
From Device selected
Restore backup set (Database - complete) selected
Click Select Devices button
"Choose Restore Devices" Dialog Box
Disk selected
Click Add button
"Choose Restore Destination" dialog box:
File Name (click the ...)
"Backup Device Location - <<SERVERNAME>>" Dialog box:
They don't show.
----
--
Interesting, though ... I copied one of the backups to another disk, turned
off the archive bit, and it showed there. Something about that drive?
Permissions? We have the Server running as an administrator's account.
Would that have anything to do with it?
Thanks,
Doug
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OtgmcOShEHA.1156@.TK2MSFTNGP10.phx.gbl...
> Strange indeed. Can you point me to exactly what dialog you refer to (how
to you "click" to get
> there) and I'll give it a spin. I've never encountered this, or even
thought about the archive bit
> being relevant...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/|||I tried your steps and I could not reproduce, the file did show, regardless of what I set the "the file is
ready for ..." option in Explorer (I assume this is the ol' archive bit).
I did a Profiler trace and it seems like EM is executing xp_dirtree to get this list. This might give you
something to go on when searching KB, Google etc.
Also, opening a case with MS might be an option.
I doubt is has anything to do with permissions. But who know...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Doug Crabtree" <not@.gonna.tell> wrote in message news:np-dnVZYZaxf9b7cRVn-ig@.comcast.com...
> In Enterprise Manager, Right click on Databases, and select All Tasks ->
> Restore Database...
> "Restore Database" Dialog Box
> Restore as database: <<DBNAME>>
> From Device selected
> Restore backup set (Database - complete) selected
> Click Select Devices button
> "Choose Restore Devices" Dialog Box
> Disk selected
> Click Add button
> "Choose Restore Destination" dialog box:
> File Name (click the ...)
> "Backup Device Location - <<SERVERNAME>>" Dialog box:
> They don't show.
> ----
> --
> Interesting, though ... I copied one of the backups to another disk, turned
> off the archive bit, and it showed there. Something about that drive?
> Permissions? We have the Server running as an administrator's account.
> Would that have anything to do with it?
> Thanks,
> Doug
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OtgmcOShEHA.1156@.TK2MSFTNGP10.phx.gbl...
> > Strange indeed. Can you point me to exactly what dialog you refer to (how
> to you "click" to get
> > there) and I'll give it a spin. I've never encountered this, or even
> thought about the archive bit
> > being relevant...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
>|||Interesting problem indeed -- can you use the "native" T-SQL RESTORE to
perform the database restore?
Steve
"Doug Crabtree" <not@.gonna.tell> wrote in message
news:np-dnVZYZaxf9b7cRVn-ig@.comcast.com...
> In Enterprise Manager, Right click on Databases, and select All Tasks ->
> Restore Database...
> "Restore Database" Dialog Box
> Restore as database: <<DBNAME>>
> From Device selected
> Restore backup set (Database - complete) selected
> Click Select Devices button
> "Choose Restore Devices" Dialog Box
> Disk selected
> Click Add button
> "Choose Restore Destination" dialog box:
> File Name (click the ...)
> "Backup Device Location - <<SERVERNAME>>" Dialog box:
> They don't show.
> ----
--
> --
> Interesting, though ... I copied one of the backups to another disk,
turned
> off the archive bit, and it showed there. Something about that drive?
> Permissions? We have the Server running as an administrator's account.
> Would that have anything to do with it?
> Thanks,
> Doug
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OtgmcOShEHA.1156@.TK2MSFTNGP10.phx.gbl...
> > Strange indeed. Can you point me to exactly what dialog you refer to
(how
> to you "click" to get
> > there) and I'll give it a spin. I've never encountered this, or even
> thought about the archive bit
> > being relevant...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
>|||I can acutally just type in the name of the file in the "Choose Restore
Destination" Dialog Box. It will restore it, they just don't show in the
dir tree.
Doug
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:uxhb6pUhEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Interesting problem indeed -- can you use the "native" T-SQL RESTORE to
> perform the database restore?
> Steve

2nd Try: Restore Database Dialog missing Backups (Archive BIT)

I have a strange problem with restoring files. I have one SQL server
installation that does not show any .BAK files that no longer have the
archive bit set. Is there a switch somewhere that I can throw to
change this back?
I have 3 other SQL server installations in different places that do
not have this issue.
Thanks,
Doug"Doug Crabtree" <not@.gonna.tell> wrote in message
news:GPKdnbA9tLgxur_cRVn-rw@.comcast.com...
> I have a strange problem with restoring files. I have one SQL server
> installation that does not show any .BAK files that no longer have the
> archive bit set. Is there a switch somewhere that I can throw to
> change this back?
> I have 3 other SQL server installations in different places that do
> not have this issue.
You can change the archive bit from a command prompt with the ATTRIB
command. It sounds like you may have backup software running that is
resetting the bit. This should not prevent you from restoring any backup
files, perhaps there is something else going on here?
Steve|||I can reset the bit, our backup is indeed changing it.
But the problem remains that the dialog that shows the backups does not show
those without the bit. Only 1 server does it. All our other servers show
it regardless of the archive bit. I was just hoping somebody out there knew
why this was happening.
Thanks,
Doug
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:%23qtYktIhEHA.2908@.TK2MSFTNGP10.phx.gbl...
> You can change the archive bit from a command prompt with the ATTRIB
> command. It sounds like you may have backup software running that is
> resetting the bit. This should not prevent you from restoring any backup
> files, perhaps there is something else going on here?
> Steve
>|||AFAIK, the restore dialog doesn't care at all whether the backup files archi
ve bit is set or not.
Actually, it doesn't even care whether the file in question *exists*. My gue
ss is that your problem
is elsewhere. You might want to troubleshoot this by doing a backup, verify
that EM show this, set
the archive bit and check EM again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Doug Crabtree" <not@.gonna.tell> wrote in message news:tcydnc2FZ7rZ_r_cRVn-vQ@.comcast.com...

> I can reset the bit, our backup is indeed changing it.
> But the problem remains that the dialog that shows the backups does not sh
ow
> those without the bit. Only 1 server does it. All our other servers show
> it regardless of the archive bit. I was just hoping somebody out there kn
ew
> why this was happening.
> Thanks,
> Doug
> "Steve Thompson" <stevethompson@.nomail.please> wrote in message
> news:%23qtYktIhEHA.2908@.TK2MSFTNGP10.phx.gbl...
>|||I have tried that. It dissapears from the selection dialog when the archive
bit is not set. When set, it appears again.
Strange...
Thanks anyway,
Doug
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ePeJe3OhEHA.3272@.TK2MSFTNGP11.phx.gbl...
> AFAIK, the restore dialog doesn't care at all whether the backup files
archive bit is set or not.
> Actually, it doesn't even care whether the file in question *exists*. My
guess is that your problem
> is elsewhere. You might want to troubleshoot this by doing a backup,
verify that EM show this, set
> the archive bit and check EM again.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/|||Strange indeed. Can you point me to exactly what dialog you refer to (how to
you "click" to get
there) and I'll give it a spin. I've never encountered this, or even thought
about the archive bit
being relevant...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Doug Crabtree" <not@.gonna.tell> wrote in message news:nOOdnVUDjKWVzr7cRVn-qA@.comcast.com...

> I have tried that. It dissapears from the selection dialog when the archi
ve
> bit is not set. When set, it appears again.
> Strange...
> Thanks anyway,
> Doug
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:ePeJe3OhEHA.3272@.TK2MSFTNGP11.phx.gbl...
> archive bit is set or not.
> guess is that your problem
> verify that EM show this, set
>|||In Enterprise Manager, Right click on Databases, and select All Tasks ->
Restore Database...
"Restore Database" Dialog Box
Restore as database: <<DBNAME>>
From Device selected
Restore backup set (Database - complete) selected
Click Select Devices button
"Choose Restore Devices" Dialog Box
Disk selected
Click Add button
"Choose Restore Destination" dialog box:
File Name (click the ...)
"Backup Device Location - <<SERVERNAME>>" Dialog box:
They don't show.
----
--
Interesting, though ... I copied one of the backups to another disk, turned
off the archive bit, and it showed there. Something about that drive?
Permissions? We have the Server running as an administrator's account.
Would that have anything to do with it?
Thanks,
Doug
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OtgmcOShEHA.1156@.TK2MSFTNGP10.phx.gbl...
> Strange indeed. Can you point me to exactly what dialog you refer to (how
to you "click" to get
> there) and I'll give it a spin. I've never encountered this, or even
thought about the archive bit
> being relevant...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/|||I tried your steps and I could not reproduce, the file did show, regardless
of what I set the "the file is
ready for ..." option in Explorer (I assume this is the ol' archive bit).
I did a Profiler trace and it seems like EM is executing xp_dirtree to get t
his list. This might give you
something to go on when searching KB, Google etc.
Also, opening a case with MS might be an option.
I doubt is has anything to do with permissions. But who know...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Doug Crabtree" <not@.gonna.tell> wrote in message news:np-dnVZYZaxf9b7cRVn-ig@.comcast.com...

> In Enterprise Manager, Right click on Databases, and select All Tasks ->
> Restore Database...
> "Restore Database" Dialog Box
> Restore as database: <<DBNAME>>
> From Device selected
> Restore backup set (Database - complete) selected
> Click Select Devices button
> "Choose Restore Devices" Dialog Box
> Disk selected
> Click Add button
> "Choose Restore Destination" dialog box:
> File Name (click the ...)
> "Backup Device Location - <<SERVERNAME>>" Dialog box:
> They don't show.
> ----
--
> --
> Interesting, though ... I copied one of the backups to another disk, turne
d
> off the archive bit, and it showed there. Something about that drive?
> Permissions? We have the Server running as an administrator's account.
> Would that have anything to do with it?
> Thanks,
> Doug
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:OtgmcOShEHA.1156@.TK2MSFTNGP10.phx.gbl...
> to you "click" to get
> thought about the archive bit
>|||Interesting problem indeed -- can you use the "native" T-SQL RESTORE to
perform the database restore?
Steve
"Doug Crabtree" <not@.gonna.tell> wrote in message
news:np-dnVZYZaxf9b7cRVn-ig@.comcast.com...
> In Enterprise Manager, Right click on Databases, and select All Tasks ->
> Restore Database...
> "Restore Database" Dialog Box
> Restore as database: <<DBNAME>>
> From Device selected
> Restore backup set (Database - complete) selected
> Click Select Devices button
> "Choose Restore Devices" Dialog Box
> Disk selected
> Click Add button
> "Choose Restore Destination" dialog box:
> File Name (click the ...)
> "Backup Device Location - <<SERVERNAME>>" Dialog box:
> They don't show.
> ----
--
> --
> Interesting, though ... I copied one of the backups to another disk,
turned
> off the archive bit, and it showed there. Something about that drive?
> Permissions? We have the Server running as an administrator's account.
> Would that have anything to do with it?
> Thanks,
> Doug
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OtgmcOShEHA.1156@.TK2MSFTNGP10.phx.gbl...
(how[vbcol=seagreen]
> to you "click" to get
> thought about the archive bit
>|||I can acutally just type in the name of the file in the "Choose Restore
Destination" Dialog Box. It will restore it, they just don't show in the
dir tree.
Doug
"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:uxhb6pUhEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Interesting problem indeed -- can you use the "native" T-SQL RESTORE to
> perform the database restore?
> Steve

2005-Problem restoring database with encrypted columns to diff ser

I need to start encrypting several fields in a database and have been doing
some testing with a test database first. I've run into problems when
attempting to restore the database on either the same server (but different
database) or to a separate server.
First, here's how i created the symmetric key and encrypted data in the
original database:
create master key
encryption by password = 'testAppleA3';
I also saw a posting here where it referenced running the below first:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;
However when i then try to open the key it stil gives an error:
An error occurred during decryption.
create certificate test
with subject = 'test certificate',
EXPIRY_DATE = '1/1/2010';
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
open symmetric key sk_Test decryption by certificate test;
insert into employees values (101,'Jane
Doe',encryptbykey(key_guid('sk_Test'),'$
200000'));
insert into employees values(102,'Bob
Jones',encryptbykey(key_guid('sk_Test'),
'$500000'));
select * from employees
--delete from employees
select id,name,cast(decryptbykey(salary) as varchar(10)) as salary from
employees
close all symmetric keys
Next I backup up this test database and restore it to a new database on a
different server (same issue if restore to different database but on same
server).
Then if i attempt to open the key in the new database and decrypt:
open symmetric key sk_Test decryption by certificate test;
I get the error: An error occurred during decryption.
Ok, well not unexpected, so reading the forums, i try doing the below first
in the new database:
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Then I try opening the key again and get the error again:
An error occurred during decryption.
So then it occurs to me, maybe i need to drop and recreate it so i do
drop symmetric key sk_test
then
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
and then try to open it.
Same error!
So then i decide, let's drop everything, the master key, the certificate and
then symmetric key:
drop symmetric key sk_test
drop certificate test
drop master key
Then recreate the master key:
create master key
encryption by password = 'testAppleA3';
Restore the certificate from a backup i had made to a file:
CREATE CERTIFICATE test
FROM FILE = 'c:\storedcerts\encryptiontestcert'
Recreate the symmetric key again:
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
And now open the key only to get the error:
Cannot decrypt or encrypt using the specified certificate, either because it
has no private key or because the password provided for the private key is
incorrect.
So what am I doing wrong here? In this scenario I would appear to have lost
all access to decrypt the data in the database despite restoring from a
backup which restored the symmetric key and certificate and i obviously know
the password for the master key.
I also tried running the command
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
again but this does not resolve the issue.So now I'm confused.
Where this did not work is when i created the intial database using SQL
Server Express edition on a Win XP workstation and then was restoring to a
database located on a SQL Server Standard edition on a Windows 2003 Server.
I just tried creating everything from scratch on one WIndows 2003 Server
with SQL Server Standard edition and then restoring that database to a
different Windows 2003 Server also with SQL Server Standard and this time it
worked as long as i did the below first:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;
My question (comes to reliabilty concerns) is why did it not work when i did
:
Orig database SQLSvr Express on Win XP
Restore to SQL Svr Std on Win2003Svr
But did work when I did
Orig database SQL Svr Std on Win 2003Svr
Restore to different SQL Svr Std on different Win 2003 Svr
What would the reason for this be?
Thx.
"Robert" wrote:

> I need to start encrypting several fields in a database and have been doin
g
> some testing with a test database first. I've run into problems when
> attempting to restore the database on either the same server (but differen
t
> database) or to a separate server.
> First, here's how i created the symmetric key and encrypted data in the
> original database:
> create master key
> encryption by password = 'testAppleA3';
> I also saw a posting here where it referenced running the below first:
> OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3';
> ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
> CLOSE MASTER KEY;
> However when i then try to open the key it stil gives an error:
> An error occurred during decryption.
>
> create certificate test
> with subject = 'test certificate',
> EXPIRY_DATE = '1/1/2010';
> create symmetric key sk_Test
> with algorithm = triple_des
> encryption by certificate test;
> open symmetric key sk_Test decryption by certificate test;
> insert into employees values (101,'Jane
> Doe',encryptbykey(key_guid('sk_Test'),'$
200000'));
> insert into employees values(102,'Bob
> Jones',encryptbykey(key_guid('sk_Test'),
'$500000'));
> select * from employees
> --delete from employees
> select id,name,cast(decryptbykey(salary) as varchar(10)) as salary from
> employees
> close all symmetric keys
> Next I backup up this test database and restore it to a new database on a
> different server (same issue if restore to different database but on same
> server).
> Then if i attempt to open the key in the new database and decrypt:
> open symmetric key sk_Test decryption by certificate test;
> I get the error: An error occurred during decryption.
> Ok, well not unexpected, so reading the forums, i try doing the below firs
t
> in the new database:
> ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
> Then I try opening the key again and get the error again:
> An error occurred during decryption.
> So then it occurs to me, maybe i need to drop and recreate it so i do
> drop symmetric key sk_test
> then
> create symmetric key sk_Test
> with algorithm = triple_des
> encryption by certificate test;
> and then try to open it.
> Same error!
> So then i decide, let's drop everything, the master key, the certificate a
nd
> then symmetric key:
> drop symmetric key sk_test
> drop certificate test
> drop master key
> Then recreate the master key:
> create master key
> encryption by password = 'testAppleA3';
> Restore the certificate from a backup i had made to a file:
> CREATE CERTIFICATE test
> FROM FILE = 'c:\storedcerts\encryptiontestcert'
> Recreate the symmetric key again:
> create symmetric key sk_Test
> with algorithm = triple_des
> encryption by certificate test;
> And now open the key only to get the error:
> Cannot decrypt or encrypt using the specified certificate, either because
it
> has no private key or because the password provided for the private key is
> incorrect.
> So what am I doing wrong here? In this scenario I would appear to have lo
st
> all access to decrypt the data in the database despite restoring from a
> backup which restored the symmetric key and certificate and i obviously kn
ow
> the password for the master key.
> I also tried running the command
> ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
> again but this does not resolve the issue.
>|||Hello Robert,
I was not able to reproduce the issue on my side.
On a SQL Express instance, create a database/table, Master key,
Certificate, symmetric key and insert the record with encrypted column.
Backup/restore to the SQL Enterprise edition instance. I could run the
following query successfully on the restored database:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3';
open symmetric key sk_Test decryption by certificate test;
It seems the issue is related to SQL service account. Is the service
account the same on both SQL Express and SQL Enterprise instances?
Master key protected by old service account credentials may need to be
protected by the current one. You can use the alter service master key
statement to enter the old credentials and have it be protected by the
current account.
Also, you may want to use same Windows account to start SQL instances to
test the situation.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: 2005-Problem restoring database with encrypted columns to
diff ser
>thread-index: AcYgUZjYietJilQUTrOvZ82WtyHCWw==
>X-WBNR-Posting-Host: 159.37.7.48
>From: examnotes <robertv@.noemail.nospam>
>References: <4CB8E9AA-99DF-44A1-8375-BDAB8B69A586@.microsoft.com>
>Subject: RE: 2005-Problem restoring database with encrypted columns to
diff ser
>Date: Mon, 23 Jan 2006 11:17:05 -0800
>Lines: 156
>Message-ID: <E0B161AC-6911-45DE-8100-C42FA028BB1F@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.security
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.security:26209
>X-Tomcat-NG: microsoft.public.sqlserver.security
>So now I'm confused.
>Where this did not work is when i created the intial database using SQL
>Server Express edition on a Win XP workstation and then was restoring to a
>database located on a SQL Server Standard edition on a Windows 2003 Server.
>I just tried creating everything from scratch on one WIndows 2003 Server
>with SQL Server Standard edition and then restoring that database to a
>different Windows 2003 Server also with SQL Server Standard and this time
it
>worked as long as i did the below first:
>OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3';
>ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
>CLOSE MASTER KEY;
>My question (comes to reliabilty concerns) is why did it not work when i
did:
>Orig database SQLSvr Express on Win XP
>Restore to SQL Svr Std on Win2003Svr
>But did work when I did
>Orig database SQL Svr Std on Win 2003Svr
>Restore to different SQL Svr Std on different Win 2003 Svr
>What would the reason for this be?
>Thx.
>
>"Robert" wrote:
>
doing[vbcol=seagreen]
different[vbcol=seagreen]
a[vbcol=seagreen]
same[vbcol=seagreen]
first[vbcol=seagreen]
and[vbcol=seagreen]
because it[vbcol=seagreen]
is[vbcol=seagreen]
lost[vbcol=seagreen]
know[vbcol=seagreen]
>

2005 keeps telling me the dababase is restoring

Hello all,

I have been seeing problems in restoring database to SQL Server 2005. The source is a .bak file backuped from SQL 2000. Everything in the process went well and at last the restore window told me the resotre has been successfully complete. But in management studio, it keeps telling me the database is restoring, I can not access it or open the property page. I tried the process many times already.

This occured several times in my Sep CTP platform so I removed it and installed the released version, but no good.

My environment: win xp sp2, SQL 2005

Any ideas?

When you restore the db, make sure you specify WITH NORECOVERY. This keeps the system from attempting to rollback uncommitted transactions.