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 |
|
vancouver101@hotmail.com
Starting Member
3 Posts |
 Posted - 2004-10-24 : 17:34:03
|
Hello fellow SQL Users & Admins:The objective: having backup/trans logs allowing for a max of 1hr data loss.The problem: Restore doesn't work for all transaction logsEvery night a backup is done with this sql command:BACKUP DATABASE myDatabase TO DISK='C:\db-backup.bak' with initevery hour during the day a transaction log backup is done:BACKUP LOG myDatabase TO DISK=@LogNameWhere @logName is 'C:\translog' + hour '.trn'Now this all works fine enough, producing a backup and then a series of transaction log files. The problem comes when trying to restore the transaction logs. I issue the following statements:RESTORE DATABASE myDatabase FROM DISK='C:\db-backup.bak' WITH NORECOVERYRESTORE LOG myDatabase FROM DISK='C:\transLog01.trn' WITH NORECOVERYRESTORE LOG myDatabase FROM DISK='C:\transLog02.trn' WITH NORECOVERYRESTORE LOG myDatabase FROM DISK='C:\transLog03.trn' WITH RECOVERYThe first two statements work fine but the third has an error:"This backup set cannot be restored because the database has not been rolled forward far enough. You must first restore all earlier logs before restoring this log."QU#1: But I have restored all earlier logs, there is only transLog01.trn, so what gives?So after reading a bit I thought the "file=n" parameter would help me, I checked the SQL Server logs in EM and could see the entries for each transaction log backup. Each one had a "File=n" parameter, so I copied the appropriate "File=n" number into my restore script and it worked:RESTORE DATABASE myDatabase FROM DISK='C:\db-backup.bak' WITH NORECOVERYRESTORE LOG myDatabase FROM DISK='C:\transLog01.trn' WITH NORECOVERY, FILE=1RESTORE LOG myDatabase FROM DISK='C:\transLog02.trn' WITH NORECOVERY, FILE=4RESTORE LOG myDatabase FROM DISK='C:\transLog03.trn' WITH RECOVERY, FILE=4QU#2: Why aren't the "file=n" numbers sequential? I reviewed the logs on different days and there doesn't seem to be any real pattern as to what file number it will use despite the fact the transaction logs are scheduled sequentially on the hour, every hour.QU#3: The "file=n" parameter makes it work, which is good, but hypothetically if say the SQL server drive failed then I would be unable to look up which file number is for which transaction log file in the Sql Server log and then it would be an awful guessing game trying to figure it out which it would be for each file. Is there any way around this?Thanks for your help!  |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-10-24 : 18:27:38
|
| Sounds like you are using existing files for backups so that you are appending a backup set.Create your filename as<dbname>_log_yyyymmdd_hhmmss.bakand the problem should go away.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
vancouver101@hotmail.com
Starting Member
3 Posts |
Posted - 2004-10-24 : 18:51:44
|
| "oops", I forgot to mention that I am doing indeed just that, each .bak database backup file is created as a separate file with the time/date imbedded in the file.The transaction logs are overwritten daily.In the event of a crash at say 2:30pm I wanted to be able to retrieve the backup file that was done the night before and then transaction logs going up to and including the 2pm one. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-10-24 : 20:16:24
|
| If that's so then there should only be one log backup in each file so what's it trying to restore when it fails?Try a restore headeronly on the failed file and verify that there's a single backup there. Look at the backup start datetime to see that it is ok.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
vancouver101@hotmail.com
Starting Member
3 Posts |
Posted - 2004-10-26 : 17:47:31
|
| the problem is that I was using the same file name for each log.On restore I would have to specify the file number eg: "file=4" which worked, but is a pain. What I needed to do is a "with init" on the log"Backup Log mydatabase To disk='c:\logbackup.bak' with init |
|
|
|
|
|
|
|
|
Problem restoring transaction logs