Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BeefStu
Starting Member
1 Post |
 Posted - 2010-07-22 : 12:30:32
|
| from what I read so far a .trn file is just a backup of a .ldf file.I am using Commvault to backup the .ldf every hour.If I want to restore a database to a certain point in time, lets say 3PM, can I just restore the .mdf from the last time it wasbackedup and the .ldf file, from my 3PM backup. Will that put myDB in sync?If not, can somebody please explain, why I would need a backup ofa .ldf file to create a .trn file to bring my DB back to a currentpoint in time, in this example 3PM.Am I missing something? Thanks in advance to all who answer. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-07-22 : 12:59:24
|
| .LDF and .TRN are simply file extensions, for SQL Server transaction log files and log backup files, respectively. There's no requirement to use either one.Just to clarify, "point-in-time" recovery doesn't just mean "up to the last backup". You can restore to any moment or even a specific transaction as long as a log backup contains that moment or event.I don't know about Commvault, but most backup software does not preserve the transaction log chain for SQL Server to recover to a point in time. Any product that backs up the files only does NOT permit point-in-time recovery. Even Microsoft's DPM product doesn't do this properly for SQL Server. SQL Server maintains markers both inside the database and within the server environment, a simple file backup won't modify these correctly.Your best bet is to use native SQL Server backups, or a SQL Server specific backup software like Quest LiteSpeed or Redgate SQLBackup. These products use the native SQL Server backup commands and preserve the backup chain. |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-07-22 : 13:32:36
|
| Rob, CommVault does have specific SQL Server backup software. It also offers point in time recovery.BeefStu, you may want to contact your CommVault reseller |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-22 : 14:41:20
|
| Not sure its clear from earlier points raise, so I apologise if I'm repeating info you already know.To restore you need:A suitable full backup (doesn't have to be the most recent - e.g. if the most recent is off site / lost / corrupted)An optional Differential backup taken after the above Full backup (but before any subsequent Full backup)Every Transaction Log backup since, in chronological order (you can specify a specific "time" to stop at if you want to restore to part way through the interval between two transaction log backups).Furthermore, in the event of a database corruption, or some such disaster, it is often possible to take a final "tail" Tlog backup which, combined with earlier FULL and Tlog backups, can be used to restore the database to "current" point without data loss. An example would be where the Data and Log files are on different drive systems and the drive(s) the Data file(s) are stored on are toast!I have no knowledge of CommVault, but anything other than SQL Native backups would give me sleepless nights. We answer lots of questions on this forum from people using 3rd party products who only realise they have a problem when they come to try to restore ...At the least you should schedule frequent restores to a different machine (and run DBCC CHECKDB on the restored database) so that you know the backups are good (this advice holds for backups made with SQL Server's own tools, as well as 3rd party tools; there is a side-benefit that there is then no need to also perform DBCC CHECKDB on the actual database - which can be a very time-consuming task on large databases) |
|
|
|
|
|
|
|
|
.ldf vs trn file