SQL 2005 Logshipping question

[mkal]

We are going to be moving databases from one sql 2005 server to another sql 2005 server as part of a hardware upgrade. One of the databases participates in logshipping.

If I backup the database and restore it on the other server will this break logshipping? I realize I will have to redo logshipping but I'm wondering if this will break the log sequence chain.

Thanks

 

[mrdenny]

Yes, the restore will break the Log chain.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[mkal]

What about detaching the db and re-attaching on the new server would that preserve the log chain?

Thanks

 

[mrdenny]

That would still break the log chain. About the only way to do it without breaking the log chain would be to setup the new box, using the exact same drive letters as the old server, and the system databases in the exact same path. Then stop SQL on both servers, and copy the system databases and user databases into place on the new server. Then start the services on the new server. Correct the server name in the sysservers table and you should be fine.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[mkal]

I got it to work by doing the following:

1)Created job to backup the logs on the old server, I didn't want them getting to large
2)Copied them up the new server.
3)Detached the database on old server
4)Copied .mdf/.ldf to new server
5)Re-attached the Database
6)Renamed computers (new server got the old server name)and rebooted both boxes
7)Ran sp_dropserver/sp_addserver on new server & restarted services
8)Wrote a script to restore the logfile backups with standby and undo file
9)Made sure drive names, foldernames, shares and paths were identical on both servers
10)Re-created logshipping

I did not need to copy over the system databases, may be I got lucky.

Thanks for the advice!