September 10, 2010 at 12:12 pm
I have a bit of challenge.. We will be soon moving our Servers - including a Clustered SQL Server which is the server in question here- from one hosting site to another.
Our Staging environment is built and they are in the process of doing application testing etc..
At some point, we will build the Production environment and I need to invent a way to ensure that no data is lost between the time the Business folks "sign off" on the new Production-hosted environment and the time we actually cut over to using the new site. This gap could be as little as hours and as much as days.
The two environments cannot talk to one another to I cannot have anything like replication or log-shipping in the mix. I've been playing around with a Full and then differential and\or log backups for the time period between the sign-off and the actual cutover.. Has anyone done anything like this before ?? any suggestions would be greatly appreciated!
September 10, 2010 at 12:26 pm
What is your downtime window?
Can you just take the system down, run a full backup, copy it, and restore it on the new server? If you have the time available, I would do that.
If not, probably differential backups would be the best way to go.
September 10, 2010 at 1:40 pm
What I was trying to avoid was a second FULL restore because each time I do a full backup and then restore I have to backup on the first hosting company, copy the backup back to my network and then copy the backup up to the second hosting company and restore. Some of the databases are huge and the copy time is what kills me. I thought if I could just do a full backup once.. then take differentials between the "sign off" on the new Hosted system , and the time we actually cut over to the new hosted system.. which they tell me could be hours or days.
If I take a full back and then differentials.. can I restore the full backup at the new hosting company and then apply the differentials ONY hours or days later? I'm trying it in a lab and it's not going very well.. Here is the code I used to try and apply the first differential
RESTORE DATABASE [Admin1] FROM DISK = N'L:\backup\Admin\Admin_backup_201009101346.bak' WITH FILE = 1, MOVE N'Admin' TO N'L:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Admin1.mdf', MOVE N'Admin_log' TO N'L:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Admin1_log.ldf', NOUNLOAD, REPLACE, STATS = 10
GO
and I get this error
System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.SqlServer.Smo)
September 10, 2010 at 1:45 pm
You need to specify WITH NORECOVERY on a differential restore, otherwise it will try to bring it live, which it can't do without the tail of the transaction log. Also..this assumes you restored the original backup WITH NORECOVERY and didn't make it live..because as I'm sure you're aware, once a database is live, no backups/logs can be applied.
Which brings me to another point..you need to take one last backup of the transaction log when the system is down, and then apply that last WITH RECOVERY.
September 13, 2010 at 5:30 am
so it sounds like I can forget the differentials and the log backups. I will have to take a full backup and apply it to the new site when we cutover and then take a second full backup and apply it again when they do their final signoff. I was trying to avoid that because I have to make a ton of permmissions changes each time I restore a full backup.. but I suppose I can script those.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply