Restore log backup without database backup file
-
Hello,
can anyone tell me if it is possible, and how, to restore a database only with a copy of datafiles and transactional logs backups?
What I want to do is a "cold backup" by making a copy of datafiles (.mdf, .ldf e.log) at night, and during the day transactinal logs backups.
If during the day I have a crash on my database, how can I apply my transactional log backups over the datafiles recovered?
Thanks in advance.
Best regards,
Anabela
-
I do not think it's possible to restore log files to a database you restore from a cold backup.
Firstly, the database needs to be in standby/restoring mode if you want to apply trx logs. I don't see how you can get the database to be in those states from a cold backup. Who knows, you might be able to, using a couple of undocumented functions.
I would suggest using the regular on-line backups. Is there a reason why you are considering using only cold backups?
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. -
your best bet may be to try attaching the files using sp_attach_db
good luck!
-
Why do you want to do it this way? It seems contrary to best practices. Maybe you are trying to accomplish something that can be done in another way.
Bob
SuccessWare Software -
Hi guys,
thank you for your ideas.
The reason I have to want a solution like this is that I don't have disk space to keep, temporarily, two backups files of my database on disk before it goes to tape, and I don't want to do backups directly to tape, so I thought in doing cold backups that rewrites datafiles backup each day.
Anyway, what I needed to have sure is that I can restore a database in this situation, and like Peter suggested, it can be done with some undocumented functions.
One more time, thanks.
Anabela
-
Did you actually manage to restore the cold backups, and apply trx logs to the database after that? As regards the space constraints, have a look at our product, MiniSQLBackup. You ought to be able to get an average of 70% to 80% space savings for your backups.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. -
It seems to me that if your problem is disk space that possibly your real issue is capacity planning. If lack of disk space prevents you from doing regular backups now, then you're likely to run into other capacity issues in the not to distant future. While you are solving this immediate space problem, I would also suggest talking to whoever controls the budget for your systems and tell them you are in need of some upgrades. Then if a problem occurs in the future, you can at least say "I told you so"
Bob
SuccessWare Software -
A transaction log backup contains the transactions that have occured since the last backup was taken (full, diff, or tran). It keeps track of this using an LSN (log sequence number). If you "restore" a "cold" backup, then somehow manage to apply a transaction log backup to that, the transactions you apply are since the last SQL backup, not the "cold" backup. So, IF you somehow manage to do this, you will either be missing transactions, or duplicating them.
If you are short on disk space, I would strongly suggest adding disk space, talking to Peter about Mini SQL Backup, or IMCEDA about SQL LiteSpeed, which will reduce the size of your backups.
In short, if you continue to use "cold" backups, you should probably change your recovery model to simple, or backup your logs with truncate_only because they are really useless without a full SQL backup to associate them with.
Steve
-
Thanks for the explanation on this. I thought something strange along these lines could happen but I really wasn't sure what was going on. It makes perfect now sense since the "cold" backup is just a copy of the data file and not a SQL Server generated backup file.
Bob
SuccessWare Software -
I don't know where my head was, but it certainly wasn't where it should have been. That first sentence is WRONG! A transaction log backup contains the transactions that have occured since the last time the transaction log was truncated, not since the last backup! A transaction log backup truncates the log, full and differential backups do NOT. The starting LSN of a transaction log backup is checked against the last restore (the current state of the database), to decide whether the backup can be used next in the sequence. The rest of my post should be correct.
To go a little further, if I took a full backup Sunday of last week, and transaction logs every night, then another full backup Sunday of this week (2 nights ago), and transaction logs every night, and I need to restore the database today, I have a couple of choices:
1. Restore the full backup from 2 nights ago, and all the transaction log backups since then.
2. Restore the full backup from Sunday of last week, and all the transaction log backup since then (NOT the full from two nights ago).
Either of those scenarios would provide the same result.
It still holds that a "cold backup" would not fit in with the transaction log backups at all.
Sorry for any confusion I have caused.
Steve
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply