MS SQL Server - Tedious Backup and Restore with EM

Author

Tedious Backup and Restore with EM

Don Miller

2006-12-13, 7:12 pm

I do a full backup on a production SQL2K database daily to one file (BAK)
and hourly transaction log backups (that began after the full backup) to
another file (TRN). When I transfer these two backup files to another dev
machine and try to do a restore (as a test) with EM it becomes very tedious.

I first restore the BAK leaving the "database operational but able to
restore transaction logs" after choosing the device and changing the
physical file names for the dev machine. Then, I do the same for the TRN
file (choose transaction log, leave database operational but able to
restore, change the physical file name, etc.). When I add the device, I can
"View Content" and see the hourly transaction log backups, but EM only
allows me to choose one "Backup number" at a time. Then I do the restore
successfully and the window closes and I have to start all over again for
the next backup number in the TRN file starting all over again for each of
the backups (choose transaction log, leave database, etc.). And finally at
the end, I leave the database operational.

Needless to say, this is all very tedious. I've tried selecting "Read backup
set information" but that doesn't appear to do anything.

Am I missing something here? Is there a more efficient/less tedious way to
apply these 23 backups from the transaction log to the full backup?

Thanks for any help or directions.

Uri Dimant

2006-12-13, 7:12 pm

Don
Ofcause it is up to you ,but why you are using EM ? Would not it be simple
to rum a script from QA?

"Don Miller" <nospam@nospam.com> wrote in message
news:OGCuBhsHHHA.1264@TK2MSFTNGP03.phx.gbl...
>I do a full backup on a production SQL2K database daily to one file (BAK)
>and hourly transaction log backups (that began after the full backup) to
>another file (TRN). When I transfer these two backup files to another dev
>machine and try to do a restore (as a test) with EM it becomes very
>tedious.
>
> I first restore the BAK leaving the "database operational but able to
> restore transaction logs" after choosing the device and changing the
> physical file names for the dev machine. Then, I do the same for the TRN
> file (choose transaction log, leave database operational but able to
> restore, change the physical file name, etc.). When I add the device, I
> can "View Content" and see the hourly transaction log backups, but EM only
> allows me to choose one "Backup number" at a time. Then I do the restore
> successfully and the window closes and I have to start all over again for
> the next backup number in the TRN file starting all over again for each of
> the backups (choose transaction log, leave database, etc.). And finally at
> the end, I leave the database operational.
>
> Needless to say, this is all very tedious. I've tried selecting "Read
> backup set information" but that doesn't appear to do anything.
>
> Am I missing something here? Is there a more efficient/less tedious way to
> apply these 23 backups from the transaction log to the full backup?
>
> Thanks for any help or directions.
>

Don Miller

2006-12-13, 7:12 pm

Yes, I can write a script, but I thought I might be missing something using
EM (since it *is* easier at times to just point and click)

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:OnsvQksHHHA.3780@TK2MSFTNGP02.phx.gbl...
> Don
> Ofcause it is up to you ,but why you are using EM ? Would not it be simple
> to rum a script from QA?
>
>
> "Don Miller" <nospam@nospam.com> wrote in message
> news:OGCuBhsHHHA.1264@TK2MSFTNGP03.phx.gbl...
>
>

Tibor Karaszi

2006-12-13, 7:12 pm

> Am I missing something here?

Nope, you are not missing anything. FWIW, 2005 allow you to select several backups from the backup
device so you can "click" them all and let the GUI execute several RESTORE commands in one sweep.

When you have more than a few, you will find it easier to execute the RESTORE commands from a query
window.

Or, you can even generate the restore commands based on the output from RESTORE HEADERONLY, using a
bit of TSQL programming. See http://www.karaszi.com/SQLServer/ u...
file.asp for
example, which would have to be adapted to suit your needs.

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

