Restore Transaction Log Backups

Hy Guys,

I have a problem with restore Log.

We make a FULL backup everyday at 22:00.
Transaction LOGs Backups are made at every 30 minutes (all the day) (WITH NO INIT) - One Log Backup per Day

I restored a a FULL Backup database from 10/17/2002 with this code:

RESTORE DATABASE dbTest
from disk = 'd:\MSSQL7\BACKUP\DB_20021017_dbsolomonprodapp.bak '
WITH norecovery
GO

Now I need to restore Transactions until 10/18/2002 16:30.
So, I have to restore
Lg_20021017_dbSolomonProdApp.bak
AND
Lg_20021018_dbSolomonProdApp.bak (Until 16:30)

My problem is.. How to make this Log Restore?Q1 My problem is.. How to make this Log Restore?
A1 Sequentially (in your case, by position similar to when restoring from tape, using time constraints as necessary). I suggest the following:
i I find it best to Dump to individual (dump) files. (multiple reasons)
ii Doing so also makes it clearer to see what you are doing, for example:

Restore Database
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_db_200210250 830.Bak'
With Stats = 1, Replace, NoRecovery,
Move 'dbSolomonProdApp_Data' To 'c:\Test_dbSolomonProdApp_Data.mdf',
Move 'dbSolomonProdApp_Log' To 'c:\Test_dbSolomonProdApp_Log.ldf'

--> Sequential dTL Restore Statements:

--> dTL file: 1
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 50843.Trn'
With Stats = 1, NoRecovery

--> dTL file: 2
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 50913.Trn'
With Stats = 1, NoRecovery

--> dTL file: 3
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 50943.Trn'
With Stats = 1, NoRecovery

--> dTL file: 4
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51013.Trn'
With Stats = 1, NoRecovery

--> dTL file: 5
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51043.Trn'
With Stats = 1, NoRecovery

--> dTL file: 6
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51113.Trn'
With Stats = 1, NoRecovery

--> dTL file: 7
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51143.Trn'
With Stats = 1, NoRecovery

--> dTL file: 8
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51213.Trn'
With Stats = 1, NoRecovery

--> dTL file: 9
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51243.Trn'
With Stats = 1, NoRecovery

--> dTL file: 10
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51313.Trn'
With Stats = 1, NoRecovery

--> dTL file: 11
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51343.Trn'
With Stats = 1, NoRecovery

--> dTL file: 12
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51413.Trn'
With Stats = 1, NoRecovery

--> dTL file: 13
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51443.Trn'
With Stats = 1, NoRecovery

--> dTL file: 14
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51513.Trn'
With Stats = 1, NoRecovery

--> dTL file: 15
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51543.Trn'
With Stats = 1, NoRecovery

--> dTL file: 16
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51613.Trn'
With Stats = 1, NoRecovery

--> dTL file: 17
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51643.Trn'
With Stats = 1, NoRecovery

--> dTL file: 18
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51716.Trn'
With Stats = 1, NoRecovery

--> dTL file: 19
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51743.Trn'
With Stats = 1, NoRecovery

--> dTL file: 20
Restore Log
[Test_SolomonProdApp]
From
Disk = 'C:\dbSolomonProdApp\dbSolomonProdApp_tlog_2002102 51813.Trn'
With Stats = 1, NoRecovery

Restore DataBase
[Test_SolomonProdApp]
With Recovery|||Hy DBA,
Your answer works perfect. But i was with my transaction logs in one file. Then i made like this:

-- This command returns many important information about this backup.
-- Like the date of the backup sets and their numbers.
RESTORE HEADERONLY FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak' WITH NOUNLOAD
GO

--Restoring the last Full Backup
RESTORE DATABASE dbSolomonTeste
from disk = 'd:\20021016_dbsolomonprodapp.bak'
WITH norecovery
GO

--Now I recovery all backup sets in the Log backup
RESTORE LOG [dbSolomonTeste]
FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak'
WITH FILE = 1,
NOUNLOAD ,
STATS = 10,
NORECOVERY
GO

