Choosing between Actual DBs or .Bak files of SQL Server

Question

0.00/5 (No votes)

See more:

Hi,
I need an expert advise on SQL Server DBs. What is better option for distributing the DBs. Actual DBs that could be attached to Server Instance through my code or .BAK files that could be restored?

Thanks

Posted 14-Mar-12 4:59am

Furqan Sehgal

Updated 14-Mar-12 5:05am

Herman<T>.Instance

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2012-03-14T05:03:00

Solution 1

Depends on the amount of data. If you are distributing a pretty-much-blank DB, I would distribute it via CREATE scripts as these compress very well. Since most DB files are allocated in multiple of megabytes there tends to be a lot of unused space in most.
If you have significant amounts of data, I would distribute the DB itself rather than a BAK file, purely because it should be faster to install at the other end.

Posted 14-Mar-12 5:03am

OriginalGriff

Comments

Furqan Sehgal 14-Mar-12 11:09am

So there is no drawback of distributing DBs instead of .Bak?

OriginalGriff 14-Mar-12 11:31am

There are always advantages and disadvantages. :laugh: If you are distributing the DB itself, then you need the LDF file as well as the MDF, and you need the DB offline when you make the file copy. So, if you are always going to distribute the current status of the DB, then BAK files are a little less hassle. But against that there is the configuration headache of monitoring and recording who got what and when, so if there is a problem you can tell what they have. If you are doing a one-off copy into a distribution package then I would still try to go with scripts if I could.

Thinking aboput it, you may need NDF files as well for a DB install, so maybe BAK is better.

I would be tempted to go with a script distribute, with an online bulk update to the latest data!

Dave Kreskowiak 14-Mar-12 11:31am

Depending on the version of the database you create the backup on and the version your installing on, you may not have the option of restoring it.

For an installation, I'd depend on a CREATE operation far more than I would a database restore.

OriginalGriff 14-Mar-12 11:39am

Very good point! I hadn't thought of that...
Pity you can't 5 a comment. I'll save it for the next time I see one of yours I can.

Dave Kreskowiak 14-Mar-12 12:22pm

I watched as someone tried to restore a database backed up on SQL Server 2008 and tried to restore it on a SQL Server 2000 box. They just couldn't understand why it wouldn't work! I kept my mouth shut to prolong and enjoy the festivities.

OriginalGriff 14-Mar-12 12:45pm

Cruel!

Did you vid it, by any chance? I'd love to see the dawning realization at the end... :laugh:

Dave Kreskowiak 14-Mar-12 13:39pm

Sadly, no video. Just imagine monkeys and footballs. And when they finally figured out they were on a 2000 box, the lightbulb went on and they went looking for an air pump for the footballs.