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 |
|
jcbennet
Starting Member
1 Post |
 Posted - 2011-01-04 : 17:24:04
|
| I would like to set up a maintenance plan that will allow me to restore a DB to the most recent trans log backup. If i run a transaction log backup every 30 min and place that file in a folder. Im not sure how to use that file to restore the database to a point in time so i think i need to place that file into my full .bak file.I do a full backup once a day. This .bak file is placed in a folder with the last 5 days .bak files. I would like to configure a transaction log backup that runs every 30 min and adds that log file to the full .bak file but Im not sure how to automate that so it places the log backups to the latest .baki think im making this more difficult than it is.Can someone please point me in the right direction? |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-04 : 17:44:55
|
| "i think im making this more difficult than it is."10 points for realising that!!!Why do you want to do this restore?And how often do you want to do it? "restore a DB to the most recent trans log backup"suggests you maybe want to do it whenever a new log backup is made?We use what is termed "log shipped" to copy our Log Backups to another server, restore them, and then that server is available as a "warm standby" in case our main server breaks.You can do that by just copying the log files across to the second server, and restoring then, but NOT setting the database to "ready" - so it is still in the correct state to restore yet-another-log-backup 30 minutes later.(If you have a disaster and you need to use the Second Server you just set it to "ready" and then you can access it.)If you need to connect to the second server's database (AND carry on restoring Log Backups every 30 minutes), then that is slightly more complicated.If you can provide a bit more info about WHY you want to do this I am sure folk here can help. |
 |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-01-05 : 01:19:38
|
| hi jcbennet, perhaps you are looking for a concept as how to use prepare your self for point in time recovery when a database server crashes or what so ever...check this out http://msdn.microsoft.com/en-us/library/ms190244.aspxCheers!MIK |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-05 : 01:28:32
|
| full and log backups are independent.You can restore a full backup followed by the sequence of log backups. It's usually the latest full but can be any as long as you have the sequence of logs.Hence which logs to apply is part of the restore not the backup and you wouldn't want to try to append the log backup files to the full backup file.To restore to the latest log backup you just restore all of the logs that are available following the full.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-01-05 : 08:39:58
|
| http://thefirstsql.com/2010/08/06/create-a-full-point-in-time-restore-script-automatically/- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
|
|
|
Restore DB to point in time