Suggestions for moving a ~1.5GB database…. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Suggestions for moving a ~1.5GB database….

We are moving to a new datacenter. I’d like to minimize the amount of downtime for the application. If I do a backup of the database, it takes about 45 minutes to get it transferred to the new datacenter. I thought it should be possible to do a backup, and then once that has been restored, take the system down and copy over the most recent transaction logs (i.e. how log shipping works)… 1. I’m not sure if this is the best way… (would one of the export data wizards be a better approach?) 2. I’m not actually sure how to go about implementing it. It looks like there are no transaction logs being kept for the database. I.e. the last modified date on the LDF file was weeks ago. Thanks for any assistance…
Here’s something that I’ve noticed with backup files–they compress REALLY well. When I need to move a large backup file over a slow connection, I zip it up into an archive, which shrinks it down quite a bit. Maybe you can see if that makes it small enough to move over?
quote:Originally posted by jlee Here’s something that I’ve noticed with backup files–they compress REALLY well. When I need to move a large backup file over a slow connection, I zip it up into an archive, which shrinks it down quite a bit. Maybe you can see if that makes it small enough to move over?

Thanks — that is good advice. Do you think that moving a backup file is my best bet? It would seem to make more sense if it was possible to do a "diff" on the databases and only move the ~5% of content that has changed then resending the entire thing…

Other solution is to detach database, copy to new server and attach to new.
If you chose this solution, run Update Statistics in new server.
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Your approach is suitable by restoring complete backup on the secondary server and meanwhile perform 15 min. interval of Tlog backup on primary server and stop the services. Then restore those logs on the secondary server to take recent transactions. I would also support what Luis suggested to detach the data & log files from the primary server and use SP_ATTACH_DB to attach on secondary server. If not simply use SP_ATTACH_SINGLE_FILE_DB to attach only data file which will create fresh Tlog file. But this way it takes bit more time than what you’d thought. For information on the topic refer to thishttp://vyaskn.tripod.com/moving_sql_server.htm link. 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.
I know that this is probably a stupid question, but I can’t figure out how to back up the transaction logs on the database. When I select the backup option, it gives me the choices of a full or differential backup, but there is no option to backup just the transaction logs. Thanks for all the advice…
You can use TSQL Statement BACKUP LOG for the same. See Backup in BOL. I would second ehat Satya suggested here. This will have minimal downtime to your systems. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Invader_Zog Check whether the database is enabled with SIMPLE recovery model.
If so you cannot perform backup transaction 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.
True, must be in full recovery model.
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />Invader_Zog<br /><br />Check whether the database is enabled with SIMPLE recovery model.<br />If so you cannot perform backup transaction log.<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of knowledge sharing.</font id="size1"></font id="teal"></center><br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />Thanks! — that is what it was <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />
Then to perform BACKUP LOG it must be either FULL or BULK LOGGED recovery models. 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.
]]>