| Author |
Topic |
|
sgadmin
Starting Member
8 Posts |
 Posted - 2007-07-09 : 12:39:01
|
| I'm fairly new to SQL administration and I inherited an existing sql 2k instance and it's got some issues. All of the trx log backups are going to one backup set and the backup set is being saved to the same location as the *.ldf files themselves...I know...very bad. Is there a way to automatically copy (maybe through xcopy?) the backup set off to another network location? Thanks so much! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-09 : 13:09:41
|
| Why don't you just change the path and also add WITH INIT to your scheduled jobs?Yes you can use xcopy to copy files, but you really should just fix the problem.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-09 : 13:16:15
|
I agree: Fix the problem. Otherwise your XCOPY BandAid will run the same time as SQL tries to open the file for backup and the backup will then fail, or the copy, either way you may come to try to recover and discover that the file is toast  Kristen |
|
|
|
sgadmin
Starting Member
8 Posts |
Posted - 2007-07-09 : 13:56:29
|
| Thanks for the information. I was a bit concerned about changing the location of the backups after reading the post from 2/14/2006 "Transaction Log backup to network drive". That's why I thought the copy would be a better choice. What's the preferred method? Thanks again!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-09 : 13:59:49
|
| The recommended procedure for backups is to backup the database to local drives, then either copy those files to tape or copy them to the network resource.I have no idea what post you are referring to, so if you want us to look at it, provide a link to it.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sgadmin
Starting Member
8 Posts |
Posted - 2007-07-09 : 14:20:23
|
| Sorry! I thought I included the link:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61721&SearchTerms=network,backup,2000The db and trx logs are backing up to a local resource and I want to copy the backups (both db and trx logs) to a network resource automatically; which is where my initial question came from. My issue is that the existing jobs are going to backup sets.Can I just create a job for the daily db backup; with an xcopy as the final step and another job for the trx log backups (not putting them in a set) and xcopy as the final step? Or is xcopy just not recommended? In that case, what's the best approach to copy those off automatically? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-09 : 15:15:49
|
| You can modify backup device to point to network share, ensure sql agent service account has permission to access it. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-10 : 03:37:54
|
| My preference is to back up locally, and then Copy to Share. That way if the COMMs is down, or fails, or whatever, then at least I have the local backup.Kristen |
|
|
|
sgadmin
Starting Member
8 Posts |
Posted - 2007-07-10 : 09:26:22
|
| Got it. Thanks everyone for the input! My biggest challenge here is convincing the original "owner" (developer; not administrator) of this db that we need to use a backup scheme that's more flexible than one, large backup set. Thanks again! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-10 : 09:33:20
|
| "more flexible than one, large backup set"Do you mean that multiple backups are appended to a single file?Here's some questions to ask yourself (or your colleague!!):What happens where there is a corruption?Can you restore to 10:21 this morning, just before some accident at 10:22?Can you restore to 15th of last month to see if there was data there then, which is missing now, and try to track down a fraud that was committed (or a program bug ...)Are we sure that your Backup files are ABLE to be restored? You need to make frequent trial restores to a separate SQL Server [and use DBCC CHECKDB to prove that the database is not damaged]Kristen |
|
|
|
sgadmin
Starting Member
8 Posts |
Posted - 2007-07-10 : 09:55:34
|
| I inherited this mess on Friday and have never used backup sets which is why I asked for help from the forum (which has been great). From an admin perspective; I generally run full and log backups to their own locations and then copy them off to a secondary location...much like the suggestions in the thread.This little beauty of a db has all trx log backups PLUS the daily full bkup being saved to one single 2 gb file. It's now my responsibility to just "copy off the file to another location". It was implemented this way for ease of resortation. The whole backup scheme is just risky!I'm in the process of documenting a better backup methodology to deliver to development and my boss. I appreciate all of the help and suggestions!! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-10 : 10:46:53
|
"It was implemented this way for ease of resortation"I don't see how it helps, there is still just as much work to identify the individual backups that have to be restored, in fact there is arguably "more" because using unique filenames, with a single backup in each, means you just have to do a Directory List of the files and you have a complete list of all files to be restored, in chronological order!Having said that you DO have to recover ALL the relevant files from Backup Media before you can make the Restore, and that might be across multiple tapes. My answer to that is to keep a complete set (i.e. all DIFF/Tlog backups back to, and including, the most recent FULL backup) online. Therefore to restore anything between last Full and latest Tlog is already online. In extremis I can go to Tape (which always takes longer than restoring from files directly available on a hard disk somewhere on the LAN, of course).The downside of the approach they are using is that ANY single corruption renders the rest of the backup unusable whereas, assuming some Periodic DIFFs - and even the route of restoring to an older FULL and then ALL Tlog backups thereafter - provides some get-arounds to a single point of failure.Of course, for mission critical ALL backups should be test-restored to an alternative server, and if its THAT mission critical then a hot or warm standby will be restoring each backup as it is made (e.g. log shipping) anyway.But their backup strategy suggests they are happy to reconstitute their data from scratch without any bother  , in which case I agree that a relatively casual backup strategy is sufficientKristen |
|
|
|
|
Copying backup sets