RESTORE LOG [dbSolomonTeste]
FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak'
WITH FILE = 2,
NOUNLOAD ,
STATS = 10,
NORECOVERY
GO

RESTORE LOG [dbSolomonTeste]
FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak'
WITH FILE = 3,
NOUNLOAD ,
STATS = 10,
NORECOVERY
GO
.
.
.

RESTORE LOG [dbSolomonTeste]
FROM DISK = N'D:\Lg_20021016_dbSolomonProdApp.bak'
WITH FILE = 48, --In my Case, the last backup set i needed!
NOUNLOAD ,
STATS = 10,
NORECOVERY
GO

Thank you for helping me!!|||RE: Thank you for helping me!!

You are welcome.

As I alluded to, I generally avoid monolithic TL dump files.
Some pain I've experienced with them includes:
i) You want to move it elsewhere to use it, but cannot because it has become huge (either connectivity loss, corruption, etc., occurs during attempted file transfer processes, or the target only has sufficient free disk space on multiple smaller volumes).
ii) RESTORE HEADERONLY results take FOREVER, and fails before the complete header result set is returned (fortunatly, the needed TL dumps on it were usable).
iii) It (a single monolithic TL dump file) gets corrupted, and the whole thing is useless! (That can be extremely painful for an organization, and is the main reason I implement and / or encourage the use of individual dump files.) While I have seen this happen on a Windows OS fileserver, it seems to happen most frequently on third party NAS boxes, (especially older SNAP storage servers), with large monolithic TL dump files. I don't recall ever seeing it happen on a local data storage volume, however; so, if that is your situuation, you may have relativly little (corruption) risk in using monolithic TL dump files.

Restore Problem

Hi Guys,
I'm having a problem with a restore. Recently a new image column was added
to our database, my request was to place the column in it's own filegroup, I
had planned to do a full restore after the new filegroup was added, but due
to a lack of a change control process the column was added at the weekend
while I was away and no full back up was done. I'm now in a situation where
I have a full backup containing 1 filegroup and several differential and
transactional backups since with 2 filegroups. I've now had a request to
restore an old copy of the database for test purposes but I'm totally unsure
how to restore it... ...for ease I've been using EM as we have numerous
transactional backups in the bak file... ... restoring the full backup and
latest differential results in an error complaining the new filegroup isn't
in the database... ...is the error due to me selecting to restore both at the
same time through EM?... ...Would the database be consistant if I do a full
restore, add the new filegroup, then restore the differential?
Any advice would be very gratefully recieved.
Thanks in advance
BenI got around it using TSQL employing the WITH MOVE operation in the RESTORE
command.
"BenUK" wrote:
> Hi Guys,
> I'm having a problem with a restore. Recently a new image column was added
> to our database, my request was to place the column in it's own filegroup, I
> had planned to do a full restore after the new filegroup was added, but due
> to a lack of a change control process the column was added at the weekend
> while I was away and no full back up was done. I'm now in a situation where
> I have a full backup containing 1 filegroup and several differential and
> transactional backups since with 2 filegroups. I've now had a request to
> restore an old copy of the database for test purposes but I'm totally unsure
> how to restore it... ...for ease I've been using EM as we have numerous
> transactional backups in the bak file... ... restoring the full backup and
> latest differential results in an error complaining the new filegroup isn't
> in the database... ...is the error due to me selecting to restore both at the
> same time through EM?... ...Would the database be consistant if I do a full
> restore, add the new filegroup, then restore the differential?
> Any advice would be very gratefully recieved.
> Thanks in advance
> Ben

Restore Problem

