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

22 GB database copy between servers

We have a process that does a production backup, copy .bak
to the dev environment, then restore.
Once we crossed the 21 GB .bak size, the copy process
started failing because of the size of the file. I found
the knowledge base article where this was explained.
How do you get around this? BCP the database out by table?
DTS package to export data? What works for you?It sounds as though your network may not be up to par. I have copied =40GB across our network with ease.
You mention that you found a KB article. What article are you talking =about?
-- Keith
"Jay" <anonymous@.discussions.microsoft.com> wrote in message =news:1cee901c422e6$6f7445d0$a401280a@.phx.gbl...
> We have a process that does a production backup, copy .bak > to the dev environment, then restore.
> > Once we crossed the 21 GB .bak size, the copy process > started failing because of the size of the file. I found > the knowledge base article where this was explained.
> > How do you get around this? BCP the database out by table? > DTS package to export data? What works for you?|||Hi,
If the normal copy doesn't work you can always try to FTP it over your
network. This is more stable than the normal copy. (In my experience.)
Greets,
Jo Segers.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> schreef in bericht
news:%23x250huIEHA.2556@.TK2MSFTNGP12.phx.gbl...
It sounds as though your network may not be up to par. I have copied 40GB
across our network with ease.
You mention that you found a KB article. What article are you talking
about?
--
Keith
"Jay" <anonymous@.discussions.microsoft.com> wrote in message
news:1cee901c422e6$6f7445d0$a401280a@.phx.gbl...
> We have a process that does a production backup, copy .bak
> to the dev environment, then restore.
> Once we crossed the 21 GB .bak size, the copy process
> started failing because of the size of the file. I found
> the knowledge base article where this was explained.
> How do you get around this? BCP the database out by table?
> DTS package to export data? What works for you?|||What was the copy process that you used? Robocopy?
http://support.microsoft.com/default.aspx?scid=kb;en-
us;259837
MORE INFORMATION
The CopyFileEx and CopyFile functions are used by all of
the standard file-copy utilities that are included with
Windows NT 4.0 and Windows 2000. These utilities include
COPY, XCOPY, and Explorer. The resource kit utility,
Robocopy.exe, also uses the CopyFileEx function;
therefore, all of these tools have the same limitation.
Because these functions use buffered I/O, the maximum size
of a file that you can copy depends on the amount of paged-
pool memory that is available at the start of the copy
procedure and on whether the Physical Address Extensions
(PAE) kernel (which allows access to memory above 4
gigabytes [GB]) is in use.
With the standard kernel, 1 kilobyte (KB) of paged pool is
required for each megabyte (MB) of file size that is
opened for buffered I/O. Because there are two files
involved in a copy procedure, 2 KB of paged pool are
required for each MB in the source file. When the PAE
kernel is used, the overhead is doubled and 4 KB of paged
pool is required for each MB in the source file.
Service Pack 2 (SP2) for Windows 2000 contains a fix for
this limitation. The kernel memory manager has been
altered with respect to management of paged pool memory
when files are opened for buffered I/O. This change
permits buffered operations on arbitrarily large files and
therefore permits arbitrarily large files to be copied by
any utility that uses the CopyFileEx function.|||Hi,
BCP OUT / BCP IN , DTS will take long execution time. I feel that you can
zip the file after backup and then copy the
zip file to development server and then unzip and restore the database. This
will be the easy and best approach.
Steps:
1. Backup the database
2. Zip the file
3. copy to destination
4. Unzip the file
5. Restore it
Thanks
Hari
MCDBA
"Jay" <anonymous@.discussions.microsoft.com> wrote in message
news:1cee901c422e6$6f7445d0$a401280a@.phx.gbl...
> We have a process that does a production backup, copy .bak
> to the dev environment, then restore.
> Once we crossed the 21 GB .bak size, the copy process
> started failing because of the size of the file. I found
> the knowledge base article where this was explained.
> How do you get around this? BCP the database out by table?
> DTS package to export data? What works for you?|||Or WINRAR which performs much faster than WINZIP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OgxYbouIEHA.2924@.TK2MSFTNGP09.phx.gbl...
> Hi,
> BCP OUT / BCP IN , DTS will take long execution time. I feel that you can
> zip the file after backup and then copy the
> zip file to development server and then unzip and restore the database.
This
> will be the easy and best approach.
>
> Steps:
> 1. Backup the database
> 2. Zip the file
> 3. copy to destination
> 4. Unzip the file
> 5. Restore it
>
> Thanks
> Hari
> MCDBA
> "Jay" <anonymous@.discussions.microsoft.com> wrote in message
> news:1cee901c422e6$6f7445d0$a401280a@.phx.gbl...
> > We have a process that does a production backup, copy .bak
> > to the dev environment, then restore.
> >
> > Once we crossed the 21 GB .bak size, the copy process
> > started failing because of the size of the file. I found
> > the knowledge base article where this was explained.
> >
> > How do you get around this? BCP the database out by table?
> > DTS package to export data? What works for you?
>

