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 database from a TRN file

I am suppose to create a database from a TRN file sent to me. I execute the
following in Query Analyzer:
RESTORE LOG omegaproposal from
disk='c:\temp\OmegaProposal_tlog_200506070000.TRN' WITH NORECOVERY
go
I get this error:
The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
STANDBY for all but the final step.
My question is: Can I create a database from a TRN file? If so, what is
wrong with my statement? The error says I did not specify WITH NORECOVERY
but I obviously did.
Thanks.No, you can not create a db from a transaction log. You can create a db,
restoring from a full db backup.
AMB
"Darin" wrote:
> I am suppose to create a database from a TRN file sent to me. I execute the
> following in Query Analyzer:
> RESTORE LOG omegaproposal from
> disk='c:\temp\OmegaProposal_tlog_200506070000.TRN' WITH NORECOVERY
> go
> I get this error:
> The preceding restore operation did not specify WITH NORECOVERY or WITH
> STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> STANDBY for all but the final step.
> My question is: Can I create a database from a TRN file? If so, what is
> wrong with my statement? The error says I did not specify WITH NORECOVERY
> but I obviously did.
> Thanks.|||You would have to have both the *BAK file (full database backup) and any
*.TRN files that were associated with that backup. A TRN file is only the
transactions since the last transaction log backup or full database backup.
"Darin" wrote:
> I am suppose to create a database from a TRN file sent to me. I execute the
> following in Query Analyzer:
> RESTORE LOG omegaproposal from
> disk='c:\temp\OmegaProposal_tlog_200506070000.TRN' WITH NORECOVERY
> go
> I get this error:
> The preceding restore operation did not specify WITH NORECOVERY or WITH
> STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> STANDBY for all but the final step.
> My question is: Can I create a database from a TRN file? If so, what is
> wrong with my statement? The error says I did not specify WITH NORECOVERY
> but I obviously did.
> Thanks.

Restore database from a TRN file

I am suppose to create a database from a TRN file sent to me. I execute the
following in Query Analyzer:
RESTORE LOG omegaproposal from
disk='c:\temp\OmegaProposal_tlog_200506070000.TRN' WITH NORECOVERY
go
I get this error:
The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
STANDBY for all but the final step.
My question is: Can I create a database from a TRN file? If so, what is
wrong with my statement? The error says I did not specify WITH NORECOVERY
but I obviously did.
Thanks.
No, you can not create a db from a transaction log. You can create a db,
restoring from a full db backup.
AMB
"Darin" wrote:

> I am suppose to create a database from a TRN file sent to me. I execute the
> following in Query Analyzer:
> RESTORE LOG omegaproposal from
> disk='c:\temp\OmegaProposal_tlog_200506070000.TRN' WITH NORECOVERY
> go
> I get this error:
> The preceding restore operation did not specify WITH NORECOVERY or WITH
> STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> STANDBY for all but the final step.
> My question is: Can I create a database from a TRN file? If so, what is
> wrong with my statement? The error says I did not specify WITH NORECOVERY
> but I obviously did.
> Thanks.
|||You would have to have both the *BAK file (full database backup) and any
*.TRN files that were associated with that backup. A TRN file is only the
transactions since the last transaction log backup or full database backup.
"Darin" wrote:

> I am suppose to create a database from a TRN file sent to me. I execute the
> following in Query Analyzer:
> RESTORE LOG omegaproposal from
> disk='c:\temp\OmegaProposal_tlog_200506070000.TRN' WITH NORECOVERY
> go
> I get this error:
> The preceding restore operation did not specify WITH NORECOVERY or WITH
> STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> STANDBY for all but the final step.
> My question is: Can I create a database from a TRN file? If so, what is
> wrong with my statement? The error says I did not specify WITH NORECOVERY
> but I obviously did.
> Thanks.

Restore database from a TRN file

I am suppose to create a database from a TRN file sent to me. I execute the
following in Query Analyzer:
RESTORE LOG omegaproposal from
disk='c:\temp\OmegaProposal_tlog_2005060
70000.TRN' WITH NORECOVERY
go
I get this error:
The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
STANDBY for all but the final step.
My question is: Can I create a database from a TRN file? If so, what is
wrong with my statement? The error says I did not specify WITH NORECOVERY
but I obviously did.
Thanks.No, you can not create a db from a transaction log. You can create a db,
restoring from a full db backup.
AMB
"Darin" wrote:

> I am suppose to create a database from a TRN file sent to me. I execute t
he
> following in Query Analyzer:
> RESTORE LOG omegaproposal from
> disk='c:\temp\OmegaProposal_tlog_2005060
70000.TRN' WITH NORECOVERY
> go
> I get this error:
> The preceding restore operation did not specify WITH NORECOVERY or WITH
> STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> STANDBY for all but the final step.
> My question is: Can I create a database from a TRN file? If so, what is
> wrong with my statement? The error says I did not specify WITH NORECOVERY
> but I obviously did.
> Thanks.|||You would have to have both the *BAK file (full database backup) and any
*.TRN files that were associated with that backup. A TRN file is only the
transactions since the last transaction log backup or full database backup.
"Darin" wrote:

> I am suppose to create a database from a TRN file sent to me. I execute t
he
> following in Query Analyzer:
> RESTORE LOG omegaproposal from
> disk='c:\temp\OmegaProposal_tlog_2005060
70000.TRN' WITH NORECOVERY
> go
> I get this error:
> The preceding restore operation did not specify WITH NORECOVERY or WITH
> STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> STANDBY for all but the final step.
> My question is: Can I create a database from a TRN file? If so, what is
> wrong with my statement? The error says I did not specify WITH NORECOVERY
> but I obviously did.
> Thanks.

Restore .BAK and incremental .TRN each day?

Hello,
Each morning, I have a plan fire at 2 AM that backs up my boss's
favorite database. I also have a 1 hour job that starts at 3 AM which
backs up the transaction log. The database is in FULL recovery and we
don't truncate the logs manually. I would like to know if anyone has
a script or suggestion for restoring the .TRN files all day long.
I can't spend $ on another product so TSQL is likely the answer. I do
not need the database operational at all times, as it is being
restored 'just in case'. I have read that restoring the .TRN files
WITH NORECOVERY is the best method as I don't want to lose anything.
Is it possible to do something like a JOB that uses XP_CMDSHELL to
list the .TRN files and just keeps restoring them? Or should I use an
external script completely? Also, what if the MASTER database
changes? I already looked into a DOS script that adds -m -c, restarts
MSSQL, restores the MASTER, removes the startup switches and then
restarts. Is that the right approach?
I just want to keep the two databases in sync, but cannot use
replication directly do to business rules. BTW, I am using an XCOPY
between the running MSSQL server and the 'just in case' one.
Any insight, TSQL, software recommendations or experiences would help.
Gracias,
TimTim,
What you are describing is referred to as Log Shipping. If you have
Enterprise edition of SQL Server you can use the maintenance wizard to set
this up. If you only have Std you can find scripts in the Resource Kit to
help with this. There is lots of information out there on this subject.
You can also do a google search for Log Shipping and I am sure you will find
some past posts that show some sample scripts.
--
Andrew J. Kelly
SQL Server MVP
"Tim" <SQLRESTORE@.CFAPOSTLE.COM> wrote in message
news:947a9f8a.0311211714.3f670726@.posting.google.com...
> Hello,
> Each morning, I have a plan fire at 2 AM that backs up my boss's
> favorite database. I also have a 1 hour job that starts at 3 AM which
> backs up the transaction log. The database is in FULL recovery and we
> don't truncate the logs manually. I would like to know if anyone has
> a script or suggestion for restoring the .TRN files all day long.
> I can't spend $ on another product so TSQL is likely the answer. I do
> not need the database operational at all times, as it is being
> restored 'just in case'. I have read that restoring the .TRN files
> WITH NORECOVERY is the best method as I don't want to lose anything.
> Is it possible to do something like a JOB that uses XP_CMDSHELL to
> list the .TRN files and just keeps restoring them? Or should I use an
> external script completely? Also, what if the MASTER database
> changes? I already looked into a DOS script that adds -m -c, restarts
> MSSQL, restores the MASTER, removes the startup switches and then
> restarts. Is that the right approach?
> I just want to keep the two databases in sync, but cannot use
> replication directly do to business rules. BTW, I am using an XCOPY
> between the running MSSQL server and the 'just in case' one.
> Any insight, TSQL, software recommendations or experiences would help.
> Gracias,
> Tim