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
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
>
>
|||Depending 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
>

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
>
>

Can *datastore.xml be used to populate template.ini as a substitute for setup.iss?

Objective

Do a manual installation of SQL Server 2005 (which could be an upgrade from SQL Server 2000) and record the steps. Replay the steps for an unattended installation of SQL Server 2005.

Problem

There isn't a setup.ini file for SQL Server 2005. There is a template.ini for unattended installations of SQL Server 2005, but it doesn't obviously map to selections on manual installation dialog boxes.

Desired workaround

Capture the steps of a manual installation and use them to populate template.ini.

Relevant background for a potential solution

A manual installation creates log files. So does an unattended installation. But the unattended installation writes all of the log files into a single zip file.

What is important to know is that an unattended installation creates an additional file, a *datasource.xml file. This XML file is found in the zip file with all of the other log files. If you open this XML file, you'll see that the attribute names correspond to keywords in template.ini.

Potential solution

If a manual installation could be forced to generate a *datasource.xml file, then it should be possible to map the settings in the file to keywords in template.ini.

So, how can a *datasource.xml file be created? In post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=359678&SiteID=1, Jeffrey Baker says "add LOGNAME=<path to cab> and run setup again". Unfortunately, these instructions are too vague. Where is this added? To the command line, to a setup.ini (if so, in which directory, and in which section should he addition be made?). I did lots of searches and couldn't find the answers.

Hi John,

In SQL2K we supported the option of "recording" the installation options to a settings file which could be used in subsequent installations. This functionality was not carried forward to SQL2K5.

Using the datasource.xml file, as mentioned above, is not a supported mechanism for creating an input template.ini file. You'll need to manually author the template.ini to contain the installation settings you wish.