How to make a smaller backup for developers

Question

How to make a smaller backup for developers

Cory E.

SSCertifiable

Points: 7076
More actions
October 12, 2007 at 1:57 pm

#175892

Good day -
I have a request from some developers to find a way to make the backups smaller, so, they can restore the DB's on their laptops and not eat so much space. The process in place now is a backup is copied from the prod backup location, restored on some other machine, some of the older data is removed, the data is then Anonymized, and then a new backup is created for the other to use. The proposed process is, after this is done, to do the backup, ignoring the log file, which is set large for the working DB, and needs to, but is not needed to be this large for the developers. I had proposed two options, 1, to backup using the "With COPY_ONLY" option, or to set the DB to simple recovery mode. Would either of these be recommended?
We did try the "With COPY_ONLY" option, but we are now unable to restore the DB. It does not seem to recognize it as a valid backup set.
What other ways could we do this? Do others do something similar? Do others have glaring reasons why this should not be done? I have been against this for quite some time, and the solution has been to "Anonymize" the data, however, I am not sure as to what that currently entails.
-- Cory

Viewing 10 posts - 1 through 9 (of 9 total)

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

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

There is no way to do what you want except with an intermediate step similar to you have. It's a commonly requested feature. The problem is that the database size is not dependant on the amount of space allocated within the files so when the database is restored, the first thing that happens is that the new database files are sized in accordance with the file metadata.

It's this way in SQL Server 2008 as well.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Is the problem that the resulting database on the laptop is too big, or the amount of space of the database AND the.BAK file ? If it's a transitory problem of having enough room for the DB and the .BAK, you could buy backup compression software to make you backup files about 80% smaller. You'd need the software on the server & the laptops.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

I think you've got a good process there, though instead of removing data, perhaps you want to move off some percentage of good data to a new database with a smaller log file and give that to the developers? Wouldn't even need DTS/SSIS. You could just use T-SQL to copy over some amount of data.

ramoha SSCommitted Points: 1909 More actions · Answer 4

Try using SQL Litespeed 2005 its a thrid party backup software recommended by microsoft I have used while working in Microsoft.

The compression is awesome almost 80% reduction in the backup size.

All the commands are available as you get with native backups..

Razi, M.
http://questivity.com/it-training.html

ianT SSCrazy Points: 2956 More actions · Answer 5

I've had good compression rates with Red Gate.

http://www.red-gate.com/products/SQL_Backup/index.htm

george sibbald SSC Guru Points: 104210 More actions · Answer 6

are you shrinking the files as much as possible before producing the final backup to be passed to the developers machines?

---------------------------------------------------------------------

Cory E. SSCertifiable Points: 7076 More actions · Answer 7

Oh jeez, somehow I missed these replys.

homebrew01: The issue is that, as Paul Randal said, the metadata requires X for space, even tho, it may only use X-Y. The developers may not have X free, and then the restore fails.

george sibbald: Yes, that is part of the process now. We have tweaked the process now, and seems to be acceptable to the developers...here is the latest process

1. Prod backup is made - this goes to tape, and is the backup that is used for the rest of the process

2. The prod backup is copied over to another server

3. The Backup is restored

4. Some process (Developers created it...) removes data older than some date

5. Some other process (again, developers) annomizes the data

6. The database is set to simple (I need to talk to them about putting this up on the order)

7. Shrink database command is executed

8. A backup is created and used to distribute to other developers.

It has been a while since I have seen this process, but I think this was the final set of steps.

-- Cory

CAGreensfelder SSC Eights! Points: 919 More actions · Answer 8

That is almost exactly what we are doing.

We set the restored database to simple before making the subset and anonymizing the data. In fact, it's the first line of the subset script so no one can forget. (Why log anymore than you have to while doing all those deletes and updates?)

We zip the backup before distributing it. Us developers still have to have room for the unzipped backup and the restored database on our laptops, but external hard drives are getting cheaper every day.

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 9

i'll agree with the comments posted by Cory Ellingson....