Restore deleted table from log?

Hi,
If a table was deleted, is it possible to roll back the deletion with the
transaction log?
ThanksCheck out www.lumigent.com.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"BDB" <bdb@.reply.to.group.com> wrote in message
news:%23iSygiyYGHA.508@.TK2MSFTNGP02.phx.gbl...
Hi,
If a table was deleted, is it possible to roll back the deletion with the
transaction log?
Thanks|||Okay this is what I have:
BACKUP LOG mydb
TO DISK= 'mydblog200604180415.bak'
WITH NO_TRUNCATE, INIT
go
use master
go
RESTORE LOG mydb
FROM DISK= 'mydblog200604180415.bak'
WITH STOPAT = 'Apr 18, 2006 3:30 PM'
I get this error:
Msg 3117, Level 16, State 1, Line 2
The log or differential backup cannot be restored because no files are ready
to rollforward.
Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.
Can anyone help?|||"BDB" <bdb@.reply.to.group.com> wrote in message
news:%23iSygiyYGHA.508@.TK2MSFTNGP02.phx.gbl...
> Hi,
> If a table was deleted, is it possible to roll back the deletion with the
> transaction log?
You can restore the database and then all logs up to the point in time just
before it was deleted.
If you mean recover the table w/o doing that, http://www.lumigent.com/ may
help.
> Thanks
>|||When you restore log, you first need to restore database, then the log backups, in order. I suggest
you read some in Books Online about backup and restore to get a grip on how this work. Also, you
might want to check out: http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BDB" <bdb@.reply.to.group.com> wrote in message news:%23$zI%23BzYGHA.428@.TK2MSFTNGP02.phx.gbl...
> Okay this is what I have:
> BACKUP LOG mydb
> TO DISK= 'mydblog200604180415.bak'
> WITH NO_TRUNCATE, INIT
> go
> use master
> go
> RESTORE LOG mydb
> FROM DISK= 'mydblog200604180415.bak'
> WITH STOPAT = 'Apr 18, 2006 3:30 PM'
> I get this error:
> Msg 3117, Level 16, State 1, Line 2
> The log or differential backup cannot be restored because no files are ready to rollforward.
> Msg 3013, Level 16, State 1, Line 2
> RESTORE LOG is terminating abnormally.
>
> Can anyone help?
>|||Just keep in mind the below point in the article to minimise impact on
production db.
* Restore into another database and copy the affected data into the
production database.
Thanks
Ajay Rengunthwarsql

Restore DB without Transaction Log

Hi,

How can I restore a DB without TRN log. As database transaction logs grows too much and I only want to restore the database.

Thanks

Shafiq

If you only restore the transaction log then the database will be transactionally inconsistent - the transaction log is an integral part of the backup.

What's the scenario context here?

BTW, what size DB and transaction log are we talking about here?

Thanks

|||

1. You must change your recovery model to simple and the backups wont record the transaction logs, this is the easiest way, but You shouldnt restore a backup without transaction logs, its supposed for you to have the info upto date until the failover.

|||

You should be wary about just going to simple recovery model. This is not suitable for production systems where loss of recent changes (in the event of a problem) is not acceptable.

This is because after a data backup in the simple recovery model, the transaction log is truncated. This means that its only possible to restore back to the time the last backup was completed - its not possible to do a point-in-time restore up to just before the problem occured so all work between those two points is lost.

See the SQL Server 2005 BOL entry for 'Overview of Simple Recovery' for more details.

Thanks

restore db without the log?

Is it possible in sql2k to restore a db without restoring the transaction
log? If so, how?
TIA, ChrisRHi,
If you have the FULL Database backup you could restore the full database.
See RESTORE DATABASE command in books online.
Log restore will help you to recover the database till POINT IN TIME.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Is it possible in sql2k to restore a db without restoring the transaction
> log? If so, how?
> TIA, ChrisR
>|||Hari, I have a FULL backup and want to restore the full db, minus the 20 gig
transaction log.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> If you have the FULL Database backup you could restore the full database.
> See RESTORE DATABASE command in books online.
> Log restore will help you to recover the database till POINT IN TIME.
> Thanks
> Hari
> SQL Server MVP
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
>> Is it possible in sql2k to restore a db without restoring the transaction
>> log? If so, how?
>> TIA, ChrisR
>|||If you mean without creating the transaction log file then no. The restore
process from a FULL backup will always rebuild the db and log files exactly
as they were when you made the backup. You can detach the db and attach it
with sing_file_db and it will create a new log file. I am not sure how
large it makes the new file though. It's probably the size you originally
created it at or explicitly did an alter file on it.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23dKNBNMiFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Hari, I have a FULL backup and want to restore the full db, minus the 20
> gig transaction log.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> If you have the FULL Database backup you could restore the full database.
>> See RESTORE DATABASE command in books online.
>> Log restore will help you to recover the database till POINT IN TIME.
>> Thanks
>> Hari
>> SQL Server MVP
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
>> Is it possible in sql2k to restore a db without restoring the
>> transaction log? If so, how?
>> TIA, ChrisR
>>
>

Restore Db

Hi ,
When I perform a restore transaction log on my server , I enounter an
issue saying that my db get lock and the restore transaction failed. How can
I set to no lock the database ? It is possible ? I am using the command below
to execute my restore log process. Actually what is the "with stanby" means ?
Am I doing the right command ? Please advise.
RESTORE LOG PDDIRECTLINK FROM disk = @.FileName with standby = 'C:\LaiDBBackup\Undo\Undo_PdDirectLink.dat'
--
Travis Tan> When I perform a restore transaction log on my server , I enounter an
> issue saying that my db get lock and the restore transaction failed.
You cannot have any connections in the database when you do RESTORE. This includes the connection
from where you run the RESTORE command.
> How can
> I set to no lock the database ? It is possible ?
No, you need to make sure that there are no users in the database.
> Actually what is the "with stanby" means ?
It is documented in Books Online. Read about the RESTORE LOG command and you will find it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:FEBA7216-7152-4605-B8B3-85C2C8C6C274@.microsoft.com...
> Hi ,
> When I perform a restore transaction log on my server , I enounter an
> issue saying that my db get lock and the restore transaction failed. How can
> I set to no lock the database ? It is possible ? I am using the command below
> to execute my restore log process. Actually what is the "with stanby" means ?
> Am I doing the right command ? Please advise.
> RESTORE LOG PDDIRECTLINK FROM disk = @.FileName with standby => 'C:\LaiDBBackup\Undo\Undo_PdDirectLink.dat'
> --
> Travis Tan|||Do
Alter database <dbname> set single_user with rollback immediate
Now Restore database.
now run
Alter database <dbname> multi_user
Regards
Amish Shahsql

Restore Db

Hi ,
When I perform a restore transaction log on my server , I enounter an
issue saying that my db get lock and the restore transaction failed. How can
I set to no lock the database ? It is possible ? I am using the command below
to execute my restore log process. Actually what is the "with stanby" means ?
Am I doing the right command ? Please advise.
RESTORE LOG PDDIRECTLINK FROM disk = @.FileName with standby =
'C:\LaiDBBackup\Undo\Undo_PdDirectLink.dat'
Travis Tan
> When I perform a restore transaction log on my server , I enounter an
> issue saying that my db get lock and the restore transaction failed.
You cannot have any connections in the database when you do RESTORE. This includes the connection
from where you run the RESTORE command.

> How can
> I set to no lock the database ? It is possible ?
No, you need to make sure that there are no users in the database.

> Actually what is the "with stanby" means ?
It is documented in Books Online. Read about the RESTORE LOG command and you will find it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:FEBA7216-7152-4605-B8B3-85C2C8C6C274@.microsoft.com...
> Hi ,
> When I perform a restore transaction log on my server , I enounter an
> issue saying that my db get lock and the restore transaction failed. How can
> I set to no lock the database ? It is possible ? I am using the command below
> to execute my restore log process. Actually what is the "with stanby" means ?
> Am I doing the right command ? Please advise.
> RESTORE LOG PDDIRECTLINK FROM disk = @.FileName with standby =
> 'C:\LaiDBBackup\Undo\Undo_PdDirectLink.dat'
> --
> Travis Tan
|||Do
Alter database <dbname> set single_user with rollback immediate
Now Restore database.
now run
Alter database <dbname> multi_user
Regards
Amish Shah

Restore Db

Hi ,
When I perform a restore transaction log on my server , I enounter an
issue saying that my db get lock and the restore transaction failed. How can
I set to no lock the database ? It is possible ? I am using the command belo
w
to execute my restore log process. Actually what is the "with stanby" means
?
Am I doing the right command ? Please advise.
RESTORE LOG PDDIRECTLINK FROM disk = @.FileName with standby =
'C:\LaiDBBackup\Undo\Undo_PdDirectLink.dat'
--
Travis Tan> When I perform a restore transaction log on my server , I enounter an
> issue saying that my db get lock and the restore transaction failed.
You cannot have any connections in the database when you do RESTORE. This in
cludes the connection
from where you run the RESTORE command.

> How can
> I set to no lock the database ? It is possible ?
No, you need to make sure that there are no users in the database.

> Actually what is the "with stanby" means ?
It is documented in Books Online. Read about the RESTORE LOG command and you
will find it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:FEBA7216-7152-4605-B8B3-85C2C8C6C274@.microsoft.com...
> Hi ,
> When I perform a restore transaction log on my server , I enounter an
> issue saying that my db get lock and the restore transaction failed. How c
an
> I set to no lock the database ? It is possible ? I am using the command be
low
> to execute my restore log process. Actually what is the "with stanby" mean
s ?
> Am I doing the right command ? Please advise.
> RESTORE LOG PDDIRECTLINK FROM disk = @.FileName with standby =
> 'C:\LaiDBBackup\Undo\Undo_PdDirectLink.dat'
> --
> Travis Tan|||Do
Alter database <dbname> set single_user with rollback immediate
Now Restore database.
now run
Alter database <dbname> multi_user
Regards
Amish Shahsql

Restore database without transaction log

Hi,
I lost transaction log of SQL 2000 database.
How i can restore the database without transaction log ?
What is restore sequence ?
I can't attach the database only:it requires log file
--
Regards
AlexAlex
Take a look ate:
sp_attach_single_file_db
This may be what you are after.
--
-- cranfield, DBA
"alext12" wrote:
> Hi,
> I lost transaction log of SQL 2000 database.
> How i can restore the database without transaction log ?
> What is restore sequence ?
> I can't attach the database only:it requires log file
> --
> Regards
> Alex|||cranfield ,
we receive error something about NLS code is incorrect
--
Regards
Alex
"Cranfield" wrote:
> Alex
> Take a look ate:
> sp_attach_single_file_db
>
> This may be what you are after.
> --
> -- cranfield, DBA
>
> "alext12" wrote:
> > Hi,
> >
> > I lost transaction log of SQL 2000 database.
> > How i can restore the database without transaction log ?
> > What is restore sequence ?
> > I can't attach the database only:it requires log file
> > --
> > Regards
> > Alex|||Have you got a backup of the DB? Without a DB backup you can't restore
anything, regardless of whether you have tlog backups or not.
When you say you "lost" the transaction log, do you mean it's corrupt?
or you've stopped the SQL instance, misplaced the ldf file and now the
server can't recover the DB on start up? or something else?
--
Mike
alext12 wrote:
> cranfield ,
> we receive error something about NLS code is incorrect

Restore database without transaction log

Hi,
I lost transaction log of SQL 2000 database.
How i can restore the database without transaction log ?
What is restore sequence ?
I can't attach the database only:it requires log file
Regards
Alex
Alex
Take a look ate:
sp_attach_single_file_db
This may be what you are after.
-- cranfield, DBA
"alext12" wrote:

> Hi,
> I lost transaction log of SQL 2000 database.
> How i can restore the database without transaction log ?
> What is restore sequence ?
> I can't attach the database only:it requires log file
> --
> Regards
> Alex
|||cranfield ,
we receive error something about NLS code is incorrect
Regards
Alex
"Cranfield" wrote:
[vbcol=seagreen]
> Alex
> Take a look ate:
> sp_attach_single_file_db
>
> This may be what you are after.
> --
> -- cranfield, DBA
>
> "alext12" wrote:
sql

Restore database without transaction log

Hi,
I lost transaction log of SQL 2000 database.
How i can restore the database without transaction log ?
What is restore sequence ?
I can't attach the database only:it requires log file
--
Regards
AlexAlex
Take a look ate:
sp_attach_single_file_db
This may be what you are after.
-- cranfield, DBA
"alext12" wrote:

> Hi,
> I lost transaction log of SQL 2000 database.
> How i can restore the database without transaction log ?
> What is restore sequence ?
> I can't attach the database only:it requires log file
> --
> Regards
> Alex|||cranfield ,
we receive error something about NLS code is incorrect
--
Regards
Alex
"Cranfield" wrote:
[vbcol=seagreen]
> Alex
> Take a look ate:
> sp_attach_single_file_db
>
> This may be what you are after.
> --
> -- cranfield, DBA
>
> "alext12" wrote:
>|||Have you got a backup of the DB? Without a DB backup you can't restore
anything, regardless of whether you have tlog backups or not.
When you say you "lost" the transaction log, do you mean it's corrupt?
or you've stopped the SQL instance, misplaced the ldf file and now the
server can't recover the DB on start up? or something else?
Mike
alext12 wrote:
> cranfield ,
> we receive error something about NLS code is incorrect

Restore database with no log

I'm trying to restore a database backed up a production machine onto
my development machine. I don't want to restore the transaction logs
(there are two) because they are far too large and are unnecessary for
what I'm doing. I would be happy to simply create one new log in my
development environment. Therefore, I backed up the database using
the No_Log option as in

backup database CPTS to disk = 'd:\backups\cpts_backup_billeh.bak'
with no_log

I took the backup file and I'm able to restore it on my development
machine with

RESTORE DATABASE [CPTS] FILE = N'CPTS_Data' FROM DISK = N'D:\archives
\Citrix\CPTS\SupportingMaterials\cpts_backup_bille h.bak' WITH MOVE
'CPTS_Data' TO 'd:\databases\CPTS.mdf', MOVE 'CPTS_Log' TO 'd:
\databases\CPTS_log.ldf', MOVE 'CPTS_1_Log' TO 'd:\databases
\CPTS_1_log.ldf'

Note that I cannot additionally specify FILE = N'CPTS_Log' or FILE =
N'CPTS_1_Log' because I get an error.

The restore created the CPTS_Data file only and returned the message

Quote:

Originally Posted by

Quote:

Originally Posted by

>>Processed 415664 pages for database 'CPTS', file 'CPTS_Data' on file 1.

The database cannot be recovered because the log was not restored.<<

What additional step should I take to create a log file? I thought
that No_Log was supposed to give me a truncated log file but it didn't
seem to do so.

Is there another way around this problem? I tried taking the the
resulting mdf file and attaching it using sp_attach_single_file_db but
this didn't work either.

Thanks,

Bill E.
Hollywood, FLI should add that both production and development environments are SQL
Server 2005|||Bill E. (billmiami2@.netscape.net) writes:

Quote:

Originally Posted by

I'm trying to restore a database backed up a production machine onto
my development machine. I don't want to restore the transaction logs
(there are two) because they are far too large and are unnecessary for
what I'm doing. I would be happy to simply create one new log in my
development environment. Therefore, I backed up the database using
the No_Log option as in
>...

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>Processed 415664 pages for database 'CPTS', file 'CPTS_Data' on file 1.

The database cannot be recovered because the log was not restored.<<
>
What additional step should I take to create a log file? I thought
that No_Log was supposed to give me a truncated log file but it didn't
seem to do so.

Books Online says about NO_LOG:

In the context of a BACKUP DATABASE statement, specifies that a backup
will not contain any log. This equates to how file backups were created
before SQL Server 2005. A database backup created with NO_LOG equates
to a full set of file backups that contains no log records.

Under the full recovery model, NO_LOG is useful if you need to back up
data quickly, and you have a complete sequence of log backups of that
data.

The last paragraph implies that you are expected to apply the transaction
logs from elsewhere.

There is a reason why this does not work as you had expected: you
see, that log serves a purpose. A database backup is taken online, which
means that pages can be updated while it's working. Therefore the
backup must include log records, so that transactions that were committed
when the backup was running can be rolled forward. And more importantly,
transactions that had not yet been committed when the backup completed,
must be rolled back. Thus a database restored from a backup with no log
content is in a inconsistent state, and thus you are not permitted in.

I would suggest that you do a regular backup/restore, and then shrink
the log files once you have restored the database and set the recovery
mode to simple. A tip is to add WITH COPY_ONLY to the BACKUP command.
That prevents the backup from being recorded as a "real" backup.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You're not being nosey. I don't know why this little PC was formatted
that way, but it was. It's a Windows x64 PC. I don't think that I'm
going to go through the trouble of reformatting the drives. This is
the first time that the issue has come arisen.

Bill

Restore Database w/Large Transaction Log File

