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 |
|
admin001
Posting Yak Master
166 Posts |
 Posted - 2003-05-29 : 04:51:22
|
| Hi ,One of our clients has accidently deleted a file whose record was existing in of the databases . We have a database backup job daily where it dumps the DB on local drive . Say for e.g that we need the database for 15 th May . Is there any way to retrieve this old DB and restore it in another Database ? I was going through the restore options , where it gives to restore File or filegroups from backup sets . In the backup sets , it displays the set for 15 th May . How do i just restore that backup set for 15 th May from the current DB bak file . The filegroup is the mdf data file . Is there any way to do this or else we have to restore the DB from a tape backup of 15 th May which is NAS . Need some help on this matter . Thanks . |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-29 : 12:47:09
|
| It just depends on how you do your backups. We always create a new file or initialize the current file (overwrite it) for each and every full backup. If you do it this way, then you are going to have to restore the file that was created for May 15th's backup. This file should be on disk or on tape. If it is on disk, then you simply perform a regular RESTORE command. If it is on tape, then you will need to restore it to disk first then perform the normal RESTORE command. If you don't initialize each time (this is a problem by the way if you don't), then you will need to specify a file number in the RESTORE command. I can help you with that, but first we need to figure out how the backups are done.TaraEdited by - tduggan on 05/29/2003 12:55:50 |
 |
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2003-05-30 : 03:36:06
|
| Hi Tara ,Thanks for your reply . In fact we backup the files through straight backup job which is scheduled to run daily . The bak file is full backup of the database with append to media option set . The options set in the job are :BACKUP DATABASE [Summary] TO DISK = N'E:\MSSQL\BACKUP\Summary_bk' WITH INIT , NOUNLOAD , NAME = N'Summary_DB backup', SKIP , STATS = 10, NOFORMAT . I restored the latest bak file , say yesterday's , but it does not show any record for 15 th May in the table . A mentioned by you , how do we specify the file number for 15 th May in the restore command. Can we also specify the date in the Restore command ? Thanks once again. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2003-05-30 : 10:41:06
|
| Since you are using WITH INIT option, your prior day's backup gets overwritten every time the job runs. In other words, your "Summary_bk" file always contains the latest backup only. But it sounds like you are backing that file to tape on a daily basis. So like Tara said: restore the "Summary_bk" backup device file from tape, and then restore your database from it. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-30 : 12:44:05
|
| I really hope that you backup the backup files to tape. If not, then you can ever only restore yesterday's backup. May 15th is not contained in your backup set. It was overwritten (completely overwritten, nothing from May 15th was saved) by May 16th, which was overwritten by May 17th, etc... So you must restore this file from tape. Then you can perform the restore of the database. BTW, just because Enterprise Manager shows you what is available to restore, that doesn't mean that they are really available for restore. What you are seeing in Enterprise Manager is just the backup history.Tara |
|
|
|
admin001
Posting Yak Master
166 Posts |
Posted - 2003-05-31 : 00:01:57
|
| Thanks for all your replies . I will have the record restored from a backup set from tape and then load it in the database . Hope that goes well without much problems !!Thanks once again. |
|
|
|
|
|
|
|
|
Recovering Backup set .