MS SQL Server - Restoring a large database

Author

Restoring a large database

Ant-nee

2006-01-24, 8:23 pm

Hi everyone,

One of our production databases is 30 GB. I sized the database at 30 GB to
avoid any future fragmentation and to keep the data files contiguous forever.
There is only about 500 MB of data in the database - the rest is, right now,
empty space.

Occasionally, I have to restore this database to another instance of SQL.
The database backup is 500 MB, but when I restore the database, it restores
itself to the original database's size of 30 GB. I don't want this to happen
- on my restore, I want just the 500 MB or so of data, not the other 29.5 GB
of empty space.

How do I do this?

Thanks in advance.

Anthony Rizzo

Tibor Karaszi

2006-01-24, 8:23 pm

> How do I do this?

You can't. Restore need to create the database with files of same size as the original database. So
either shrink before the backup or after the restore. I'd shrink the source database as you have *a
lot* of empty space in there and it is making your operations a bit tricky (apparently).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"Ant-nee" <Antnee@discussions.microsoft.com> wrote in message
news:067CDCA2-165F-4060-811D- F0EA36F168B6@microso
ft.com...
> Hi everyone,
>
> One of our production databases is 30 GB. I sized the database at 30 GB to
> avoid any future fragmentation and to keep the data files contiguous forever.
> There is only about 500 MB of data in the database - the rest is, right now,
> empty space.
>
> Occasionally, I have to restore this database to another instance of SQL.
> The database backup is 500 MB, but when I restore the database, it restores
> itself to the original database's size of 30 GB. I don't want this to happen
> - on my restore, I want just the 500 MB or so of data, not the other 29.5 GB
> of empty space.
>
> How do I do this?
>
> Thanks in advance.
>
> Anthony Rizzo
>
>

Ant-nee

2006-01-25, 3:24 am

Tibor - thanks for the reply.

I was taught by a SQL instructor to always create my original data files
large to avoid future fragmentation. Is this still a good practice?
Eventually, we WILL have 30 GB (or more) data in this database and it will be
an ultra-transaction-heavy data source.

Thanks!

Anthony

"Tibor Karaszi" wrote:

>
> You can't. Restore need to create the database with files of same size as the original database. So
> either shrink before the backup or after the restore. I'd shrink the source database as you have *a
> lot* of empty space in there and it is making your operations a bit tricky (apparently).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "Ant-nee" <Antnee@discussions.microsoft.com> wrote in message
> news:067CDCA2-165F-4060-811D- F0EA36F168B6@microso
ft.com...
>
>

Tibor Karaszi

2006-01-25, 3:25 am

That was good advice. However, letting the database grow a few times (you can monitor and then
expand files manually) isn't a problem. The problem is when you have autogrow, and it grows a large
number of times, and you perhaps even shrink manually, so it grow, shrink, grow, shrink etc.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"Ant-nee" <Antnee@discussions.microsoft.com> wrote in message
news:E592E49D-AFBC-40C0-8DB0- D033F9BE1743@microso
ft.com...[color=darkred]
> Tibor - thanks for the reply.
>
> I was taught by a SQL instructor to always create my original data files
> large to avoid future fragmentation. Is this still a good practice?
> Eventually, we WILL have 30 GB (or more) data in this database and it will be
> an ultra-transaction-heavy data source.
>
> Thanks!
>
> Anthony
>
> "Tibor Karaszi" wrote:
>

Steen Persson (DK)

2006-01-25, 3:25 am

Ant-nee wrote:[color=darkred
]
> Tibor - thanks for the reply.
>
> I was taught by a SQL instructor to always create my original data files
> large to avoid future fragmentation. Is this still a good practice?
> Eventually, we WILL have 30 GB (or more) data in this database and it will be
> an ultra-transaction-heavy data source.
>
> Thanks!
>
> Anthony
>
> "Tibor Karaszi" wrote:
>

Your instructor is basically right, but you might take his word a bit
too seriously...:-).
There are actually two "problems" when a database file will have to
grow. One is the fragmentation and the second is the resources it takes
when the file has to grow. That's why you should avoid that your file(s)
has to grow too often.
When that's said, I think it's a bit overkill to have a 30 GB database
that only contains 500 Mb of data. If you just have moderate growth, it
will take ages before the database is filled up. In the meantime it will
just cause you extra administrative overhead like Tibor is pointing out.
IF you know that all 30 GB is going to be filled up within near future,
I'd leave it like it is, but then you'll also have to get used to deal
with that size of database.

In your case I'd maybe shrink it to e.g. 5 GB and then leave autogrow on
1 GB. Diskspace if fairly cheap today, so unless you have some special
need for a small database, I wouldn't put a lot of effort in trying to
keep it very small - keeping in mind the negative effects of a database
that has to grow too often.

Also keep in mind that you have both the database file and the logfile
that can grow - and take up space. The amount of free space in the
logfile will most likely vary quite a bit over time (assuming that you
run in FULL mode and do regular logfile backups), where the databasefile
will just grow and grow (..of course unless you delete data regularly).

One of my databases has a 65 GB logfile that often only contains a few
hundred MB of data - but every Saturday where we do reindexing it needs
all 65 GB. Therefore it won't be to much help to shrink a file like this
one...:-).

Even though the above maybe wasn't a clear answer on your question, I
hope it cleared things up a little bit so you can make your own judgment
on what to do with the database.

--

Regards
Steen

Sponsored Links