Create a New DB and Restore the Production DB

Andwian

Dear Experts,

Is it needed and a best practice to periodically create a new DB for the Production DB?
Does it has any effects?
And which one is better, restore using SQL Backup or NAV backup?

Thank you in advance.

bbrown

Why would you want to do this?

Andwian

Nothing. I just think that it may 'refresh' the DB as a new DB :oops:
And I observed that the DB file size is smaller than the old one. Save the disk space, and may be could boost performance

bbrown

Andwian wrote:

...And I observed that the DB file size is smaller than the old one...

Where are you observing this? If we are talking a SQL restore, then the new DB will be the same size as the old one. With a NAV restore it will be whatever size you create the empty database.

Savatage

Maybe he means "Database Used" :-k

If my database is 60gb with 85% used - Delete & Restore it would then show ..say 80% used.
That's because all the keys have been recreated & everything is optimized.

bbrown

Savatage wrote:

Maybe he means "Database Used" :-k

If my database is 60gb with 85% used - Delete & Restore it would then show ..say 80% used.
That's because all the keys have been recreated & everything is optimized.

But only if you do a NAV backup/restore. I think he's looking at the SQL backup file size. But then we're all just guessing.

Andwian

Sorry for not getting back earlier.

I mean the *.mdf, *.ndf, and *.ldf file size in the windows program file, or Database | Alter.

For the DB Used, I think it would be grow with time, so it is useless to see the DB used.
I think DB Size could be grow, and the DB used % will be less again and again.

Anyway, is it nice to do the routine creating a new DB and restoring for the Production DB?

bbrown

I see no reason. Why are you thinking this might be something to do?

David_Singleton

If it's not broke don't fix it.

deV.ch

That makes no sense, why would you do that? your mdf file would grow anyway... and in performance perspective its even worse if sql needs to enlarge the mdf file regularly. in fact its better to have a large file and size increase should happen as few as possible.
If your ldf is too large then you do something wrong reagarding log file backup...

So having smaller files will not not result in a performance gain, it might even decrase your performance.
However doing regular maintanance jobs such as index reorg/rebuild is what you should do for performance.

David Singleton wrote:

If it's not broke don't fix it.

I totaly agree with that!

Andwian

Hmm... I see..
So it does not make any performance getting better.
I thought before, that I may get the DB "refreshed" again, and get the performance better.:oops:

Well, thanks everyone for the appreciable inputs!
I can see it clearly now =D>

DenSter

To keep the database 'fresh', you put in place proper maintenance. Watch this to learn about essential database maintenance on SQL Server: http://www.youtube.com/watch?v=0KbZkKdyZps

Andwian

Thanks Daniel!

DenSter

You're welcome