SQL Server Performance Forum – Threads Archive

Attach and detach database for log shipping config

We are currently using SQL SERVER 2000 on Service Pack 3 with log shipping configured. We have a SAP database of 200 GB and we have to change the server(box) on which the secondary server resides. Since copying of the database backup is taking pretty long time, we were evaluating the attach-detattach database option. However, I am able to successfully attach the secondary database (which is read-only). but when I re-attach the secondary database (on the new box), it becomes read-write. So when I configure the log shipping to the secondary database on the new box, I am getting the following error :
"The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY" So is there any option to use attach and re-attach database on read-only database. Even I manually made the secondary database on the new box read-only, but it did not work Please help us. rgds, viral shah

If error 4306, http://msdn.microsoft.com/library/en-us/trblsql/tr_reslsyserr_2_3ewm.asp HTH Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Luis, I understand that the option the note refers is re-creating the standby database thru backup and restore. But re-creating the standy server is not possible thru attach and detach procedure? because our database is 200 Gb and copying the dump from prodn to standy is taking days (not couple of hours !!).. So please guide me.. rgds, viral shah

Viral If you attach a database by default it will be in read-write mode and if you enable as read-only mode the transaction logs will not be restored, and you need read-write capability to write the latest log. The best option for this is to take full backup from Primary server to secondary server to restore and then enable LOG SHIPPING to apply the process. Other option is the take latest trnsaction log and use WITH STANDBY option while restoring the log. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya, thanks for the update. We cannot go for the backup and restore option as it is 200 GB database and the copy takes as mentioned earlier takes 2 days…. So we were eyeing for attach and re-attach database… but as mentioned earlier the restore of trasnsaction logs is giving error… As suggested by you, i m applying the transaction logs through enterprise manager as is using the 3rd option in the Options tab ( Leave the database read-only and able to apply transactional logs — which options to WITH STANDBY option), i am getting the same error. I tried apply all the transactional logs which were after the backup of database was done, but I got the same error. Please help me at the earliest. rgds, viral shah

There might be an unsupported option to alter the "status" column in the sysdatabases table on the destination server and set it to 32 for your database which would mean "loading". See: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-d_5xrn.asp</a> This seems like a dangerous thing to do though but in theory it could work to restore transaction logs after this is done. Of course if you encounter any issues with it don’t expect any help from Microsoft. And 200 GB + SAP + unsupported doesn’t sounds good to me <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Instead I would use the supported way which is by doing backup and restore. You could set up a backup device that points to an network UNC path to your source server and restore over network. I would recommend you look into products like SQL LiteSpeed that zip the backups on the fly to speed up this process: http://www.imceda.com/SQL_Server_2000.htm</a> http://www.sql-server-performance.com/sql_litespeed_spotlight.asp</a> If you have multiple CPUs LiteSpeed will be really quick if you split up the backup to a number of files as well. I think they have a trial version you can download.

Argyle, Thanks for the update. Indeed I used the SQLlite product and it indeed worked fine.. thanks for the update.. the backup was compressed to 45 GB ( in place of 210 GB) but however the restore took 36 hours.. I don’t know whY? does this usually happens? Any thoughts on this? Rgds, Viral Shah

Where the files on the local disks or an UNC path during restore? If they where on an unc path the network speed could slow down the restore.

Argyle, Yes, the files were on an UNC path. But we had placed a 1 GB ethernet card to increase the throughput.. So as per your remarks, whenever u use an UNC path, the restore is going to be dead slow !!.. Any alernative suggestion or guidance or thumb rules for the restoration time calculation would be highly appreciated. Thanking you in anticipation. Rgds, Viral Shah

Well 1GBit should be enough performance but I’ve rarly seen 1GB networks that actually reach these speeds. I would test different scenarios and see which is fastest for you: 1. Backup to UNC path, restore locally
2. Backup locally, restore over UNC path
3. Backup locally, standard copy of files, restore locally

ENsure no issues on the network and no hardware glitch between these 2 servers.
http://www.sql-server-performance.com/backup_restore_tuning.asp for information on tuning tips. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>