Tranactional Backups and Database restore

Question

Tranactional Backups and Database restore

AVB

SSChampion

Points: 11418
More actions
May 28, 2004 at 12:30 pm

#162594

Hey all, I am pretty new to sql, never even knew what it looked like till about 6 monthes ago..... I currently have my main DB do a Full back up to 2 remote servers and itself. It only take 27 secs to back it up each job. I have contemplated the idea of either doing a Full Backup every hour or Transactional Log backups every hour. If I decide to go with Log backups will I need a new file for each Log back up specfic to that time (i.e. 1:00 log1.bak, 2:00 log2.bak...) so that I may restore to that specfic time or is this accomplished by appending the backup job to the same file Also I haven't found out how to restore a deleted database with these backups, is this possible? TIA for any help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lawkaanat Grasshopper Points: 11 More actions · Answer 1

First set your application database to "FULL". Then from enterprise manager go to ->Management ->Database Maintenance Plan ....right click -> New Maintenence Plan.

It is EZ 2 follow instruction. And you will find a schedule tab for DataBase which you might want FULL BACK UP once a day. And Transaction Log Back UP every hour. Don't worry for log file name , bcoz SQL server is smart enough to name them chronologically. Good Luck !!

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Check the Books OnLine for BACKUP DATABASE and BACKUP LOG.

I suggest doing a FULL backup once a day and several TRANSACTION LOG backups during the day.

To recover the database you will need to RESTORE the latest FULL backup using the WITH NORECOVERY option. Then RESTORE all of the TRANSACTION LOG backups made after that FULL backup, again using WITH NORECOVERY until the very last RESTORE which will have WITH RECOVERY.

Transaction Log backups allow you to restore to a 'point-in-time'. Let's say someone deletes a table at 2:30. You immediately do a transaction log backup. Then you restore the FULL backup from before 2:30, and all the transaction logs. On the last transaction log you use WITH STOPAT and the date and time before the delete happened.

Again, read up on it in the BOL.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's Help. Installed as part of the Client Tools and found at Start>Programs>Microsoft SQL Server>Books OnLine

AVB SSChampion Points: 11418 More actions · Answer 3

Thanks I appreciate it. I have already created the Full backups for my Master,MSDB, and the companies DB but that was not through the Maintaince Plan. I'll give that a whorl too.

AVB SSChampion Points: 11418 More actions · Answer 4

I have accomplished Backups and Restores to Db's thru Em but I have not found anything in the updated BOL's on Restoring a deleted DB (Probable overlooked something I'll look again). Thanks for the advice.