can I bring roll back a trasaction log

I am building a test environment. My objective is to be able to bring the
test database from different stage within 15 minutes. The database mdf file
is 6G and the log file is 500M after truncated. The data is updated not
using transaction commit command.
Existing Plan
1/ prepare a full backup of the database to a file called
FullBackup.bak
2/ at each hour , do a transaction log backup. e.g
job1.trn
3/ I restore the database by applying the full backup bak and individual
transaction.log . But the whole process takes about one hour which does not
meet the requirement to bring back the database within 15 minutes.
Question:
1/ consider the following:
8:00 am full backup
9:00 am transaction log backup job 1
10:00am transaction log backup job 2
11:00am transaction log backup job 3
I need to bring back the database at 9:59am.
Is it a way to apply the transaction log so that the database can undo the
change based on the job3 transaction log, job 2 transaction log?
or
2/ What other way can allow me to bring back a data base at different stage
within 15 minutes?
3/ How to find out what database has been modified every one hour? and
what data has been added/deleted/edited.
Regards
SMS1Depending on your disk configurations, you might reduce the backup and
recovery times by striping your backup files e.g.
BACKUP DATABASE pubs TO DISK = 'f:\backups\pubs1.bak', DISK ='f:\backups\pubs2.bak' ...
When you mentioned that the recovery took 1 hour, did you delete the
database before restoring it? If you did, try restoring without first
deleting the database. It would save some time as it avoids having SQL
Server create a 6 Gig file before the restore process actually starts.
Lastly, depending again on your hardware configuration, you can reduce
backup and restore times by using backup software that works with compressed
SQL Server backup files e.g. our product, MiniSQLBackup.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"sms" <sms1@.pctc.com> wrote in message
news:vsg4d.2575$KF.20421@.tor-nn1.netcom.ca...
> I am building a test environment. My objective is to be able to bring the
> test database from different stage within 15 minutes. The database mdf
file
> is 6G and the log file is 500M after truncated. The data is updated not
> using transaction commit command.
> Existing Plan
> 1/ prepare a full backup of the database to a file called
> FullBackup.bak
> 2/ at each hour , do a transaction log backup. e.g
> job1.trn
> 3/ I restore the database by applying the full backup bak and individual
> transaction.log . But the whole process takes about one hour which does
not
> meet the requirement to bring back the database within 15 minutes.
> Question:
> 1/ consider the following:
> 8:00 am full backup
> 9:00 am transaction log backup job 1
> 10:00am transaction log backup job 2
> 11:00am transaction log backup job 3
> I need to bring back the database at 9:59am.
> Is it a way to apply the transaction log so that the database can undo the
> change based on the job3 transaction log, job 2 transaction log?
> or
> 2/ What other way can allow me to bring back a data base at different
stage
> within 15 minutes?
>
> 3/ How to find out what database has been modified every one hour? and
> what data has been added/deleted/edited.
> Regards
> SMS1
>|||Hi
1. If you need to bring a database back to 9:59am, you need to restore the
last good full backup and all the transaction logs after that, including the
10:00am one, up to the time you need. See "point in time recovery" in BOL
2. This is the only option, as transaction logs can be restored to an exact
point in time.
3. Look at the commercial products LogPI or Log Explorer to examine log files.
Cheers
Mike
"sms" wrote:
> I am building a test environment. My objective is to be able to bring the
> test database from different stage within 15 minutes. The database mdf file
> is 6G and the log file is 500M after truncated. The data is updated not
> using transaction commit command.
> Existing Plan
> 1/ prepare a full backup of the database to a file called
> FullBackup.bak
> 2/ at each hour , do a transaction log backup. e.g
> job1.trn
> 3/ I restore the database by applying the full backup bak and individual
> transaction.log . But the whole process takes about one hour which does not
> meet the requirement to bring back the database within 15 minutes.
> Question:
> 1/ consider the following:
> 8:00 am full backup
> 9:00 am transaction log backup job 1
> 10:00am transaction log backup job 2
> 11:00am transaction log backup job 3
> I need to bring back the database at 9:59am.
> Is it a way to apply the transaction log so that the database can undo the
> change based on the job3 transaction log, job 2 transaction log?
> or
> 2/ What other way can allow me to bring back a data base at different stage
> within 15 minutes?
>
> 3/ How to find out what database has been modified every one hour? and
> what data has been added/deleted/edited.
> Regards
> SMS1
>
>