"Don Miller" <nospam@nospam.com> wrote in message news:OGCuBhsHHHA.1264@TK2MSFTNGP03.phx.gbl...
>I do a full backup on a production SQL2K database daily to one file (BAK) and hourly transaction
>log backups (that began after the full backup) to another file (TRN). When I transfer these two
>backup files to another dev machine and try to do a restore (as a test) with EM it becomes very
>tedious.
>
> I first restore the BAK leaving the "database operational but able to restore transaction logs"
> after choosing the device and changing the physical file names for the dev machine. Then, I do the
> same for the TRN file (choose transaction log, leave database operational but able to restore,
> change the physical file name, etc.). When I add the device, I can "View Content" and see the
> hourly transaction log backups, but EM only allows me to choose one "Backup number" at a time.
> Then I do the restore successfully and the window closes and I have to start all over again for
> the next backup number in the TRN file starting all over again for each of the backups (choose
> transaction log, leave database, etc.). And finally at the end, I leave the database operational.
>
> Needless to say, this is all very tedious. I've tried selecting "Read backup set information" but
> that doesn't appear to do anything.
>
> Am I missing something here? Is there a more efficient/less tedious way to apply these 23 backups
> from the transaction log to the full backup?
>
> Thanks for any help or directions.
>

Don Miller

2006-12-13, 7:12 pm

Thanks for the link. I may have to move to 2005 sooner than planned.

In the meantime, I used RESTORE HEADERONLY on the TRN file and found that
last backup on Production.TRN was on 12/13/06 at 7am (there were 10 sets). I
tried the script below using the stoptime of an hour before the last. After
it completes, the db icon appears in the db list but is gray and says
'Loading' and I cannot access the db (Database 'testRecover' cannot be
opened. It is in the middle of a restore.). I've waited 15 minutes, and then
have to delete the restored db and start over (there seems to be no
activity).

Am I missing something here with the RESTORE LOG and STOPAT command? Do you
have to write a separate script for each log backup set using FILE = 1, FILE
= 2, etc., with the last one using WITH RECOVERY?

Thanks.

RESTORE DATABASE testRecover
FROM DISK = 'C:\Production.BAK' --- has one backup set
WITH NORECOVERY,
MOVE 'Production_dat' TO 'c:\testRecover.mdf',
MOVE 'Production_log' TO 'c:\testRecover_log.ldf'
RESTORE LOG testRecover
FROM DISK = 'C:\Production.TRN' --- has 10 backup sets
WITH RECOVERY,
STOPAT = '2006-12-13 06:00 AM'

"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:O3KMqItHHHA.2232@TK2MSFTNGP02.phx.gbl...
>
> Nope, you are not missing anything. FWIW, 2005 allow you to select several
> backups from the backup device so you can "click" them all and let the GUI
> execute several RESTORE commands in one sweep.
>
> When you have more than a few, you will find it easier to execute the
> RESTORE commands from a query window.
>
> Or, you can even generate the restore commands based on the output from
> RESTORE HEADERONLY, using a bit of TSQL programming. See
> http://www.karaszi.com/SQLServer/ u...
file.asp for example,
> which would have to be adapted to suit your needs.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Don Miller" <nospam@nospam.com> wrote in message
> news:OGCuBhsHHHA.1264@TK2MSFTNGP03.phx.gbl...
>
>

Tibor Karaszi

2006-12-13, 7:12 pm

Assuming you restore the most recent database backup and only the most recent log backups (skipping
log backups in between), then SQL Server should have returned an error message for the attemt to
restore that log backup. Yes, you do have to execute a RESTORE LOG command for each log backup. If
you have them in the same file, you would use FILE = x to specify which one you want to restore.
Something like:

RESTORE DATABASE testRecover
FROM DISK = 'C:\Production.BAK' --- has one backup set
WITH NORECOVERY...
RESTORE LOG testRecover
FROM DISK = 'C:\Production.TRN' --- has 10 backup sets
WITH FILE = 1, NORECOVERY...
RESTORE LOG testRecover
FROM DISK = 'C:\Production.TRN' --- has 10 backup sets
WITH FILE = 2, NORECOVERY...
....
RESTORE LOG testRecover
FROM DISK = 'C:\Production.TRN' --- has 10 backup sets
WITH RECOVERY, FILE = 10,
STOPAT = '2006-12-13 06:00 AM'

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

