Reducing data file size prior to a database move

Question

Reducing data file size prior to a database move

Phineas Gage

Ten Centuries

Points: 1002
More actions
December 14, 2010 at 3:32 am

#146546

Hi All
We have several very large databases (800 GB+) that we are moving to a new server cluster. Prior to moving the databases, we would like to reduce the file sizes as much as possible to minimize the time taken to move them to new disks.
What would be the best way to do this? We will be performing maintenance on the databases once they are on the new cluster and prior to go-live, so fragmentation levels are not important prior to the move.
Would a truncate followed by a shrink file reduce the file size as much as possible, or is there more that we could do.
Thanks very much,
Phineas.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

How are you moving them? Detach/attach? Backup/Restore?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Innerise Ten Centuries Points: 1020 More actions · Answer 2

Ah, sorry - we'll be using detach>attach.

Gah - multiple accounts! :rolleyes:

Phineas Gage Ten Centuries Points: 1002 More actions · Answer 3

Gail - we'll be using attach>detach for most of them. However, we will be using backup and restore for one of them that has slight corruption - we'll be fixing this once it has been moved across.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

I strongly suggest that you fix the corruption prior to moving the database. It could fail to restore.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Phineas Gage Ten Centuries Points: 1002 More actions · Answer 5

Actually, funny you should say that, because the work has been postponed, so I now have time to fix the corruption first - thanks for the tip.

So how about reducing the file sizes for the other databases?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Your plans are about the best.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jpotucek SSCoach Points: 18583 More actions · Answer 7

Just a thought.. but we recently moved several DB Servers (SQL Server and DB2) to a new Hosting Company.

What I did was install 7Zip on each Server (source and Destination) - then I did a SQL server Backup and Zipped the backups before copying them to the destination Server. 7Zip is an Open Source ZIP program - it works great!

Here is my backup command:

osql -S "Servername" -E -Q"BACKUP DATABASE MYDB TO DISK = N'M:\Archive1\MyDB.bak'

and the command line switches for 7Zip:

L: 7z a Archive1.7z -m0=lzma2 -mx1 L:\Archive1\*.bak >Archive1.log

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 8

I would recommend investing in one of the backup compression tools that are available.

Quest Litespeed - I use this one

Redgate SQL Backup - also a very good tool, and less expensive than Litespeed

Idera SQL Safe

Hyperbac (compression only - so won't necessarily speed up your backups).

I use Litespeed on several systems - but the most important one is my 1TB+ database. Litespeed compresses the backup to less than 200GB (~185GB). Because of this, I was able to copy the backup file across the WAN several states away, restore the database and setup mirroring.

Now - I would recommend doing something like this all the time. You can reduce the time it takes to perform your backups and reduce the storage requirements (read, keep more backup copies online).

Another option you might have is to investigate the utilities you have available with your SAN. Each one has different capabilities - and some of them allow you to split a LUN and present a copy to another server, or synchronize and move, or....

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Phineas Gage Ten Centuries Points: 1002 More actions · Answer 9

Gail - Thanks for the confirmation.

Jpotucek - I've used 7Zip before, so I might look into doing what you suggest. Thanks.

Jeffrey - We already currently use LiteSpeed for these databases. However, when we move to the new cluster, we'll be moving to a Netbackup SQL Agent solution for these databases, since all our other databases currently use the Netbackup SQL agent (and very good it is, too). This means that we won't have LiteSpeed on the destination cluster to restore the databases. Good idea about splitting the LUNs though - I'll have a word with our SAN guys...

gbargsley SSCommitted Points: 1514 More actions · Answer 10

There is a command line utility that comes with Quest LiteSpeed that allows you to decomporess and restore a LiteSpeed backups without it being installed on the box.

If you already have that infrastrucutre in place I would stick with that to make your migration.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

Just want to clarify a statement: hyperbac can indeed compress (and encrypt) your backups and it will be faster than native backups because of this. As a side note you can also now mount backups as live read/write databases as well. Pretty spiffy.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

smartin-756027 SSC Enthusiast Points: 145 More actions · Answer 12

I never had an issue with 7-Zip and the compression ratio is great as I remember close to 10-1 great for moving large databases though the backup files I used were in the 100-125 gig range.