I have a SQL Server 2000 database that is approximately 300 GB in size and I
auto grew the database transaction log file to 200 GB in size. I grew the
database transaction log file to 200 GB in size since that was the largest
size the file grew.
Weekly I copy the database backup file to a disk and send it to the software
vendor.
Is there away to restore this database without the database transaction log
file growing to 200 GB in size?
To restore the database transaction log file without all of the unused space.
I would like the software vendor not to have to purchase another disk just
to restore an almost empty database transaction log file.
Thank You,This is a multi-part message in MIME format.
--=_NextPart_000_002A_01C4E08C.1DE69330
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Hello there,
Shrink the transaction log file before backing up the database.
Tunji O
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message =news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
I have a SQL Server 2000 database that is approximately 300 GB in size =and I auto grew the database transaction log file to 200 GB in size. I grew =the database transaction log file to 200 GB in size since that was the =largest size the file grew.
Weekly I copy the database backup file to a disk and send it to the =software vendor.
Is there away to restore this database without the database =transaction log file growing to 200 GB in size?
To restore the database transaction log file without all of the unused =space.
I would like the software vendor not to have to purchase another disk =just to restore an almost empty database transaction log file.
Thank You,
--=_NextPart_000_002A_01C4E08C.1DE69330
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hello there,
Shrink the transaction log file before =backing up the database.
Tunji O
"Joe K." wrote in message news:46F=71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...I have a SQL Server 2000 database that is approximately 300 GB in size =and I auto grew the database transaction log file to 200 GB in =size. I grew the database transaction log file to 200 GB in size since =that was the largest size the file grew.Weekly I copy the database =backup file to a disk and send it to the software vendor.Is there =away to restore this database without the database transaction log file =growing to 200 GB in size?To restore the database transaction log file =without all of the unused space.I would like the software vendor not =to have to purchase another disk just to restore an almost empty database transaction log file. Thank You,

---

--=_NextPart_000_002A_01C4E08C.1DE69330--|||Shrinking a database transaction log file that has that has been pre-gone to
200 GB in size, will not save any disk space on the restore.
Please help me resolve the problem listed below.
Thanks,
"Tunji O" wrote:
> Hello there,
> Shrink the transaction log file before backing up the database.
> Tunji O
> "Joe K." <JoeK@.discussions.microsoft.com> wrote in message news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
> I have a SQL Server 2000 database that is approximately 300 GB in size and I
> auto grew the database transaction log file to 200 GB in size. I grew the
> database transaction log file to 200 GB in size since that was the largest
> size the file grew.
> Weekly I copy the database backup file to a disk and send it to the software
> vendor.
> Is there away to restore this database without the database transaction log
> file growing to 200 GB in size?
> To restore the database transaction log file without all of the unused space.
> I would like the software vendor not to have to purchase another disk just
> to restore an almost empty database transaction log file.
> Thank You,|||A restored database is exactly like the original. Only the database name
and file names can be changed during the restore process.
As Tunji O said, you need to shrink the transaction log file before taking
the backup. You cannot shrink it during the restore.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:A1B27173-EA51-41A0-9A19-560229412D82@.microsoft.com...
> Shrinking a database transaction log file that has that has been pre-gone
> to
> 200 GB in size, will not save any disk space on the restore.
> Please help me resolve the problem listed below.
> Thanks,
>
> "Tunji O" wrote:
>> Hello there,
>> Shrink the transaction log file before backing up the database.
>> Tunji O
>> "Joe K." <JoeK@.discussions.microsoft.com> wrote in message
>> news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
>> I have a SQL Server 2000 database that is approximately 300 GB in size
>> and I
>> auto grew the database transaction log file to 200 GB in size. I grew
>> the
>> database transaction log file to 200 GB in size since that was the
>> largest
>> size the file grew.
>> Weekly I copy the database backup file to a disk and send it to the
>> software
>> vendor.
>> Is there away to restore this database without the database transaction
>> log
>> file growing to 200 GB in size?
>> To restore the database transaction log file without all of the unused
>> space.
>> I would like the software vendor not to have to purchase another disk
>> just
>> to restore an almost empty database transaction log file.
>> Thank You,|||If you shrink th elog file before you back it up then when you restore that
backup the log file will be the size it was when you backed it up. A 200GB
log file is pretty large for a 300GB database. I suspect you are not doing
regular log backups if it auto grew that much.
--
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:A1B27173-EA51-41A0-9A19-560229412D82@.microsoft.com...
> Shrinking a database transaction log file that has that has been pre-gone
> to
> 200 GB in size, will not save any disk space on the restore.
> Please help me resolve the problem listed below.
> Thanks,
>
> "Tunji O" wrote:
>> Hello there,
>> Shrink the transaction log file before backing up the database.
>> Tunji O
>> "Joe K." <JoeK@.discussions.microsoft.com> wrote in message
>> news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
>> I have a SQL Server 2000 database that is approximately 300 GB in size
>> and I
>> auto grew the database transaction log file to 200 GB in size. I grew
>> the
>> database transaction log file to 200 GB in size since that was the
>> largest
>> size the file grew.
>> Weekly I copy the database backup file to a disk and send it to the
>> software
>> vendor.
>> Is there away to restore this database without the database transaction
>> log
>> file growing to 200 GB in size?
>> To restore the database transaction log file without all of the unused
>> space.
>> I would like the software vendor not to have to purchase another disk
>> just
>> to restore an almost empty database transaction log file.
>> Thank You,|||If I am reading you correctly, the backup has already been trasmitted to the
vendor. At this point, the restore will reproduce the files, data and
transaction logs, exactly as they were when the backup was taken. You only
have the option of renaming the database and/or location and names of the
files, not their sizes nor their content.
If you want to achieve what you have specified, YOU WILL HAVE TO DO IT
AGAIN, just like the other respondents have told you: shrink the log file,
backup the database, transmit the backup, manually regrow the transaction
log back to its original size.
AUTOGROW is a nice feature, in a pinch. However, as the Database
Administrator, you need to manually allocate a reasonable intitial size for
the data and log files, especially for the log files.
If you run the DBCC LOGINFO command on the database, I suspect you will see
thousands of VLOG entries. That many VLOGs is inefficient. If you were to
manually grow an initial model database log file to the allocation you want,
you will probably only see 20 or so VLog entries. This is what you want.
The other possibility is as what one of the other respondents has stated,
you may not be applying an appropriate disaster recovery backup schedule by
including regular transaction log backups.
Sincerely,
Anthony Thomas
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:A1B27173-EA51-41A0-9A19-560229412D82@.microsoft.com...
Shrinking a database transaction log file that has that has been pre-gone to
200 GB in size, will not save any disk space on the restore.
Please help me resolve the problem listed below.
Thanks,
"Tunji O" wrote:
> Hello there,
> Shrink the transaction log file before backing up the database.
> Tunji O
> "Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
> I have a SQL Server 2000 database that is approximately 300 GB in size
and I
> auto grew the database transaction log file to 200 GB in size. I grew
the
> database transaction log file to 200 GB in size since that was the
largest
> size the file grew.
> Weekly I copy the database backup file to a disk and send it to the
software
> vendor.
> Is there away to restore this database without the database transaction
log
> file growing to 200 GB in size?
> To restore the database transaction log file without all of the unused
space.
> I would like the software vendor not to have to purchase another disk
just
> to restore an almost empty database transaction log file.
> Thank You,

