October 10, 2009 at 5:43 pm
Hi,
I have took a backup in the morning 9:00 AM to a perform a task and then the regular backup job at night (ran at 9:00 PM) appended the .bak files to the same .bak file taken in the morning. Now I want to restore the database back from 9:00 AM .bak file. How can I do this? how to separate 9:00 AM .bak from the appended .bak file and restore to 9:00AM??
thanks
October 11, 2009 at 4:23 am
If you have appended the backups in a single file, when you select the backup file, you will see all backups within the file.
If you only have two backups, you should see the 9am and 9pm backups listed - check the 9am backup and proceed with the restore options (overwrite, location etc etc etc).
It's not possible to split the file.
October 11, 2009 at 8:41 am
rambilla4 (10/10/2009)
Hi,I have took a backup in the morning 9:00 AM to a perform a task and then the regular backup job at night (ran at 9:00 PM) appended the .bak files to the same .bak file taken in the morning. Now I want to restore the database back from 9:00 AM .bak file. How can I do this? how to separate 9:00 AM .bak from the appended .bak file and restore to 9:00AM??
thanks
You can restore the 9 AM backup from the file.
Use With FILE=n option in Restore database(check out BOL for details)
restore database myDB from disk='c:\myBackup.bak' WITH file=1
You can see various backups within a single file by using
restore headeronly from disk='full path of backup file here'
October 14, 2009 at 4:38 am
Hi Clive,
I'm setting up backups for an SQL 2005 Database.
My backup plan so far is:
1. @ 4am - Create a a full backup to C:\backups\{dayName}\MyDB.bak
2. every 4 hours from 8am - produce a differential backup to C:\backups\{dayName}\MyDB.bak (so that it's included in the same backup file)
3. every 30 create a log file backup to: C:\backups\{dayName}\MyDB_{DDMMYY_HHMM}_Log.TRN
I have a few questions about this and just need some advice from some of the more seasoned DBA out there.
Firstly, is it a best practice to Keep Log files out of the main database backup? It's seems to be how everyone else does it as a standard practice.
Or should I just backup the log, along with the main backup file, which would make recovery much simpler from the GUI, as I could select my Full backup, the required differential and required log files all from the same backup file. Of course this could potentially leave me with a monumental single database backup file.
As far as restoring database goes, I would like to ensure that the process is fast, as ultimately the speed with which we can recover is one of the most important factors. what I want to avoid is complexity when restoring and the need to restore 1 transaction log at a time.
Which thirdly, leads me onto SQL 2000. When I had to recover database from the full backup in the SQL 2000 restore GUI, it appeared to pick up the log files that resided in the same directory. Does SQL 2005 behave in the same manner?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply