Moving logshipping target database (sql2000) without recovery

We use SqlLiteSpeed and a set of scripts to do logshipping. The scripts
simply use SqlLiteSpeed's version of restore log with standby.
Our recipient databases of log shipping sit over a WAN on our old server.
Due to changing database sizes I needed to move one of the data files. I
assumed sp_detach_db and sp_attach_db would be the easiest way to do this.
However I could not restore any logs after the sp_attach_db as this appears
to recover the database and leave it in read/write mode.
Luckily the first database I tried to move was the smallest and I will be
able to re-initialise the logshipping over a few hours.
Is there another way to move the data files without recovering the database?
Could it be done by starting up with -m and updating tables?
Regards
Paul CahillYou are out of luck.
What you are looking for is "Attach with NoRecovery", a feature that doesn't
exist yet.
I have a Connect item requesting this feature which was closed due to
duplication. Unfortunately, Microsoft has not seen fit to tell me the
duplicate Connect item.
I do have reason to believe it is being considered for the SQL 2008 release,
but that won't help you much now.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:uCt6wmUIIHA.4296@.TK2MSFTNGP04.phx.gbl...
> We use SqlLiteSpeed and a set of scripts to do logshipping. The scripts
> simply use SqlLiteSpeed's version of restore log with standby.
> Our recipient databases of log shipping sit over a WAN on our old server.
> Due to changing database sizes I needed to move one of the data files. I
> assumed sp_detach_db and sp_attach_db would be the easiest way to do this.
> However I could not restore any logs after the sp_attach_db as this
> appears to recover the database and leave it in read/write mode.
> Luckily the first database I tried to move was the smallest and I will be
> able to re-initialise the logshipping over a few hours.
> Is there another way to move the data files without recovering the
> database? Could it be done by starting up with -m and updating tables?
> Regards
> Paul Cahill
>
>|||Cheers for the info Geoff. I might have to do this with drive letters. There
will be too much log build up to resync this easily. I sync'd the machines
when they were on the both on the lan.
Paul
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23E04laVIIHA.5208@.TK2MSFTNGP04.phx.gbl...
> You are out of luck.
> What you are looking for is "Attach with NoRecovery", a feature that
> doesn't exist yet.
> I have a Connect item requesting this feature which was closed due to
> duplication. Unfortunately, Microsoft has not seen fit to tell me the
> duplicate Connect item.
> I do have reason to believe it is being considered for the SQL 2008
> release, but that won't help you much now.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:uCt6wmUIIHA.4296@.TK2MSFTNGP04.phx.gbl...
>> We use SqlLiteSpeed and a set of scripts to do logshipping. The scripts
>> simply use SqlLiteSpeed's version of restore log with standby.
>> Our recipient databases of log shipping sit over a WAN on our old server.
>> Due to changing database sizes I needed to move one of the data files. I
>> assumed sp_detach_db and sp_attach_db would be the easiest way to do
>> this. However I could not restore any logs after the sp_attach_db as this
>> appears to recover the database and leave it in read/write mode.
>> Luckily the first database I tried to move was the smallest and I will be
>> able to re-initialise the logshipping over a few hours.
>> Is there another way to move the data files without recovering the
>> database? Could it be done by starting up with -m and updating tables?
>> Regards
>> Paul Cahill
>>
>|||If they are physically close, a USB drive might do the trick to transfer the
snapshot. SneakerNet on Steroids.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul Cahill" <anon@.hotmail.com> wrote in message
news:eaaNWHYIIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Cheers for the info Geoff. I might have to do this with drive letters.
> There will be too much log build up to resync this easily. I sync'd the
> machines when they were on the both on the lan.
> Paul
>
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23E04laVIIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> You are out of luck.
>> What you are looking for is "Attach with NoRecovery", a feature that
>> doesn't exist yet.
>> I have a Connect item requesting this feature which was closed due to
>> duplication. Unfortunately, Microsoft has not seen fit to tell me the
>> duplicate Connect item.
>> I do have reason to believe it is being considered for the SQL 2008
>> release, but that won't help you much now.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:uCt6wmUIIHA.4296@.TK2MSFTNGP04.phx.gbl...
>> We use SqlLiteSpeed and a set of scripts to do logshipping. The scripts
>> simply use SqlLiteSpeed's version of restore log with standby.
>> Our recipient databases of log shipping sit over a WAN on our old
>> server. Due to changing database sizes I needed to move one of the data
>> files. I assumed sp_detach_db and sp_attach_db would be the easiest way
>> to do this. However I could not restore any logs after the sp_attach_db
>> as this appears to recover the database and leave it in read/write mode.
>> Luckily the first database I tried to move was the smallest and I will
>> be able to re-initialise the logshipping over a few hours.
>> Is there another way to move the data files without recovering the
>> database? Could it be done by starting up with -m and updating tables?
>> Regards
>> Paul Cahill
>>
>>
>|||I'll have to get the motorcycle out one last time before winter bites.
Cheers Geoff
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:ujhmrgYIIHA.3356@.TK2MSFTNGP02.phx.gbl...
> If they are physically close, a USB drive might do the trick to transfer
> the snapshot. SneakerNet on Steroids.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul Cahill" <anon@.hotmail.com> wrote in message
> news:eaaNWHYIIHA.5400@.TK2MSFTNGP04.phx.gbl...
>> Cheers for the info Geoff. I might have to do this with drive letters.
>> There will be too much log build up to resync this easily. I sync'd the
>> machines when they were on the both on the lan.
>> Paul
>>
>>
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
>> news:%23E04laVIIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> You are out of luck.
>> What you are looking for is "Attach with NoRecovery", a feature that
>> doesn't exist yet.
>> I have a Connect item requesting this feature which was closed due to
>> duplication. Unfortunately, Microsoft has not seen fit to tell me the
>> duplicate Connect item.
>> I do have reason to believe it is being considered for the SQL 2008
>> release, but that won't help you much now.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:uCt6wmUIIHA.4296@.TK2MSFTNGP04.phx.gbl...
>> We use SqlLiteSpeed and a set of scripts to do logshipping. The scripts
>> simply use SqlLiteSpeed's version of restore log with standby.
>> Our recipient databases of log shipping sit over a WAN on our old
>> server. Due to changing database sizes I needed to move one of the data
>> files. I assumed sp_detach_db and sp_attach_db would be the easiest way
>> to do this. However I could not restore any logs after the sp_attach_db
>> as this appears to recover the database and leave it in read/write
>> mode.
>> Luckily the first database I tried to move was the smallest and I will
>> be able to re-initialise the logshipping over a few hours.
>> Is there another way to move the data files without recovering the
>> database? Could it be done by starting up with -m and updating tables?
>> Regards
>> Paul Cahill
>>
>>
>>
>|||Anyone know if this would work?
Paul
An extract from http://support.microsoft.com/kb/224071/en-us
SQL Server 2005 and SQL Server 2000
In SQL Server 2005 and in SQL Server 2000, you cannot detach system
databases by using the sp_detach_db stored procedure. When you try to run
the sp_detach_db 'model' statement, you receive the following error message:
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
To move the model database, you must start SQL Server together with the -c
option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL
Server from recovering any database except the master database.
Note You will not be able to access any user databases after you do this.
You must not perform any operations, other than the following steps, while
you use this trace flag. To add trace flag 3608 as a SQL Server startup
parameter, follow these steps: 1. In SQL Server Enterprise Manager,
right-click the server name, and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add the following new parameter:
-c -m -T3608