| Author |
Topic |
|
ITTrucker
Yak Posting Veteran
64 Posts |
 Posted - 2012-01-26 : 09:50:15
|
| How do I create a full backup that is independent of the normal backup chain?We have our production and a test database for some new software we're installing. The people setting it up are making changes in the live database and then they want me to copy the live database to the test one so they can create POs/Invoices with the new settings in the test environment and don't clutter the live database.We are doing full backups each morning and Tlog backups every 30 min, but sometimes they will make a bunch of changes at 9:45 and want to work right away in the test database to check them. Right now I'm waiting until 10:00 when the tlog backs up and restoring the live database to the test. So I want to be able to create a full backup of the live database at any point, but I don't want to break the backups that we're currently keeping. I checked the forums and most people don't seem to like the copy database, but I want to create a backup right now, not wait until the tlogs back up and restore from there. |
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2012-01-26 : 11:33:48
|
| Use BACKUP DATABASE <DBNAME> TO DISK = '<FILEPATH>' WITH COPY_ONLY----------------------------Junior DBA learning the ropes |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-26 : 12:30:29
|
| Full backups never interfere with the log backup chain, so you're safe to take a full backup any way you like.--Gail ShawSQL Server MVP |
|
|
|
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2012-01-26 : 12:42:28
|
| Thanks, I thought that it might be something short like that. I guess I got hung up on the COPY_ONLY description where it says that it cannot serve as a differential base. I was worried that when I restored it, the subsequent transactional backups on the restored test database would fail. Wasn't really thinking clearly about it I guess. Thanks again. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-26 : 13:23:02
|
| Just to reiterate, copy_only is NOT required in this case. Full backups do not ever break or interfere with the log chain, so if all you are taking is full and log backups, you can take an ad-hoc backup at any point and it will have no effect whatsoever on the backups or restore path of the production database.Copy_Only is solely needed when you are taking differential backups, it is a full backup that does not reset the differential base.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-26 : 13:40:12
|
quote:
Originally posted by GilaMonster
Copy_Only is solely needed when you are taking differential backups, it is a full backup that does not reset the differential base.
That point may be very import though Gail.If someone is relying on infrequent FULL backups (say weekly) and DIFF backups (say daily) then taking a FULL backup will restart the DIFF "chain". That's fine if the adhoc FULL backup file has not been deleted, and the person trying to make the Restore knows where to look ... but otherwise might be an annoyance if disaster strikes causing possibly masses of TRANS files to have to be restored ...If making an adhoc full backup I think best practice would be to use WITH COPY_ONLY so as not to interfere with any Full/Diff backup strategies, that way it is not important for the person to know whether it is actually important to the backup strategy, or not, its either benign or crucial!FWIW here we backup to disk, and we have a specific SProc that makes backups, and if anyone wants a "safety backup" for whatever reason they run that Sproc. That guarantees that the backup file is put into the "normal" folder in case it may be needed during a disaster recovery restore. |
|
|
|
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2012-01-26 : 14:12:47
|
| So I ran:BACKUP DATABASE [dbname] TO DISK = '[drive\folder]' WITH NAME = '[filename.bak]'and got:Msg 3201, Level 16, State 1, Line 1Cannot open backup device '[drive\folder]. Operating system error 5(Access is denied.).Msg 3013, Level 16, State 1, Line 1BACKUP DATABASE is terminating abnormally.Did a quick check online and "looks" like it is a permissions issue. I'm running Managment Studio on my local machine connecting to our server. I'm a domain admin so I set the permissions on the server folder to allow the domain admins full access to it and got the error again. Then I shared the folder and got the error. I tried the drive:\folder, I tried the share's UNC path \\server\folder. I tried adding NETWORK SERVICE and then finally added EVERYONE to the folder permissions and still not working.I checked the service on the server and the Log on account is Network Service. Tried logging on as SA on local machine and running, same error. Tried logging onto the server and running SSMS on the server as SA and tried with my admin credentials, still errors out.Any thoughts?(And should I leave this post here, or start a new topic?) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-26 : 14:29:39
|
quote:
Originally posted by Kristen
quote:
Originally posted by GilaMonster
Copy_Only is solely needed when you are taking differential backups, it is a full backup that does not reset the differential base.
That point may be very import though Gail.
What I am trying to get across is the fact that full backups do not, will not, can not affect the log backup chain. It's a pervasive myth that I just keep finding.If people know what copy_only really does and they know how backups affect each other, then they can make an educated decision on what options are necessary and when.--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-26 : 14:31:37
|
| Operating system error 5 is indeed a permissions problem. Maybe take the backup to a local device (or the normal backup location) and then copy it where necessary?--Gail ShawSQL Server MVP |
|
|
|
steve_r18
Yak Posting Veteran
59 Posts |
Posted - 2012-01-27 : 15:18:43
|
Confirm you have access through standard UNC pathing in explorer.This will help narrow down whether it's windows permissions or something else. There are also 2 sets of permissions...not sure if you confirmed access to 1 single account with both sets. NTFS and Sharing.quote:
Originally posted by ITTrucker
So I ran:BACKUP DATABASE [dbname] TO DISK = '[drive\folder]' WITH NAME = '[filename.bak]'and got:Msg 3201, Level 16, State 1, Line 1Cannot open backup device '[drive\folder]. Operating system error 5(Access is denied.).Msg 3013, Level 16, State 1, Line 1BACKUP DATABASE is terminating abnormally.Did a quick check online and "looks" like it is a permissions issue. I'm running Managment Studio on my local machine connecting to our server. I'm a domain admin so I set the permissions on the server folder to allow the domain admins full access to it and got the error again. Then I shared the folder and got the error. I tried the drive:\folder, I tried the share's UNC path \\server\folder. I tried adding NETWORK SERVICE and then finally added EVERYONE to the folder permissions and still not working.I checked the service on the server and the Log on account is Network Service. Tried logging on as SA on local machine and running, same error. Tried logging onto the server and running SSMS on the server as SA and tried with my admin credentials, still errors out.Any thoughts?(And should I leave this post here, or start a new topic?)
Steve |
|
|
|
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2012-01-30 : 12:05:26
|
| I connected to the server desktop as an admin and I am running SSMS on the server itself. I run:BACKUP DATABASE TL1 TO DISK = 'F:\OneTimeBackup' WITH NAME = 'TL1Backup.bak'and it errors out. I tried BACKUP DATABASE TL1 TO DISK = \\servername\OneTimeBackup' WITH NAME = 'TL1Backup.bak'and that errors out. Then I ran:BACKUP DATABASE TL1 TO DISK = '\OneTimeBackup' WITH NAME = 'TL1Backup.bak'And I got a successful backup message sort of:Processed 28112 pages for database 'TL1', file 'L1Dat.mdf' on file 1.Processed 1 pages for database 'TL1', file 'L1Log.ldf' on file 1.BACKUP DATABASE successfully processed 28113 pages in 0.982 seconds (223.658 MB/sec).But I can't find the .bak file anywhere on the server. It looks like it created a backup somewhere, but I can't find it. :) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-30 : 12:33:22
|
| DISK = '\OneTimeBackup'That'll be in the default backup location. Assuming that hasn't been changed, it'll be somewhere under C:\Program Files\Microsoft SQL Server\.... Just do an explorer search for *.bak under that folder, you should find it.--Gail ShawSQL Server MVP |
|
|
|
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2012-01-30 : 12:50:51
|
| I did find it under:C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backupbut the file name was OneTimeBackup with no extension (which is why I couldn't find it with a *.bak search). If I go to restore the file via the Restore Wizard, the device is C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\OneTimeBackup and the Name of the backup set is the TL1Backup.bak.The restore seemed to work fine, and I can run the command from my local machine and it puts the file on the server so I can use it. Just odd that I can't get it to work with a specified path. |
|
|
|
|
Independent Backup