Does anyone know if it is possible to backup a functional database, and then place it in standby mode capable of having logs restored on it? (SQL 2k on Win2k)
In a nutshell, this is what I am trying to do. Server A is live in production. Server B is new shiny powerful server waiting to become production. I want to backup our 60 gig DB copy it from A to B. Restore it on B. Stop SQL on A, copy new transaction logs to B, apply them, rename B to A and bring it up as the new production server. Now, here's the problem. I want to make the old A into the warm spare. To do this it has to be in standby mode ready to accept transaction logs. Restoring onto oldA is not an option because it is taking around 10 hours to do it, and we would be without a back up for that time. Not good.
So anyone know if this is possible, or have a better migration plan?
You can use the Log Shipping process to accomplish this project.
For more detail instruction on this, please refer to BOL. I can give you an example of restore a Full backup (from Server A) to Server B:
restore database MYDB
with MOVE MYDB_data' to 'H:\MSSQL\data\MYDB_Data.MDF',
MOVE MYDB_log' to 'E:\MSSQL\log\MYDB_log.LDF',
REPLACE, RESTART, STANDBY ='T:\MYDB_undo',
After this, the translog from Server A will automatically copied &
restore into Server B. After successfully setup the server B as a hot standby, you then switch the role between the 2 servers.
The first approach worked for us as well (though we had plenty of down time and only a 10GB database to work with.
1. Renaming with SQL 2K did not seem to be a problem for us, except for the Jobs. When we renamed the servers, the jobs retained the original name and were thus considered to be "owned" by another server. I could not enable them, disable them, delete them or otherwise modify them. I eventually went into the sysjobs table in MSDB and modified the "owner"
2. If you have a lot of users, watch out. It's easy to migrate the logins over using the DTS transfer package, but you then have to "link" the logins on the new server to the permissions they had on the old server. I'll dig through my notes, but there is a process to do this (and it's not well documented in the SQL 2000 log shipping in SQL BOL).
3. The Log Shipping concept proposed by someone else should also work well for you. Very well in fact. You still have to watch out for jobs, DTS packages and the logins, but if you need a minimum amount of downtime, then I would strongly consider this approach. It may not work from SQL 7 to SQL 2K.
Originally posted by Cesar Fraustro
We experienced many problems when changed that way, mainly because SQL refused to be renamed! (I'm talking abour SQL7, maybe SQL2K works better)
Also, we have problems with IP addresses, mirroring, SP's, etc...
What about replication?
I saw very impressive results in LANs and WANs.
Good points there hmscott. Here is how I propose to get around those very issues...
1. We are planning on scripting out all the jobs prior to the move, and then recreating them once we hit the new machine.
2. I have scripted out the sysxlogins table to a table I created on another server. doing this, it is pretty simple to link the logins to the apporpriate database, which, when restored, has all the permissions associated with it.
3. I am still researching logshipping. We use a home grown solution that works pretty well, so we'll have to look at em both and go from there.
Finally, for anyone that is interested, it is possible to bring a database back up into standby mode by doing a backup log with standby command. you then apply that log you just backed up using a restore log with standby and from there, you can apply transaction logs.