22 GB database copy between servers

We have a process that does a production backup, copy .bak
to the dev environment, then restore.
Once we crossed the 21 GB .bak size, the copy process
started failing because of the size of the file. I found
the knowledge base article where this was explained.
How do you get around this? BCP the database out by table?
DTS package to export data? What works for you?
It sounds as though your network may not be up to par. I have copied =
40GB across our network with ease.
You mention that you found a KB article. What article are you talking =
about?
--=20
Keith
"Jay" <anonymous@.discussions.microsoft.com> wrote in message =
news:1cee901c422e6$6f7445d0$a401280a@.phx.gbl...
> We have a process that does a production backup, copy .bak=20
> to the dev environment, then restore.
>=20
> Once we crossed the 21 GB .bak size, the copy process=20
> started failing because of the size of the file. I found=20
> the knowledge base article where this was explained.
>=20
> How do you get around this? BCP the database out by table?=20
> DTS package to export data? What works for you?
|||Hi,
If the normal copy doesn't work you can always try to FTP it over your
network. This is more stable than the normal copy. (In my experience.)
Greets,
Jo Segers.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> schreef in bericht
news:%23x250huIEHA.2556@.TK2MSFTNGP12.phx.gbl...
It sounds as though your network may not be up to par. I have copied 40GB
across our network with ease.
You mention that you found a KB article. What article are you talking
about?
Keith
"Jay" <anonymous@.discussions.microsoft.com> wrote in message
news:1cee901c422e6$6f7445d0$a401280a@.phx.gbl...
> We have a process that does a production backup, copy .bak
> to the dev environment, then restore.
> Once we crossed the 21 GB .bak size, the copy process
> started failing because of the size of the file. I found
> the knowledge base article where this was explained.
> How do you get around this? BCP the database out by table?
> DTS package to export data? What works for you?
|||What was the copy process that you used? Robocopy?
http://support.microsoft.com/default.aspx?scid=kb;en-
us;259837
MORE INFORMATION
The CopyFileEx and CopyFile functions are used by all of
the standard file-copy utilities that are included with
Windows NT 4.0 and Windows 2000. These utilities include
COPY, XCOPY, and Explorer. The resource kit utility,
Robocopy.exe, also uses the CopyFileEx function;
therefore, all of these tools have the same limitation.
Because these functions use buffered I/O, the maximum size
of a file that you can copy depends on the amount of paged-
pool memory that is available at the start of the copy
procedure and on whether the Physical Address Extensions
(PAE) kernel (which allows access to memory above 4
gigabytes [GB]) is in use.
With the standard kernel, 1 kilobyte (KB) of paged pool is
required for each megabyte (MB) of file size that is
opened for buffered I/O. Because there are two files
involved in a copy procedure, 2 KB of paged pool are
required for each MB in the source file. When the PAE
kernel is used, the overhead is doubled and 4 KB of paged
pool is required for each MB in the source file.
Service Pack 2 (SP2) for Windows 2000 contains a fix for
this limitation. The kernel memory manager has been
altered with respect to management of paged pool memory
when files are opened for buffered I/O. This change
permits buffered operations on arbitrarily large files and
therefore permits arbitrarily large files to be copied by
any utility that uses the CopyFileEx function.
|||Hi,
BCP OUT / BCP IN , DTS will take long execution time. I feel that you can
zip the file after backup and then copy the
zip file to development server and then unzip and restore the database. This
will be the easy and best approach.
Steps:
1. Backup the database
2. Zip the file
3. copy to destination
4. Unzip the file
5. Restore it
Thanks
Hari
MCDBA
"Jay" <anonymous@.discussions.microsoft.com> wrote in message
news:1cee901c422e6$6f7445d0$a401280a@.phx.gbl...
> We have a process that does a production backup, copy .bak
> to the dev environment, then restore.
> Once we crossed the 21 GB .bak size, the copy process
> started failing because of the size of the file. I found
> the knowledge base article where this was explained.
> How do you get around this? BCP the database out by table?
> DTS package to export data? What works for you?
|||I have used the Method Hari mentioned using FTP and Winrar. Winrar handles the large files well and ftp is faster than a regular copy. You can set up a DTS package that handles it all also.
Jeff
|||Or WINRAR which performs much faster than WINZIP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OgxYbouIEHA.2924@.TK2MSFTNGP09.phx.gbl...
> Hi,
> BCP OUT / BCP IN , DTS will take long execution time. I feel that you can
> zip the file after backup and then copy the
> zip file to development server and then unzip and restore the database.
This
> will be the easy and best approach.
>
> Steps:
> 1. Backup the database
> 2. Zip the file
> 3. copy to destination
> 4. Unzip the file
> 5. Restore it
>
> Thanks
> Hari
> MCDBA
> "Jay" <anonymous@.discussions.microsoft.com> wrote in message
> news:1cee901c422e6$6f7445d0$a401280a@.phx.gbl...
>

