MS SQL Server New Users - Restore only a file from one server to another

Author

Restore only a file from one server to another

Panagiotis Ladas

2006-03-28, 9:29 am

Hi everybody

I'm supporting a developers team and for testing reasons I have to provide
them with a copy of one of the files in the production database (SQL server
2000) very often. The whole database is restored in the test environment now
and the only data that change very frequently and are needed for the tests
are stored in the PRIMARY filegroup of the production server. The live
database is backed up properly (1 full backup/day, 4 differential/day,
transaction log backup/15 minutes).

The problem obviously is that I can’t figure out how to do it :) All I can
think of doing is backup the needed filegroup whenever it is needed, transfer
it and then restore it on the test server BUT I can’t understand if this is
good enough or not.

I would like to quote something from the 70-228 manual (SQL Server 2000
system administration) that worries me about my way of doing the restore:

"Remember, to restore a database using a file or filegroup backup, you must
restore all transaction log files more recent than the data files you are
restoring in order to ensure that your database is in a consistent state."

Any comments from you would be really helpful and appreciated :)

Regards

Lawrence Garvin

2006-03-28, 8:27 pm

"Panagiotis Ladas" < PanagiotisLadas@disc
ussions.microsoft.com> wrote in
message news:5A3A352E-3923-48E1-89BD- B344DCA9B84F@microso
ft.com...
> Hi everybody
>
> I'm supporting a developers team and for testing reasons I have to provide
> them with a copy of one of the files in the production database (SQL
> server
> 2000) very often.

Can you please clarify that you meant FILE.. and not TABLE?

If so, how have you determined that they need the FILE refreshed, as
opposed to specified TABLES?

> The whole database is restored in the test environment now
> and the only data that change very frequently and are needed for the tests
> are stored in the PRIMARY filegroup of the production server. The live
> database is backed up properly (1 full backup/day, 4 differential/day,
> transaction log backup/15 minutes).

> The problem obviously is that I can't figure out how to do it :) All I can
> think of doing is backup the needed filegroup whenever it is needed,
> transfer
> it and then restore it on the test server BUT I can't understand if this
> is
> good enough or not.

Other options, much more appropriate than a daily backup/restore (Which,
btw, will require the DEV server to be OFFLINE during the filegroup restore
with SQL Server 2000), are Replication and DTS (Data Transformation
Services). My personal preference, based on what you've described, would be
a daily DTS package that refreshes the needed tables on the DEV server from
the LIVE server.

> I would like to quote something from the 70-228 manual (SQL Server 2000
> system administration) that worries me about my way of doing the restore:
>
> "Remember, to restore a database using a file or filegroup backup, you
> must
> restore all transaction log files more recent than the data files you are
> restoring in order to ensure that your database is in a consistent state."
>
> Any comments from you would be really helpful and appreciated :)

Yeah... there's that issue to be dealt with as well... and becomes a
nightmare if you're trying to do partial restores by filegroup.

Frankly, the -ONLY- time you should ever consider doing a restore by
filegroup or FILE, is when a FILE has been physically damaged on the
filesystem of a LIVE server. Even so, on SQL 2000, this is still academic,
as the database will still be offline while you do a file or filegroup
restore, so in that scenario, I'd opt for the more reliable option of doing
a FULL RESTORE from the last database backup. (You'd also need to properly
account for the TAIL of the transaction log, and restoring LOG backups if
you're running in FULL RECOVERY mode.)

Panagiotis Ladas

2006-03-28, 8:27 pm

Hi Lawrence,

thanks a lot for your reply, indeed at the point that I sent the original
message I wasn't sure of what exactly I was trying to say/do. One table of
the database is stored in a different file than the rest of the database and
I want to restore all the database everyday EXCEPT that one table (that is
also a separate file).

It seems to me like I have to read more on DTS as it's probably the easiest
solution for my problem. What I thought of doing is doing a full backup (it's
not that much of a problem), making a safe copy of the table that I want to
exclude, restoring the backup to the DEV server and then copying (somehow???)
the excluded table on the DEV database. Does it work like that? Am I wrong?
Way wrong? Should I just quite and do a completely different job? :)

Once again Lawrence I would like to thank you for your help.

Kind Regards
Panagiotis
--
Panagiotis (Panos) Ladas

"Lawrence Garvin" wrote:

>
>
> "Panagiotis Ladas" < PanagiotisLadas@disc
ussions.microsoft.com> wrote in
> message news:5A3A352E-3923-48E1-89BD- B344DCA9B84F@microso
ft.com...
>
> Can you please clarify that you meant FILE.. and not TABLE?
>
> If so, how have you determined that they need the FILE refreshed, as
> opposed to specified TABLES?
>
>
>
> Other options, much more appropriate than a daily backup/restore (Which,
> btw, will require the DEV server to be OFFLINE during the filegroup restore
> with SQL Server 2000), are Replication and DTS (Data Transformation
> Services). My personal preference, based on what you've described, would be
> a daily DTS package that refreshes the needed tables on the DEV server from
> the LIVE server.
>
>
> Yeah... there's that issue to be dealt with as well... and becomes a
> nightmare if you're trying to do partial restores by filegroup.
>
> Frankly, the -ONLY- time you should ever consider doing a restore by
> filegroup or FILE, is when a FILE has been physically damaged on the
> filesystem of a LIVE server. Even so, on SQL 2000, this is still academic,
> as the database will still be offline while you do a file or filegroup
> restore, so in that scenario, I'd opt for the more reliable option of doing
> a FULL RESTORE from the last database backup. (You'd also need to properly
> account for the TAIL of the transaction log, and restoring LOG backups if
> you're running in FULL RECOVERY mode.)
>
>
>
>

Sponsored Links