Restore Database w/Large Transaction Log File

I have a SQL Server 2000 database that is approximately 300 GB in size and I
auto grew the database transaction log file to 200 GB in size. I grew the
database transaction log file to 200 GB in size since that was the largest
size the file grew.
Weekly I copy the database backup file to a disk and send it to the software
vendor.
Is there away to restore this database without the database transaction log
file growing to 200 GB in size?
To restore the database transaction log file without all of the unused space.
I would like the software vendor not to have to purchase another disk just
to restore an almost empty database transaction log file.
Thank You,
Hello there,
Shrink the transaction log file before backing up the database.
Tunji O
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
I have a SQL Server 2000 database that is approximately 300 GB in size and I
auto grew the database transaction log file to 200 GB in size. I grew the
database transaction log file to 200 GB in size since that was the largest
size the file grew.
Weekly I copy the database backup file to a disk and send it to the software
vendor.
Is there away to restore this database without the database transaction log
file growing to 200 GB in size?
To restore the database transaction log file without all of the unused space.
I would like the software vendor not to have to purchase another disk just
to restore an almost empty database transaction log file.
Thank You,
|||Shrinking a database transaction log file that has that has been pre-gone to
200 GB in size, will not save any disk space on the restore.
Please help me resolve the problem listed below.
Thanks,
"Tunji O" wrote:

> Hello there,
> Shrink the transaction log file before backing up the database.
> Tunji O
> "Joe K." <JoeK@.discussions.microsoft.com> wrote in message news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
> I have a SQL Server 2000 database that is approximately 300 GB in size and I
> auto grew the database transaction log file to 200 GB in size. I grew the
> database transaction log file to 200 GB in size since that was the largest
> size the file grew.
> Weekly I copy the database backup file to a disk and send it to the software
> vendor.
> Is there away to restore this database without the database transaction log
> file growing to 200 GB in size?
> To restore the database transaction log file without all of the unused space.
> I would like the software vendor not to have to purchase another disk just
> to restore an almost empty database transaction log file.
> Thank You,
|||A restored database is exactly like the original. Only the database name
and file names can be changed during the restore process.
As Tunji O said, you need to shrink the transaction log file before taking
the backup. You cannot shrink it during the restore.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:A1B27173-EA51-41A0-9A19-560229412D82@.microsoft.com...[vbcol=seagreen]
> Shrinking a database transaction log file that has that has been pre-gone
> to
> 200 GB in size, will not save any disk space on the restore.
> Please help me resolve the problem listed below.
> Thanks,
>
> "Tunji O" wrote:
|||If you shrink th elog file before you back it up then when you restore that
backup the log file will be the size it was when you backed it up. A 200GB
log file is pretty large for a 300GB database. I suspect you are not doing
regular log backups if it auto grew that much.
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:A1B27173-EA51-41A0-9A19-560229412D82@.microsoft.com...[vbcol=seagreen]
> Shrinking a database transaction log file that has that has been pre-gone
> to
> 200 GB in size, will not save any disk space on the restore.
> Please help me resolve the problem listed below.
> Thanks,
>
> "Tunji O" wrote:
|||If I am reading you correctly, the backup has already been trasmitted to the
vendor. At this point, the restore will reproduce the files, data and
transaction logs, exactly as they were when the backup was taken. You only
have the option of renaming the database and/or location and names of the
files, not their sizes nor their content.
If you want to achieve what you have specified, YOU WILL HAVE TO DO IT
AGAIN, just like the other respondents have told you: shrink the log file,
backup the database, transmit the backup, manually regrow the transaction
log back to its original size.
AUTOGROW is a nice feature, in a pinch. However, as the Database
Administrator, you need to manually allocate a reasonable intitial size for
the data and log files, especially for the log files.
If you run the DBCC LOGINFO command on the database, I suspect you will see
thousands of VLOG entries. That many VLOGs is inefficient. If you were to
manually grow an initial model database log file to the allocation you want,
you will probably only see 20 or so VLog entries. This is what you want.
The other possibility is as what one of the other respondents has stated,
you may not be applying an appropriate disaster recovery backup schedule by
including regular transaction log backups.
Sincerely,
Anthony Thomas

"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:A1B27173-EA51-41A0-9A19-560229412D82@.microsoft.com...
Shrinking a database transaction log file that has that has been pre-gone to
200 GB in size, will not save any disk space on the restore.
Please help me resolve the problem listed below.
Thanks,
"Tunji O" wrote:

> Hello there,
> Shrink the transaction log file before backing up the database.
> Tunji O
> "Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
> I have a SQL Server 2000 database that is approximately 300 GB in size
and I
> auto grew the database transaction log file to 200 GB in size. I grew
the
> database transaction log file to 200 GB in size since that was the
largest
> size the file grew.
> Weekly I copy the database backup file to a disk and send it to the
software
> vendor.
> Is there away to restore this database without the database transaction
log
> file growing to 200 GB in size?
> To restore the database transaction log file without all of the unused
space.
> I would like the software vendor not to have to purchase another disk
just
> to restore an almost empty database transaction log file.
> Thank You,
sql

Restore database to a certain point of time.

I will make it simpler to look...

I have DB1 - as backup for day 1
LOg1 as backup of logs

T1 T2 T3 T4 T5 ...some transaction on day 2

Now i backup again
DB2
Log2

I want to restore the database till the point of transaction T3 say. I
know the time or i assume a certain time.
Is this possible ....i tried several options but hand in between for
some reason or the other. How can i achieve my solution. Is there some
extra parameter i will require or what...i am wondering now that it is
not at all possible. Please help.
RVG
If possible guys can you please mail me the sloution on
raj_chins@.rediffmail.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Rajesh Garg <raj_chins@.rediffmail.com> wrote in message news:<3f4c2fb2$0$62083$75868355@.news.frii.net>...
> I will make it simpler to look...
> I have DB1 - as backup for day 1
> LOg1 as backup of logs
> T1 T2 T3 T4 T5 ...some transaction on day 2
> Now i backup again
> DB2
> Log2
> I want to restore the database till the point of transaction T3 say. I
> know the time or i assume a certain time.
> Is this possible ....i tried several options but hand in between for
> some reason or the other. How can i achieve my solution. Is there some
> extra parameter i will require or what...i am wondering now that it is
> not at all possible. Please help.
> RVG
> If possible guys can you please mail me the sloution on
> raj_chins@.rediffmail.com
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Your description is a little confusing for two reasons - first, you
don't say what time each event is happening at, and second, it's not
clear if you are backing up your log regularly or only once each day.
My guess from your description is that you are doing this:

Day 1

22h00 - BACKUP DATABASE...
22h30 - BACKUP LOG...

Day 2

09h00 - COMMIT txn1
10h00 - COMMIT txn2
11h00 - COMMIT txn3
12h00 - COMMIT txn4

22h00 - BACKUP DATABASE...
22h30 - BACKUP LOG...

Let's say it's now 23h30 on day 2, and you want to recover your
database to 11h00 on day 2. If my assumptions are correct, then you
cannot recover to a point in time because you have no transaction log
backups during Day 2. To recover to a point in time, you first recover
a full backup, then recover your log backups, one after another. But
in your scenario you have no series of transaction backups. At the end
of Day 2 you can recover to Day 1 22h00 (full backup), Day 1 22h30
(full backup + log backup), Day 2 22h00 (full backup), or Day 2 22h30
(full backup + log backup).

To recover to a point in time, you need at least one transaction log
backup after 11h00:

Day 2

09h00 - COMMIT txn1
10h00 - COMMIT txn2
11h00 - COMMIT txn3
12h00 - COMMIT txn4

13h00 - BACKUP LOG...

22h00 - BACKUP DATABASE...
22h30 - BACKUP LOG...

Now you can recover to 11h00: restore day 1 22h00 full + day 1 22h30
log + day 2 13h00 log, recovering only transactions before 11h00.

I may have misunderstood what your situation is - if so, then please
post a clear description of exactly what you are doing (in a similar
way to my example above). In the meantime, I suggest you review the
Books Online backup/restore section, in particular "Using Recovery
Models" and "Backup and Restore Operations".

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0308270354.789e8248@.posting.google.c om...
> Your description is a little confusing for two reasons - first, you
> don't say what time each event is happening at, and second, it's not
> clear if you are backing up your log regularly or only once each day.
> My guess from your description is that you are doing this:
> Day 1
> 22h00 - BACKUP DATABASE...
> 22h30 - BACKUP LOG...
> Day 2
> 09h00 - COMMIT txn1
> 10h00 - COMMIT txn2
> 11h00 - COMMIT txn3
> 12h00 - COMMIT txn4
> 22h00 - BACKUP DATABASE...
> 22h30 - BACKUP LOG...
> Let's say it's now 23h30 on day 2, and you want to recover your
> database to 11h00 on day 2. If my assumptions are correct, then you
> cannot recover to a point in time because you have no transaction log
> backups during Day 2. To recover to a point in time, you first recover
> a full backup, then recover your log backups, one after another. But
> in your scenario you have no series of transaction backups. At the end
> of Day 2 you can recover to Day 1 22h00 (full backup), Day 1 22h30
> (full backup + log backup), Day 2 22h00 (full backup), or Day 2 22h30
> (full backup + log backup).
> To recover to a point in time, you need at least one transaction log
> backup after 11h00:

This is not true (see my reply to Rajesh), a full backup does not truncate
the transaction log, so in your example it is possible to restore from the
first full backup and then use the two transaction logs, completely ignoring
the second full backup.

Ian.

Restore database question