22 GB database copy between servers

We have a process that does a production backup, copy .bak
to the dev environment, then restore.
Once we crossed the 21 GB .bak size, the copy process
started failing because of the size of the file. I found
the knowledge base article where this was explained.
How do you get around this? BCP the database out by table?
DTS package to export data? What works for you?It sounds as though your network may not be up to par. I have copied =
40GB across our network with ease.
You mention that you found a KB article. What article are you talking =
about?
--=20
Keith
"Jay" <anonymous@.discussions.microsoft.com> wrote in message =
news:1cee901c422e6$6f7445d0$a401280a@.phx
.gbl...
> We have a process that does a production backup, copy .bak=20
> to the dev environment, then restore.
>=20
> Once we crossed the 21 GB .bak size, the copy process=20
> started failing because of the size of the file. I found=20
> the knowledge base article where this was explained.
>=20
> How do you get around this? BCP the database out by table?=20
> DTS package to export data? What works for you?|||Hi,
If the normal copy doesn't work you can always try to FTP it over your
network. This is more stable than the normal copy. (In my experience.)
Greets,
Jo Segers.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> schreef in bericht
news:%23x250huIEHA.2556@.TK2MSFTNGP12.phx.gbl...
It sounds as though your network may not be up to par. I have copied 40GB
across our network with ease.
You mention that you found a KB article. What article are you talking
about?
Keith
"Jay" <anonymous@.discussions.microsoft.com> wrote in message
news:1cee901c422e6$6f7445d0$a401280a@.phx
.gbl...
> We have a process that does a production backup, copy .bak
> to the dev environment, then restore.
> Once we crossed the 21 GB .bak size, the copy process
> started failing because of the size of the file. I found
> the knowledge base article where this was explained.
> How do you get around this? BCP the database out by table?
> DTS package to export data? What works for you?|||What was the copy process that you used? Robocopy?
http://support.microsoft.com/default.aspx?scid=kb;en-
us;259837
MORE INFORMATION
The CopyFileEx and CopyFile functions are used by all of
the standard file-copy utilities that are included with
Windows NT 4.0 and Windows 2000. These utilities include
COPY, XCOPY, and Explorer. The resource kit utility,
Robocopy.exe, also uses the CopyFileEx function;
therefore, all of these tools have the same limitation.
Because these functions use buffered I/O, the maximum size
of a file that you can copy depends on the amount of paged-
pool memory that is available at the start of the copy
procedure and on whether the Physical Address Extensions
(PAE) kernel (which allows access to memory above 4
gigabytes [GB]) is in use.
With the standard kernel, 1 kilobyte (KB) of paged pool is
required for each megabyte (MB) of file size that is
opened for buffered I/O. Because there are two files
involved in a copy procedure, 2 KB of paged pool are
required for each MB in the source file. When the PAE
kernel is used, the overhead is doubled and 4 KB of paged
pool is required for each MB in the source file.
Service Pack 2 (SP2) for Windows 2000 contains a fix for
this limitation. The kernel memory manager has been
altered with respect to management of paged pool memory
when files are opened for buffered I/O. This change
permits buffered operations on arbitrarily large files and
therefore permits arbitrarily large files to be copied by
any utility that uses the CopyFileEx function.|||Hi,
BCP OUT / BCP IN , DTS will take long execution time. I feel that you can
zip the file after backup and then copy the
zip file to development server and then unzip and restore the database. This
will be the easy and best approach.
Steps:
1. Backup the database
2. Zip the file
3. copy to destination
4. Unzip the file
5. Restore it
Thanks
Hari
MCDBA
"Jay" <anonymous@.discussions.microsoft.com> wrote in message
news:1cee901c422e6$6f7445d0$a401280a@.phx
.gbl...
> We have a process that does a production backup, copy .bak
> to the dev environment, then restore.
> Once we crossed the 21 GB .bak size, the copy process
> started failing because of the size of the file. I found
> the knowledge base article where this was explained.
> How do you get around this? BCP the database out by table?
> DTS package to export data? What works for you?|||I have used the Method Hari mentioned using FTP and Winrar. Winrar handles
the large files well and ftp is faster than a regular copy. You can set up
a DTS package that handles it all also.
Jeff|||Or WINRAR which performs much faster than WINZIP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OgxYbouIEHA.2924@.TK2MSFTNGP09.phx.gbl...
> Hi,
> BCP OUT / BCP IN , DTS will take long execution time. I feel that you can
> zip the file after backup and then copy the
> zip file to development server and then unzip and restore the database.
This
> will be the easy and best approach.
>
> Steps:
> 1. Backup the database
> 2. Zip the file
> 3. copy to destination
> 4. Unzip the file
> 5. Restore it
>
> Thanks
> Hari
> MCDBA
> "Jay" <anonymous@.discussions.microsoft.com> wrote in message
> news:1cee901c422e6$6f7445d0$a401280a@.phx
.gbl...
>

