Moving a 40GB db
-
Hi all,
I have a 40GB db from Eastcoast that needs to be moved to Westcoast. What is the effective way to do so?. I planned to do backup/restore, but the current server did not have enough space to hold for another backup file. The database can not be down for several hrs. Please help.
Thanks
Minh Vu
-
Are you talking about copying the file via WAN/Internet?
Windows Resource kit has a tool called ROBOCOPY that can be used to copy the files. One of the many switches for the command allows for a delay between packets so that the network isn't flooded. I'm sure there would also be other tools, that perform the same function.
If this is a one time move, how about UPS
--------------------
Colt 45 - the original point and click interface
-
We also had to move a 40GB and a 25GB database over a very slow WAN connection (actually a slow VPN). We detached the database, zipped it with Winzip (max compression) and then FTP'd the zip file to the new server and then reversed the process.
NOTE: We zipped both the MDF and LDF into separate files so we could transfer them at the same time. However, you might be able to get away with just transferring the MDF becuase after you detach the database the log file is completely written/flushed and then detached. When you reattach, it will error out and ask if you want to create a blank log file.
Good luck!
-
Have you though about backup the database to a group of file? That's how I was able to overcome moving 60 GB backup file accross the network. Let's say you back it up to 10 backup file, after backup you can zip those file and move it accross.
The other way is to check to see if the database you want to move or copy is using more then one file group for data. You could potentially backup each filegroup to a backup and move it accross one at a time.
By the way the suggestion of doing robocopy does not always work. robocopy works really well for most of the file but not all the time. I have never successfully restore a file that was copy using robocopy. We had a problem somewhere in the network where it drop connection every 10 minutes just for a few seconds... and robocopy did finish the copy but still not a restorable file.
mom
-
I have a 60 gb db that I move frequently across our somewhat less than industrial WAN (3mb) and can accomplish this in less than 6 hours. Get a solid backup and use WINRAR - no size limitiation. The backup is typically 35 gb and takes about 3 hrs to compress on fast mode, the resulting rar file is @ 2 gb and can be moved cross country in no time flat. Unrar and restore on the destination. Works like a charm. I have even automated this in batch to include unrarring and restoring the db on the remote server from a scheduled task.
-
We use Robocopy as part of our log shipping from our production site in Melbourne to the DR site in Sydney. It is also used to copy backup files to and from branch offices to a central storage location for backup.
So far, touch wood, there have been no problems with any file corruption and a couple of the branch offices are on dodgy network connections.
--------------------
Colt 45 - the original point and click interface -
Hi,
The best way is to use Red-Gate SQL Backup. http://www.red-gate.com
Otherwise you can use
Backup Database <DatabaseName> to Disk =<BackupPath>
Backup Log <DatabaseName> to Disk =<BackupPath>
Use this command for back up even through the network mapping.
Then to restore the data at the destination Server, you can use
Restore Database <DatabaseName> from Disk=<Path>
Have a great day!!
Cool.......
Deepak G Nair
Gurgaon,India
-
I had moved some hugh databases across 2 continents, also the downtime cannot be too long. What I did was
1) Transmit the regular full backups to the new server
2) Replace the regular full backup with differential backup, transaction log still carried on.
3) Restore the full backup at the new server with norecovery.
4) On the day of cutover, do a manual diff backup and restore it at the new server.
5) If you are using SQL litespeed, the size of the databases and the time for restoration can be reduced tremendously.
Hope this help!
King
-
I have got a question/suggestion: Is it worth checking the fill factor?? If you reduce this does it reduce the size of the database??
Other than that I agree with the comments above, i.e.:
1. Detach database using sp_detach_db.
2. Use WinRar to compress the MDF file (think you can do this into multiple files which you can transfer individually if it helps).
3. Use sp_attach_single_file_db to attach the MDF file on the destination server. The LDF will be automatically created.
-
What would be the sql command to zip a file via WinRar?
-
SG: WinRar is shareware (I think). After your regular SQL backup, you would "RAR" your .BAK file instead of trying to "ZIP" it, because ZIP can only handle "small" files.
-
If you do it correctly, you should be able to move the database with only a few minutes of downtime.
1. Backup the database and copy the backup to the new server over the network.
2. Restore the database backup on the new server with no recovery.
3. Copy all the transaction log backups made since the full backup to the new server using ROBOCOPY. Continue the copies until you are ready to cutover.
4. Restore the log backups in sequence with no recovery.
5. When you are ready to cutover, kick all the users out of the old database, make one last transaction log backup, and set the database offline.
6. Copy the last transaction log backup to the new server, and restore it with recovery. The database is now live on the new server.
Before starting this, you should make sure all the logins are setup on the new server.
It is helpful if you run through this whole sequence as a test once in advance of go-live to make sure that you have everythig ready for the cutover, and you don't run into some last minute problem.
-
If you use PKZIP 5 or later, which has been out for 3 or 4 years, there is no limit on the size of the compressed files, and I have compressed 40 GB and larger files with it.
-
If it is one time thing... you can download some thirdparty backup tools like LiteSpeed (demo version) for backup.
Backup your db using tool(litespeed) to multiple files so that you can copy them easily...
If you copy single big file across the coast .... for some reason if you loose connection you have start over again.. if you have multiple files it will be better...
These tools(litespeed) compress your backup upto 80% and more...
MohammedU
Microsoft SQL Server MVP -
You can also backup to multiple files using the regular SQL Server backup command, but if you need to compress them, you will have to use a file compression utility like PKZIP.
Both the COPY and ROBOCOPY commands have a /Z option that does a restartable copy so that you can just rerun the command in the event of a network failure and it will pickup from where it stopped. I always use it for these types of transfers.
Accepting all invitesViewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply