Re: Backup / restore question

From: Hari Prasad (hari_prasad_k_at_hotmail.com)
Date: 03/07/04 

Date: Sun, 7 Mar 2004 09:11:39 +0530

Hi,

You can do this only if your OLD database is in "FULL" recovery model. Below
steps are under the assumption
that database was set to FULL recovery model and the existing database is
still up.

1. In the new server Load the full database backup with NORECOVERY
2. Perform a transaction log backup in Old Server.
3. Copy the TX log backup file to new server
4. Restore the Transaction log WITH RECOVERY , STOPAT='DATE/TIME (refer
Books online)

This will allow you to go back to old data based on the date and time.

Note:

If it is Simple or Bulk_logged recovery model you cant rollback.
POINT-IN-TIME recovery is not supposed in both.

Thanks
Hari
MCDBA

<--> wrote in message news:#xrmzy6AEHA.3788@TK2MSFTNGP10.phx.gbl...
> I had a SQL server with some harddrive problems. So, a full backup was
made
> (manually using Enterprise Manager) and restored in a new machine
> successfully using Enterprise Manager (the option Leave Database
> Operational. No Additional Transaction Logs Can Be Restored was selected)
>
> If the only backup available is the last Full backup, is it possible to
ever
> roll back some of the last changes made in the old machine before the Full
> Backup ?
>
> Thanks for your help.
>
>

Relevant Pages

  • What is the best practice to create Maintenance Plans
    ... There is not a definitive answer to the question of which backup is better. ... the database can be recovered to the point ... Full Recovery or Bulk-Logged Recovery model. ... Transaction Log backup for master database is not allowed. ...
    (microsoft.public.sqlserver.security)
  • Re: Large Transaction Log Backup after Database Backup
    ... script and see for yourself that the database backup doesn't truncate the ... ALTER DATABASE steve SET RECOVERY FULL ... But my understanding was that the transaction log was> truncated after a full database backup. ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction Log Size
    ... Backup database does not empty the transaction log files. ... How to Shrink the SQL Server 7.0 Transaction Log ...
    (microsoft.public.sqlserver.server)
  • Disaster Averted?
    ... Some process, unknown at this point, flooded a transaction log to the point ... the .mdf file was 0 bytes according to EM. ... Following the backup, I ... the database was now Suspect! ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction Log Size
    ... We currently use a 3rd party backup agent to backup our SQL databases and ... transaction logs (Commvault Galaxy iDataAgent for SQL) and the database is ... Because we are using a 3rd party agent to backup the database, ... > here are some articles on how to shrink your transaction log. ...
    (microsoft.public.sqlserver.server)