2005 vs 2000 performance problem

We have "proc_a" in our production sql2k db, executing it takes 5-10 seconds
if we backup our prod db, restore to sql2k5, rebuild all indexes, on faster
hardware, same proc takes about 2 minutes to run.
Can anyone suggest things to look at? This proc does quite a bit of
creating temp tables and inserting/deleting from them. (a legacy approach
we are looking to change)
I am comparing estimated execution plans and they look similar.
Thanks in advance!
Derrick> if we backup our prod db, restore to sql2k5, rebuild all indexes, on
> faster hardware, same proc takes about 2 minutes to run.
> Can anyone suggest things to look at?
Is compatibility mode set to 80 or 90? How "similar" are the execution
plans? If you do statistics time and statistics i/o, where is all the work
being done and time being spent?
Aaron Bertrand
SQL Server MVP|||Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
syntax again to get the query plan as text, with stats? I am trying :
set statistics time on
go
set statistics io on
go
set showplan_text on
go
exec myproc
The proc does *lots* of things, so eyeballing a compare between plans is
next to impossible.
Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OwZMg%234zHHA.1164@.TK2MSFTNGP02.phx.gbl...
> Is compatibility mode set to 80 or 90? How "similar" are the execution
> plans? If you do statistics time and statistics i/o, where is all the
> work being done and time being spent?
> --
> Aaron Bertrand
> SQL Server MVP
>|||> Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
> syntax again to get the query plan as text, with stats? I am trying :
I use this:
SET NOCOUNT ON;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
GO
SET SHOWPLAN_XML ON;
GO
EXEC dbo.my_procedure;
GO
SET SHOWPLAN_XML OFF;
GO
I don't really get much out of showplan_text. YMMV.

