What are the challenges you face when working across database platforms? Take the survey
Options

Backup is not showing all backups in Restore wizard

malikjavedmalikjaved Posts: 7
edited September 21, 2008 12:53PM in SQL Backup Previous Versions
Hi,

I desperately need of your help for restoring the database, i cann’t Restore database by using Red-gate software, before going ahead i would like to tell you how my backup plan works.
I have setup full backup very Sunday night(deleting old file older than 8 days), Differential backup everyday night along with truncate log file(deleting old file’s older than 7 days) , and the again log file backup that run after each 30mint gap(deleting old files older then 2 days).
The problem with me is that i want to restore the database to my pervious last T-log backup, but now when i try to restore it’s just showing the only file to restore that has been run last time and more over my full backup run on 19th septemember and on 20th septemeber, but in wizard it's only showing me backup of 19th not the one of 20th,, i check the files in backup folder , the files exist over there but in restore wizard it’s not showing those file. so it's mean it's showing me only 1 full backup file option of older date and only T-log backup file that been created last time,

Please do help me considering this urgent
Malik
DBA - LDSI

Comments

  • Options
    peteypetey Posts: 2,358 New member
    Could you please try restoring your database manually using the SQL Backup extended stored procedures, from Query Analyzer or Management Studio? Here's a suggested approach:

    - if required, back up the transaction log of the database you are planning to restore
    - perform a restore using the last full backup file e.g.
    EXEC master..sqlbackup '-sql "RESTORE DATABASE AdventureWorks FROM DISK = [g:\backups\AdventureWorks_FULL.sqb] WITH NORECOVERY"'
    
    - perform a restore using the last differential backup file e.g.

    EXEC master..sqlbackup '-sql "RESTORE DATABASE AdventureWorks FROM DISK = [g:\backups\AdventureWorks_DIFF.sqb] WITH NORECOVERY"'

    - identify the transaction log backup files that were created after the last differential backup, place them in a separate folder (for convenience) and restore them:
    EXEC master..sqlbackup '-sql "RESTORE DATABASE AdventureWorks FROM DISK = [g:\backups\logs\AdventureWorks_LOG*.sqb] WITH NORECOVERY"'
    
    - confirm that you do not have any more transaction logs to restore. Bring the database online e.g.
    RESTORE DATABASE AdventureWorks WITH RECOVERY
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    I am really sorry i couldn't Get that..
    what i want to do is to resotre database from the Second last Transactional Backup..

    So you mean i have to put all Transacial and Differential and full backup files into a same folder and then manuall add each file by using this code. and then execute them all in once..

    in my case every time when transaction backup run it's create a seprate log backup file for each backup. So that's y i am confuse, how do i add each file name in the code, or i have to write same code for each log file seprate..

    Code:
    EXEC master..sqlbackup '-sql "RESTORE DATABASE AdventureWorks FROM DISK = [g:\backups\logs\AdventureWorks_LOG*.sqb] WITH NORECOVERY"'

    the Full backup was taken @ friday after noon, and then laster on remain day Transactional backup runs after every 30 mint, then in the night time a differential backup along with truncate transcation backup taken and then again upto the time of restore a log files backup taken...
    Malik
    DBA - LDSI
  • Options
    peteypetey Posts: 2,358 New member
    I am suggesting that you perform the restore manually using the SQL Backup extended stored procedures, via Query Analyzer or Management Studio.

    If you are restoring over an existing database, you may want to back up the transaction log of the existing database first.

    To perform a restore to the second last transaction log backup, you need to first perform a full database restore. From your description, it looks like you need to restore the database using the full backup taken at Friday noon. Remember to use the NORECOVERY option so that further restores are possible. Use code provided in the earlier post as a guide.

    Next, you have a choice. You could choose to :

    - restore all the transaction log backups taken since Friday's full backup
    or
    - you could restore the differential backup taken that night, followed by the transaction log backups that were taken after the differential

    To restore the transaction logs, you have 2 choices. You could restore each log individually, or you could use a search pattern to restore all the relevant logs with one command e.g.

    EXEC master..sqlbackup '-sql "RESTORE LOG AdventureWorks FROM DISK = ''g:\backups\logs\AdventureWorks_LOG*.sqb'' WITH NORECOVERY"'

    If you do use a search pattern, you would need to ensure that only the relevant logs are in the folder that match that search pattern.

    Also, I made a mistake in my earlier post. To restore the transaction logs, it should be

    RESTORE LOG ...

    instead of

    RESTORE DATABASE ...
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Thanks petey.. it's really help me alot for solving the problem as well as understanding how backup work's ...

    any way i need you brain more for 5mint's ....

    could you please tell me how can i make DISASTER RECOVERY PLAN...
    i am quite good doing with sql native backup jobs. but i am not good in red-gate..

    what i use to do in navtive sql .. full backup weekly , differential backup along with truncate transactional backup every day, and transactional backup after every 30 mint..
    but now with red-get. it's create a single for each backup.. all file's are seprate and that's make b bit confuse.
    do you have any good plan and how often old backup files i should remove....
    Malik
    DBA - LDSI
  • Options
    peteypetey Posts: 2,358 New member
    A disaster recovery plan is unique to each organization. The 2 most common driving factors of a disaster recovery plan are:

    - how much data can you afford to lose
    - how much time are you given to recover from a disaster

    How much data can you afford to lose
    You are currently backing up your transaction logs every 30 minutes. So it would appear that your management is willing to tolerate up to 30 minutes loss of work. Or perhaps you were constrained by disk space availability. Now that you are using SQL Backup, the backup files should be smaller, so perhaps you could start to back up the transaction log more frequently.

    How much time are you given to recover from a disaster
    A recovery process requires at least a full database restore followed by one or more transaciton log restores. If you have differential backups, you could use it to reduce the number of transaction logs that you need to restore. You might be performing only 1 differential backup a day because of disk space constraints. You could consider performing more frequent differential backups using SQL Backup as the files are now smaller, and if it helps to significantly reduce your recovery time.

    So basically, SQL Backup helps in addressing the 2 issues above by generating smaller backup files, and speeding up the restore process in most cases.

    However, you need to be aware that SQL Backup consumes more CPU resources. This may or may not impact your normal database operations, depending on your selected compression level, hardware resources, and various other factors. If using SQL Backup slows down your database operations noticeably, you may need to adapt your backups accordingly, such as scheduling them during non-peak hours, using less backup threads, using a 'lighter' compression setting etc.

    You are right in that SQL Backup does not support storing multiple backup sets in a single file. You will have more files to handle, but if you follow a consistent naming convention for your files, it should not be a problem. The advantage of separate files are as follows:

    - it prevents the loss of multiple backup sets due to errors in any single file. In a single file with multiple backup sets, you could potentially lose all the backups sets if the first backup set gets corrupted.

    - promotes portability. New backup sets can be easily copied to other locations, as each file is separate. If new backup sets are constantly added to the same file, the file will take consistently longer to be copied to other locations.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.