Moving a SQL Server 2000 database that is in Standby Mode

SQL Users/DBAs,

I'm trying to move data files around for a database that is in standby
mode. I can detach/attach the database fine using
SP_DETACH_DB/SP_ATTACH_DB , but when I re-attach the database the log
sequence is broken and I can't restore any more logs. Does anyone
know if there is a way to move around data files and keep the database
in standby mode?

Thanks,

JB"jbizzy" <jbizzaro@.yahoo.com> wrote in message
news:609b4de0.0404201342.29ceb03e@.posting.google.c om...
> SQL Users/DBAs,
> I'm trying to move data files around for a database that is in standby
> mode. I can detach/attach the database fine using
> SP_DETACH_DB/SP_ATTACH_DB , but when I re-attach the database the log
> sequence is broken and I can't restore any more logs. Does anyone
> know if there is a way to move around data files and keep the database
> in standby mode?
>
> Thanks,
>
> JB

It would be useful to know why you need to do this - if you want to maintain
two standby copies of a database, then why not simply restore the log
backups to both databases, instead of restoring to only one and then trying
to copy it?

Simon|||Our database is 350 GB...we're upgrading some of our arrays...rather
than re-sync the database up using restore, we would just like to move a
few of the datafiles..the restore takes many hours..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"jbizzy" <jbizzaro@.yahoo.com> wrote in message
news:609b4de0.0404201342.29ceb03e@.posting.google.c om...
> SQL Users/DBAs,
> I'm trying to move data files around for a database that is in standby
> mode. I can detach/attach the database fine using
> SP_DETACH_DB/SP_ATTACH_DB , but when I re-attach the database the log
> sequence is broken and I can't restore any more logs. Does anyone
> know if there is a way to move around data files and keep the database
> in standby mode?
>
> Thanks,
>
> JB

It would be useful to know why you need to do this - if you want to maintain
two standby copies of a database, then why not simply restore the log
backups to both databases, instead of restoring to only one and then trying
to copy it?

Simon|||Our database is 350 GB...we're upgrading some of our arrays...rather
than re-sync the database up using restore, we would just like to move a
few of the datafiles..the restore takes many hours..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

moving a database to another physical disk

I want to move 2 databases to another physical disk. They are both single file databases. It looks like a fairly straigtforward attach and detach procedure, but I have a couple of questions:

The log files (.ldf) currently reside on a separate physical disk from the data files, if I attach and reattach the data files will the logs remain where they are by default or do I have to re specify there location?

Is there any advantage to running the update statistics portion of the sp_detach_db?with the re-attach procedure you can specify the location of the log file as well. See the article in BOL and this is probably the quickest method. However the same thing can be done with backup and restore using the with move and replace arguments.

Moving a database from 2005 Express to Enterprise

If I detach a database from an instance of SQL Server 2005 Express and attach it to an instance of SQL Server 2005 Enterprise, will the database be essentially identical to one initially created under 2005 Enterprise, or will some Express edition limitation follow the database from the original instance to the new one?

Thanks,
Peter Nyberg

The on-disk structures will be identical, so you should have no problems. Only thing to keep in mind would be the possible features available in enterprise not available for use in express (for example, if you make use of indexed views, enterprise's optimizer will automatically consider them whereas express's won't)...but, given that you're moving from express, you are probably not making use of things like that anyhow.

HTH