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.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 2 Sets of Backup

Author  Topic 

dips255
Starting Member

17 Posts

Posted - 2014-10-12 : 13:58:04
Hi

Is it possible to execute 2 separate jobs of full as well as differential backups of a database? Basically our service provider manages backup of sql database with their agent but I want to have my individual backup to run separately. Is this possible?

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-10-13 : 06:17:00
Yes it is possible(If I read your question correctly) you can take backup using copy_only option. When you take backup using copy_only option backup is exactly same as full backup only difference is it would not affect the backup chain in anyway. So you can take full and transaction log backup (with copy_only) option anytime without disturbing chain.
Remember there is no copy only backup option for differential backup its only for full and log backup
Please read http://msdn.microsoft.com/en-GB/library/ms191495.aspx

Plus you can also have multiple copies of same backup file
Hope this is what you are asking

Hope this helps

Regards
Shanky
SQL Server MVP
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page

dips255
Starting Member

17 Posts

Posted - 2014-10-21 : 06:17:23

Hello Shanky

Sorry about the delay

Basically I'm looking for a recovery solution with least amount of data loss.

I will explain what exactly I'm looking for.
Our service provider has installed an agent which manages backup of sql db. The agent takes 1 full backup and 1 differential backup. The backup files are stored at another location on the datacenter. My purpose is to have same set of backup files in my custody. I added sql job to create backups but it only gives me differential backup after their agent takes full backup.

I've used full and differential backups till now. Never tried transaction log backup. Can you advise which is the best suitable for this purpose.

quote:
Originally posted by Shanky

Yes it is possible(If I read your question correctly) you can take backup using copy_only option. When you take backup using copy_only option backup is exactly same as full backup only difference is it would not affect the backup chain in anyway. So you can take full and transaction log backup (with copy_only) option anytime without disturbing chain.
Remember there is no copy only backup option for differential backup its only for full and log backup
Please read http://msdn.microsoft.com/en-GB/library/ms191495.aspx

Plus you can also have multiple copies of same backup file
Hope this is what you are asking

Hope this helps

Regards
Shanky
SQL Server MVP
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx

Go to Top of Page

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-10-21 : 07:34:10
I am sure backup is happening in your environment using third party(TP) tool and this might be reason you dont have transaction log backup in place. With no transaction log backup in place you would never achieve point in time(PIT) recovery. If your database is in simple recovery transaction log backup is not possible.
If you want to have same set of backup ( assuming backup is not taken on tape using TP tool) you can copy the backup file and store it to location where you like of course you would have to procure disk for the storage if backup sets are large. That is simplest thing you can do. I am not sure whether tool you are using to backup has ability to write backups files at multiple locations
Do you ever tested your backups by restoring it this will help you to achieve your RPO and RTO

Hope this helps

Regards
Shanky
SQL Server MVP
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page
   

- Advertisement -