Hello,
I would like to restore my database what come first,
I first restore the transaction log and after the data backup
InaNo,
first you restore your most recent full backup, then you apply the
transaction log backups, in order.
- Baileys
ina wrote:
> Hello,
> I would like to restore my database what come first,
> I first restore the transaction log and after the data backup
> Ina
>|||Hi Ina
How you restore the database will depend on the your recovery mode and what
backups you have.
If you have a full backup and transaction logs check out:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_4zzt.asp
If you want to restore to a point in time check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_18ah.asp
If you have a full backup, differential backup and transaction logs check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_18ah.asp
If you use the second or third method make sure you backup the log first (as
shown in the first link)
John
"ina" wrote:
> Hello,
> I would like to restore my database what come first,
> I first restore the transaction log and after the data backup
> Ina
>|||Thank you but when I select one log all the log of the day are select
together.|||Hi Ina
I guess you are trying to use EM rather than T-SQL?
You still have to restore each log in order, and I believe EM will do this
for you and each log backup in the bakup set up to the log backup that you
select will be checked. i.e. if you had a full backup and a two log backups
then if you selected the first log backup it would check the full backup and
the first log backup, it would then restore the full backup followed by the
log backup. If you selected the second log backup all files would be selected
then the full backup would be restored followed by the log backups in order.
HTH
John
"ina" wrote:
> Thank you but when I select one log all the log of the day are select
> together.
>|||thank you! I have a problem with my restore. So I have this error Error
Database in use. Exclusive access could not be obtained because the
database is in use. Restore database is termatting abnormally
the database is not in use.
Ina|||Hi
I have answered this in your other thread. Your results from sp_who2
indicate that there are connections using the database.
John
"ina" wrote:
> thank you! I have a problem with my restore. So I have this error Error
> Database in use. Exclusive access could not be obtained because the
> database is in use. Restore database is termatting abnormally
> the database is not in use.
> Ina
>|||yes I have done it, but I am newbie and I do not know how to understand
that file yet. :(
Thanks
Ina|||Hi Ina
Time spent reading Books Online will help you, there is a wealth of
information in it. You can get the latest version oof Book Online for SQL
2000 from http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
John
"ina" wrote:
> yes I have done it, but I am newbie and I do not know how to understand
> that file yet. :(
> Thanks
> Ina
>|||thanks, John do you think the problem could be because I am using the
sa login instead of the login of the database I would like to restore?
I have done everything I could but still the same error active database
is in use.
Ina|||Hi
You can only restore the database if there are no connections, you need to
find out why you have these other connections and make sure that they get
disconnected.
Make sure that your own connection is not blocking the database restore by
using the USE command e.g. USE MASTER if you are in Query Analyser.
Once you have managed to disconnect all the users you will be able to
restore the database.
John
"ina" wrote:
> thanks, John do you think the problem could be because I am using the
> sa login instead of the login of the database I would like to restore?
> I have done everything I could but still the same error active database
> is in use.
> Ina
>|||thanks John, I understood the problem is that the service connected to
this database, all time long connect itself to the database (the famous
tester user). I need to disable the service in order to restore the
database.
I really appreciate your help because thanking you I understood a
little more the restore with sql server.
Ina|||Hi Ina
It's good to know you resolved the problem :)
John
"ina" wrote:
> thanks John, I understood the problem is that the service connected to
> this database, all time long connect itself to the database (the famous
> tester user). I need to disable the service in order to restore the
> database.
> I really appreciate your help because thanking you I understood a
> little more the restore with sql server.
> Ina
>|||thank to you :)sql

Restore database question

Hello,
I would like to restore my database what come first,
I first restore the transaction log and after the data backup
InaNo,
first you restore your most recent full backup, then you apply the
transaction log backups, in order.
- Baileys
ina wrote:
> Hello,
> I would like to restore my database what come first,
> I first restore the transaction log and after the data backup
> Ina
>|||Hi Ina
How you restore the database will depend on the your recovery mode and what
backups you have.
If you have a full backup and transaction logs check out:
http://msdn.microsoft.com/library/d...>
kpc_4zzt.asp
If you want to restore to a point in time check out
http://msdn.microsoft.com/library/d...>
kpc_18ah.asp
If you have a full backup, differential backup and transaction logs check ou
t:
http://msdn.microsoft.com/library/d...>
kpc_18ah.asp
If you use the second or third method make sure you backup the log first (as
shown in the first link)
John
"ina" wrote:

> Hello,
> I would like to restore my database what come first,
> I first restore the transaction log and after the data backup
> Ina
>|||Thank you but when I select one log all the log of the day are select
together.|||Hi Ina
I guess you are trying to use EM rather than T-SQL?
You still have to restore each log in order, and I believe EM will do this
for you and each log backup in the bakup set up to the log backup that you
select will be checked. i.e. if you had a full backup and a two log backups
then if you selected the first log backup it would check the full backup and
the first log backup, it would then restore the full backup followed by the
log backup. If you selected the second log backup all files would be selecte
d
then the full backup would be restored followed by the log backups in order.
HTH
John
"ina" wrote:

> Thank you but when I select one log all the log of the day are select
> together.
>|||thank you! I have a problem with my restore. So I have this error Error
Database in use. Exclusive access could not be obtained because the
database is in use. Restore database is termatting abnormally
the database is not in use.
Ina|||Hi
I have answered this in your other thread. Your results from sp_who2
indicate that there are connections using the database.
John
"ina" wrote:

> thank you! I have a problem with my restore. So I have this error Error
> Database in use. Exclusive access could not be obtained because the
> database is in use. Restore database is termatting abnormally
> the database is not in use.
> Ina
>|||yes I have done it, but I am newbie and I do not know how to understand
that file yet.
Thanks
Ina|||Hi Ina
Time spent reading Books Online will help you, there is a wealth of
information in it. You can get the latest version oof Book Online for SQL
2000 from http://www.microsoft.com/sql/prodin...ions/books.mspx
John
"ina" wrote:

> yes I have done it, but I am newbie and I do not know how to understand
> that file yet.
> Thanks
> Ina
>|||thanks, John do you think the problem could be because I am using the
sa login instead of the login of the database I would like to restore?
I have done everything I could but still the same error active database
is in use.
Ina

Restore Database problem - LOADING forever

I had to restore a database late this afternoon. I have the database set to FULL recovery. Database backups are performed nightly and transaction log backups are performed every other hour. I decided to perform a point-in-time restore. When I restored this way everything seems to go ok and it finishes. Then the database is grayed and says "Loading". Although I tried 4 separate times, one time allowing over an hour, the grayed out database and "Loading" never goes away.

Freaking out I deleted the "Loading" database (didn't delete logs and backup files) and tried a manual restore from the previous night's backup file. It attached and restored properly and was ready to go in 2 minutes.

Of course I wanted to get the transaction log files restored too, since it had work from earlier today. So I tried another restore via point-in-time and got the same old messages. Currently, the database is running with the previous night's backup restored but the users aren't too keen on having to do 5 hours worth of work to catch up to the previous transaction log backup come Monday morning.

Any suggestions?

Thanks,
JBHow are you doing the restore? And why do you want to restore to a point in time?

Do it via query analyser with standby - this will enable you to look at the database between restores.

Do the full restore - look at it.

Now do the tr logs in turn - checking the database between each one.
Now you can do the last one with the point in time recovery.

If that fails then go back and redo up to the last one that restored successfully. At least you will lose at the most 2 hours.|||The poster probably did not click on Refresh menu item on Databases folder in EM.sql

Restore Database in Standby without a Transaction undo file

Is there a way to restore a database that is in stand-by mode, if the TUF
file has become corrupted or accidently deleted.
If you do a resotre database with recovery you will get an error saying the
file is not accessiable. Is there a way to force the restore.
You can stop SQL, copy the files and re-attach I think.
It would also be nice if SQL could backup database that are in standby. This
way you can do quick offsite backups.
Hi,
AFAIK, IF the TUF file (undo file) is corrupted or lost you can not restore
the subsequent transaction logs in to your database which is in
stand by mode.
Thanks
Hari
SQL Server MVP
"et" <et@.discussions.microsoft.com> wrote in message
news:A59EF3D5-08C9-4A69-A97F-10EEBAB112B2@.microsoft.com...
> Is there a way to restore a database that is in stand-by mode, if the TUF
> file has become corrupted or accidently deleted.
> If you do a resotre database with recovery you will get an error saying
> the
> file is not accessiable. Is there a way to force the restore.
> You can stop SQL, copy the files and re-attach I think.
> It would also be nice if SQL could backup database that are in standby.
> This
> way you can do quick offsite backups.
|||If the standby file is hosed, so are you. I submitted a SQLWISH a while ago
for SQL Server to lock the standby file just as it does any SQL database
file. I don't know the status of that request.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"et" <et@.discussions.microsoft.com> wrote in message
news:A59EF3D5-08C9-4A69-A97F-10EEBAB112B2@.microsoft.com...
> Is there a way to restore a database that is in stand-by mode, if the TUF
> file has become corrupted or accidently deleted.
> If you do a resotre database with recovery you will get an error saying
> the
> file is not accessiable. Is there a way to force the restore.
> You can stop SQL, copy the files and re-attach I think.
> It would also be nice if SQL could backup database that are in standby.
> This
> way you can do quick offsite backups.

Restore Database in Standby without a Transaction undo file

Is there a way to restore a database that is in stand-by mode, if the TUF
file has become corrupted or accidently deleted.
If you do a resotre database with recovery you will get an error saying the
file is not accessiable. Is there a way to force the restore.
You can stop SQL, copy the files and re-attach I think.
It would also be nice if SQL could backup database that are in standby. This
way you can do quick offsite backups.Hi,
AFAIK, IF the TUF file (undo file) is corrupted or lost you can not restore
the subsequent transaction logs in to your database which is in
stand by mode.
Thanks
Hari
SQL Server MVP
"et" <et@.discussions.microsoft.com> wrote in message
news:A59EF3D5-08C9-4A69-A97F-10EEBAB112B2@.microsoft.com...
> Is there a way to restore a database that is in stand-by mode, if the TUF
> file has become corrupted or accidently deleted.
> If you do a resotre database with recovery you will get an error saying
> the
> file is not accessiable. Is there a way to force the restore.
> You can stop SQL, copy the files and re-attach I think.
> It would also be nice if SQL could backup database that are in standby.
> This
> way you can do quick offsite backups.|||If the standby file is hosed, so are you. I submitted a SQLWISH a while ago
for SQL Server to lock the standby file just as it does any SQL database
file. I don't know the status of that request.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"et" <et@.discussions.microsoft.com> wrote in message
news:A59EF3D5-08C9-4A69-A97F-10EEBAB112B2@.microsoft.com...
> Is there a way to restore a database that is in stand-by mode, if the TUF
> file has become corrupted or accidently deleted.
> If you do a resotre database with recovery you will get an error saying
> the
> file is not accessiable. Is there a way to force the restore.
> You can stop SQL, copy the files and re-attach I think.
> It would also be nice if SQL could backup database that are in standby.
> This
> way you can do quick offsite backups.

Restore Database in Standby without a Transaction undo file

Is there a way to restore a database that is in stand-by mode, if the TUF
file has become corrupted or accidently deleted.
If you do a resotre database with recovery you will get an error saying the
file is not accessiable. Is there a way to force the restore.
You can stop SQL, copy the files and re-attach I think.
It would also be nice if SQL could backup database that are in standby. This
way you can do quick offsite backups.Hi,
AFAIK, IF the TUF file (undo file) is corrupted or lost you can not restore
the subsequent transaction logs in to your database which is in
stand by mode.
Thanks
Hari
SQL Server MVP
"et" <et@.discussions.microsoft.com> wrote in message
news:A59EF3D5-08C9-4A69-A97F-10EEBAB112B2@.microsoft.com...
> Is there a way to restore a database that is in stand-by mode, if the TUF
> file has become corrupted or accidently deleted.
> If you do a resotre database with recovery you will get an error saying
> the
> file is not accessiable. Is there a way to force the restore.
> You can stop SQL, copy the files and re-attach I think.
> It would also be nice if SQL could backup database that are in standby.
> This
> way you can do quick offsite backups.|||If the standby file is hosed, so are you. I submitted a SQLWISH a while ago
for SQL Server to lock the standby file just as it does any SQL database
file. I don't know the status of that request.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"et" <et@.discussions.microsoft.com> wrote in message
news:A59EF3D5-08C9-4A69-A97F-10EEBAB112B2@.microsoft.com...
> Is there a way to restore a database that is in stand-by mode, if the TUF
> file has become corrupted or accidently deleted.
> If you do a resotre database with recovery you will get an error saying
> the
> file is not accessiable. Is there a way to force the restore.
> You can stop SQL, copy the files and re-attach I think.
> It would also be nice if SQL could backup database that are in standby.
> This
> way you can do quick offsite backups.

Restore Database FROM MDF/LDF, then apply Transaction Log From POint of Failure? Is it pOs

If I have a database backup from sunday, and a failure occurs monday... Can the backup .mdf and .ldf files be attached, and the backup log after the point of failure be applied to them?

The problem I am having is it looks like you can only restore from a .bak file, and then apply the log at the point of failure. IT doesn't look like you can restore the .ldf/.mdf files, and then apply the backup log from the point of failure.

Can someone please help? I'm in desparate need of fixing this !

Thanks,
dp

You can restore from backup and apply your log files to recover up to failure unless it is a transactional database you can restore almost everything but you are also supposed to keep a DR(disaster recovery) copy of your database. Try the link below. Hope this helps.

http://www.sql-server-performance.com/log_explorer_spotlight.asp

sql