> The proc does *lots* of things, so eyeballing a compare between plans is
> next to impossible.
Then you might want to break the procedure up into chunks to make the
analysis more practical. Or get a bigger (or additional) monitor. :-)
A|||Aaron, thanks again, worked great.
A little more description on the proc, it is creating in table variables
(@.tables instead of #table) and then doing a join to that table, from 'real'
tables.
The rows returned on the join are crazy, millions, where in 2k, only
hundreds
Any idea if there is something with 2005 different than 2000 with respect to
indexes on table variables?
Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OM8JTO5zHHA.5800@.TK2MSFTNGP05.phx.gbl...
> I use this:
> SET NOCOUNT ON;
> SET STATISTICS TIME ON;
> SET STATISTICS IO ON;
> GO
> SET SHOWPLAN_XML ON;
> GO
> EXEC dbo.my_procedure;
> GO
> SET SHOWPLAN_XML OFF;
> GO
> I don't really get much out of showplan_text. YMMV.
>
> Then you might want to break the procedure up into chunks to make the
> analysis more practical. Or get a bigger (or additional) monitor. :-)
> A
>

2005 vs 2000 performance problem

We have "proc_a" in our production sql2k db, executing it takes 5-10 seconds
if we backup our prod db, restore to sql2k5, rebuild all indexes, on faster
hardware, same proc takes about 2 minutes to run.
Can anyone suggest things to look at? This proc does quite a bit of
creating temp tables and inserting/deleting from them. (a legacy approach
we are looking to change)
I am comparing estimated execution plans and they look similar.
Thanks in advance!
Derrick> if we backup our prod db, restore to sql2k5, rebuild all indexes, on
> faster hardware, same proc takes about 2 minutes to run.
> Can anyone suggest things to look at?
Is compatibility mode set to 80 or 90? How "similar" are the execution
plans? If you do statistics time and statistics i/o, where is all the work
being done and time being spent?
--
Aaron Bertrand
SQL Server MVP|||Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
syntax again to get the query plan as text, with stats? I am trying :
set statistics time on
go
set statistics io on
go
set showplan_text on
go
exec myproc
The proc does *lots* of things, so eyeballing a compare between plans is
next to impossible.
Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OwZMg%234zHHA.1164@.TK2MSFTNGP02.phx.gbl...
>> if we backup our prod db, restore to sql2k5, rebuild all indexes, on
>> faster hardware, same proc takes about 2 minutes to run.
>> Can anyone suggest things to look at?
> Is compatibility mode set to 80 or 90? How "similar" are the execution
> plans? If you do statistics time and statistics i/o, where is all the
> work being done and time being spent?
> --
> Aaron Bertrand
> SQL Server MVP
>|||> Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
> syntax again to get the query plan as text, with stats? I am trying :
I use this:
SET NOCOUNT ON;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
GO
SET SHOWPLAN_XML ON;
GO
EXEC dbo.my_procedure;
GO
SET SHOWPLAN_XML OFF;
GO
I don't really get much out of showplan_text. YMMV.
> The proc does *lots* of things, so eyeballing a compare between plans is
> next to impossible.
Then you might want to break the procedure up into chunks to make the
analysis more practical. Or get a bigger (or additional) monitor. :-)
A|||Aaron, thanks again, worked great.
A little more description on the proc, it is creating in table variables
(@.tables instead of #table) and then doing a join to that table, from 'real'
tables.
The rows returned on the join are crazy, millions, where in 2k, only
hundreds
Any idea if there is something with 2005 different than 2000 with respect to
indexes on table variables?
Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OM8JTO5zHHA.5800@.TK2MSFTNGP05.phx.gbl...
>> Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
>> syntax again to get the query plan as text, with stats? I am trying :
> I use this:
> SET NOCOUNT ON;
> SET STATISTICS TIME ON;
> SET STATISTICS IO ON;
> GO
> SET SHOWPLAN_XML ON;
> GO
> EXEC dbo.my_procedure;
> GO
> SET SHOWPLAN_XML OFF;
> GO
> I don't really get much out of showplan_text. YMMV.
>> The proc does *lots* of things, so eyeballing a compare between plans is
>> next to impossible.
> Then you might want to break the procedure up into chunks to make the
> analysis more practical. Or get a bigger (or additional) monitor. :-)
> A
>