"Don Miller" <nospam@nospam.com> wrote in message news:uaCTDZtHHHA.3676@TK2MSFTNGP03.phx.gbl...
> Thanks for the link. I may have to move to 2005 sooner than planned.
>
> In the meantime, I used RESTORE HEADERONLY on the TRN file and found that last backup on
> Production.TRN was on 12/13/06 at 7am (there were 10 sets). I tried the script below using the
> stoptime of an hour before the last. After it completes, the db icon appears in the db list but is
> gray and says 'Loading' and I cannot access the db (Database 'testRecover' cannot be opened. It is
> in the middle of a restore.). I've waited 15 minutes, and then have to delete the restored db and
> start over (there seems to be no activity).
>
> Am I missing something here with the RESTORE LOG and STOPAT command? Do you have to write a
> separate script for each log backup set using FILE = 1, FILE = 2, etc., with the last one using
> WITH RECOVERY?
>
> Thanks.
>
> RESTORE DATABASE testRecover
> FROM DISK = 'C:\Production.BAK' --- has one backup set
> WITH NORECOVERY,
> MOVE 'Production_dat' TO 'c:\testRecover.mdf',
> MOVE 'Production_log' TO 'c:\testRecover_log.ldf'
> RESTORE LOG testRecover
> FROM DISK = 'C:\Production.TRN' --- has 10 backup sets
> WITH RECOVERY,
> STOPAT = '2006-12-13 06:00 AM'
>
>
>
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in message
> news:O3KMqItHHHA.2232@TK2MSFTNGP02.phx.gbl...
>
>

Don Miller

2006-12-13, 7:12 pm

Thanks. I found that worked but am disappointed there is not an elegant way
to apply just those log backups up to a point in time (I guess I don't
understand STOPAT).

"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:OJ5XFrtHHHA.1264@TK2MSFTNGP06.phx.gbl...
> Assuming you restore the most recent database backup and only the most
> recent log backups (skipping log backups in between), then SQL Server
> should have returned an error message for the attemt to restore that log
> backup. Yes, you do have to execute a RESTORE LOG command for each log
> backup. If you have them in the same file, you would use FILE = x to
> specify which one you want to restore. Something like:
>
> RESTORE DATABASE testRecover
> FROM DISK = 'C:\Production.BAK' --- has one backup set
> WITH NORECOVERY...
> RESTORE LOG testRecover
> FROM DISK = 'C:\Production.TRN' --- has 10 backup sets
> WITH FILE = 1, NORECOVERY...
> RESTORE LOG testRecover
> FROM DISK = 'C:\Production.TRN' --- has 10 backup sets
> WITH FILE = 2, NORECOVERY...
> ...
> RESTORE LOG testRecover
> FROM DISK = 'C:\Production.TRN' --- has 10 backup sets
> WITH RECOVERY, FILE = 10,
> STOPAT = '2006-12-13 06:00 AM'
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Don Miller" <nospam@nospam.com> wrote in message
> news:uaCTDZtHHHA.3676@TK2MSFTNGP03.phx.gbl...
>
>

Tibor Karaszi

2006-12-13, 7:12 pm

> I found that worked but am disappointed there is not an elegant way to apply just those log
> backups up to a point in time

SQL Server doesn't have this type of intelligence. If you have, say, 10 log backups and you want to
STOPAT somewhere during the time of the 10:th log backup, you have to restore each log backup (as
separate RESTORE LOG commands) and specify STOPAT the 10:th RESTORE LOG command.

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

"Don Miller" <nospam@nospam.com> wrote in message news:%233OyputHHHA.816@TK2MSFTNGP06.phx.gbl...
> Thanks. I found that worked but am disappointed there is not an elegant way to apply just those
> log backups up to a point in time (I guess I don't understand STOPAT).
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in message
> news:OJ5XFrtHHHA.1264@TK2MSFTNGP06.phx.gbl...
>
>

Sponsored Links