Hi Guys,
I'm having a problem with a restore. Recently a new image column was added
to our database, my request was to place the column in it's own filegroup, I
had planned to do a full restore after the new filegroup was added, but due
to a lack of a change control process the column was added at the weekend
while I was away and no full back up was done. I'm now in a situation where
I have a full backup containing 1 filegroup and several differential and
transactional backups since with 2 filegroups. I've now had a request to
restore an old copy of the database for test purposes but I'm totally unsure
how to restore it... ...for ease I've been using EM as we have numerous
transactional backups in the bak file... ... restoring the full backup and
latest differential results in an error complaining the new filegroup isn't
in the database... ...is the error due to me selecting to restore both at th
e
same time through EM?... ...Would the database be consistant if I do a full
restore, add the new filegroup, then restore the differential?
Any advice would be very gratefully recieved.
Thanks in advance
BenI got around it using TSQL employing the WITH MOVE operation in the RESTORE
command.
"BenUK" wrote:

> Hi Guys,
> I'm having a problem with a restore. Recently a new image column was adde
d
> to our database, my request was to place the column in it's own filegroup,
I
> had planned to do a full restore after the new filegroup was added, but du
e
> to a lack of a change control process the column was added at the weekend
> while I was away and no full back up was done. I'm now in a situation whe
re
> I have a full backup containing 1 filegroup and several differential and
> transactional backups since with 2 filegroups. I've now had a request to
> restore an old copy of the database for test purposes but I'm totally unsu
re
> how to restore it... ...for ease I've been using EM as we have numerous
> transactional backups in the bak file... ... restoring the full backup an
d
> latest differential results in an error complaining the new filegroup isn'
t
> in the database... ...is the error due to me selecting to restore both at
the
> same time through EM?... ...Would the database be consistant if I do a ful
l
> restore, add the new filegroup, then restore the differential?
> Any advice would be very gratefully recieved.
> Thanks in advance
> Ben

Restore Problem

Hi Guys:
I am restoring a databse backup from sql server 2000 cluster to another
server. The restore has been failing with message "RESTORE DATABASE is
terminating abnormally.". When I use restore verify, i get the following
message. Backup is being done on sql server 200 cluster, I am restoring on
another server which is not a cluster. Any help is greatly appreciated
because this is critical and urgent.
Server: Msg 3242, Level 16, State 2, Line 1
The file on device 'C:\temp\LancomeSIS_US_db_200603240243.BAK' is not a
valid Microsoft Tape Format backup set.
Server: Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
ThanksAre you on Windows 2003?
> Hi Guys:
> I am restoring a databse backup from sql server 2000 cluster to another
> server. The restore has been failing with message "RESTORE DATABASE is
> terminating abnormally.". When I use restore verify, i get the following
> message. Backup is being done on sql server 200 cluster, I am restoring on
> another server which is not a cluster. Any help is greatly appreciated
> because this is critical and urgent.
>
> Server: Msg 3242, Level 16, State 2, Line 1
> The file on device 'C:\temp\LancomeSIS_US_db_200603240243.BAK' is not a
> valid Microsoft Tape Format backup set.
> Server: Msg 3013, Level 16, State 1, Line 1
> VERIFY DATABASE is terminating abnormally.
>
> Thanks|||could you paste the output of restore headeronly|||looks like your backup is corrupted, not sure of yet!|||Thanks for replying. Below is the output of the Restore HeaderOnly. Looks
looks like db is corrupt but why. is it something to do with backups
happening on cluster (Raid drive).
BackupName
BackupDescription
BackupType ExpirationDate Compressed
Position DeviceType UserName
ServerName
DatabaseName
DatabaseVersion DatabaseCreationDate
BackupSize FirstLsn LastLsn
CheckpointLsn DifferentialBaseLsn
BackupStartDate BackupFinishDate
SortOrder CodePage UnicodeLocaleId
UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId
SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName
Flags BindingId
RecoveryForkId Collation
------
-----------
-- --- --
-- --
------
------
------
-- ---
-- --
-- --
--
---
--- -- --
-- -- -- --
-- -- --
------
-- ---
---
------
*** INCOMPLETE ***
NULL
NULL NULL NULL
1 NULL NULL
NULL
NULL
NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL
NULL NULL NULL
NULL
"Shadow" wrote:
> looks like your backup is corrupted, not sure of yet!
>