2005 vs 2000 performance problem

We have "proc_a" in our production sql2k db, executing it takes 5-10 seconds
if we backup our prod db, restore to sql2k5, rebuild all indexes, on faster
hardware, same proc takes about 2 minutes to run.
Can anyone suggest things to look at? This proc does quite a bit of
creating temp tables and inserting/deleting from them. (a legacy approach
we are looking to change)
I am comparing estimated execution plans and they look similar.
Thanks in advance!
Derrick
> if we backup our prod db, restore to sql2k5, rebuild all indexes, on
> faster hardware, same proc takes about 2 minutes to run.
> Can anyone suggest things to look at?
Is compatibility mode set to 80 or 90? How "similar" are the execution
plans? If you do statistics time and statistics i/o, where is all the work
being done and time being spent?
Aaron Bertrand
SQL Server MVP
|||Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
syntax again to get the query plan as text, with stats? I am trying :
set statistics time on
go
set statistics io on
go
set showplan_text on
go
exec myproc
The proc does *lots* of things, so eyeballing a compare between plans is
next to impossible.
Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OwZMg%234zHHA.1164@.TK2MSFTNGP02.phx.gbl...
> Is compatibility mode set to 80 or 90? How "similar" are the execution
> plans? If you do statistics time and statistics i/o, where is all the
> work being done and time being spent?
> --
> Aaron Bertrand
> SQL Server MVP
>
|||> Hi Aaron, thanks for the help. I have tried both 80 and 90. What is the
> syntax again to get the query plan as text, with stats? I am trying :
I use this:
SET NOCOUNT ON;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
GO
SET SHOWPLAN_XML ON;
GO
EXEC dbo.my_procedure;
GO
SET SHOWPLAN_XML OFF;
GO
I don't really get much out of showplan_text. YMMV.

> The proc does *lots* of things, so eyeballing a compare between plans is
> next to impossible.
Then you might want to break the procedure up into chunks to make the
analysis more practical. Or get a bigger (or additional) monitor. :-)
A
|||Aaron, thanks again, worked great.
A little more description on the proc, it is creating in table variables
(@.tables instead of #table) and then doing a join to that table, from 'real'
tables.
The rows returned on the join are crazy, millions, where in 2k, only
hundreds
Any idea if there is something with 2005 different than 2000 with respect to
indexes on table variables?
Thanks!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OM8JTO5zHHA.5800@.TK2MSFTNGP05.phx.gbl...
> I use this:
> SET NOCOUNT ON;
> SET STATISTICS TIME ON;
> SET STATISTICS IO ON;
> GO
> SET SHOWPLAN_XML ON;
> GO
> EXEC dbo.my_procedure;
> GO
> SET SHOWPLAN_XML OFF;
> GO
> I don't really get much out of showplan_text. YMMV.
>
> Then you might want to break the procedure up into chunks to make the
> analysis more practical. Or get a bigger (or additional) monitor. :-)
> A
>

2005 to 2005 cluster

Hi,

is it possible simply restore db(s) and copy SSIS packages and legacy DTS from

server running sql 2005 to sql2005 running cluster ?

Since our cluster configuration not ready ,we plan migrate production to sql2005 stand alone server and few month later move -copy everything from this server to cluster.

Thanks

Yes, you can do it as you imaged. What you may notice is you have to restore the database into a cluster disk drive (cluster resources).

2005 to 2000

I can't restore a 2005 backup to 2000...

Do I have to detach/attach or is there some other option?

THanks

Susan

It's not possible for you to take a sql2k5 backup/data and restore/mount it on sql2k. The meta data has drastically changed in sql2k5.

Your only option is to export/import (SSIS) to transfer data between the instances.|||

hi susan,

You can't demote from compatibility level 9

to 8. however if you need to demote a db back to level 8

you can do it with SSIS. any sql statement using yukon functionality has

to be rewriten to match sql2k compatibility

regards,

joey