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 |
|
dips255
Starting Member
17 Posts |
 Posted - 2014-10-12 : 13:58:04
|
| HiIs 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 backupPlease read http://msdn.microsoft.com/en-GB/library/ms191495.aspxPlus you can also have multiple copies of same backup fileHope this is what you are askingHope this helpsRegardsShankySQL Server MVPhttp://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx |
 |
|
|
dips255
Starting Member
17 Posts |
Posted - 2014-10-21 : 06:17:23
|
Hello ShankySorry about the delayBasically 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 backupPlease read http://msdn.microsoft.com/en-GB/library/ms191495.aspxPlus you can also have multiple copies of same backup fileHope this is what you are askingHope this helpsRegardsShankySQL Server MVPhttp://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
|
|
|
|
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 RTOHope this helpsRegardsShankySQL Server MVPhttp://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx |
|
|
|
|
|
|
2 Sets of Backup