Backup is not showing all backups in Restore wizard

Comments

• petey Redgate › Posts: 2,358 New member

  September 21, 2008 4:06AM

  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

  0 · Share on Twitter
• malikjaved Posts: 7

  September 21, 2008 6:25AM

  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

  0 · Share on Twitter
• petey Redgate › Posts: 2,358 New member

  September 21, 2008 11:10AM

  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

  0 · Share on Twitter
• malikjaved Posts: 7

  September 21, 2008 11:53AM

  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

  0 · Share on Twitter
• petey Redgate › Posts: 2,358 New member

  September 21, 2008 12:53PM

  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

  0 · Share on Twitter