I have a pilot MS SQL SERVER 2000 database that I need to backup. I don't have a tape device available and hope to be able to backup to CDs. The database is very close to full (approximately 22 GB used, 580 MB unused). Does SQL Server 2000 backup to CDs fairly easily? How does it manage splitting the data to multiple CDs (my CDs are 700MB)? I know that if I was using tapes for the backup, I could use the BLOCKSIZE command; does that command work for CDs?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
DB Backup to CDs
- Thread starter SQLBill
- Start date
Bill,
I hate to be the bearer of bad news, but NT backup devices (which SQL backup uses also, same format) cannot be assigned to writable CD media. (In spite of what the removable media MMC applet seems to indicate.) What I do is to backup to a File device and then zip that to the CD-Writeable. Haven't exceeded 1 CD's worth yet on my test bed, but media spanning in the zip program should work.
Hal
I hate to be the bearer of bad news, but NT backup devices (which SQL backup uses also, same format) cannot be assigned to writable CD media. (In spite of what the removable media MMC applet seems to indicate.) What I do is to backup to a File device and then zip that to the CD-Writeable. Haven't exceeded 1 CD's worth yet on my test bed, but media spanning in the zip program should work.
Hal
- Thread starter
- #3
Hal,
Thanks for the response. You were a big help to me. Unfortunately, I don't have any external file systems hooked up to my pilot databases (and I can't hook any up except for the possiblity of a CD writer) and I don't have room to back up to the hard drive itself since it is already full. I did finally find a solution to my problem, but not the one I really need. A co-worker is the system admin for our servers and he has a tape backup that he uses mainly for exchange. I connected my off-line, full, database to his tape drive and he copied the .mdb files over.
I couldn't do a 'real' backup from the database because he wouldn't let me for security reasons (mainly he didn't want any possiblity of his backup system and tapes being 'screwed up' and I don't blame him). So now I have the data backed up for about 3 months and then I'll lose it when his backups overwrite my data. Plus, my database still shows it has never been backed up. Unfortunately, I still don't have my own backup system - the people who purchased the system and set it up never ordered the software for the tape library.
I think my end solution will have to be 'suck it in' with the pilot databases for now. When I finally get my software for the tape library (and hopefully they finally ordered the right stuff), I'll load the data files I have on tape to my production database and then do a backup to MY tapes.
-Bill
Thanks for the response. You were a big help to me. Unfortunately, I don't have any external file systems hooked up to my pilot databases (and I can't hook any up except for the possiblity of a CD writer) and I don't have room to back up to the hard drive itself since it is already full. I did finally find a solution to my problem, but not the one I really need. A co-worker is the system admin for our servers and he has a tape backup that he uses mainly for exchange. I connected my off-line, full, database to his tape drive and he copied the .mdb files over.
I couldn't do a 'real' backup from the database because he wouldn't let me for security reasons (mainly he didn't want any possiblity of his backup system and tapes being 'screwed up' and I don't blame him). So now I have the data backed up for about 3 months and then I'll lose it when his backups overwrite my data. Plus, my database still shows it has never been backed up. Unfortunately, I still don't have my own backup system - the people who purchased the system and set it up never ordered the software for the tape library.
I think my end solution will have to be 'suck it in' with the pilot databases for now. When I finally get my software for the tape library (and hopefully they finally ordered the right stuff), I'll load the data files I have on tape to my production database and then do a backup to MY tapes.
-Bill
Bill,
Hope you really don't have to suck it up too much, (ever tried to syphon gasoline...??) but here are a couple of other ideas:
1. What about a network share? I dis-remember, but there is a way to backup (SQL backup) to a UNC, and you may have to set a trace flag to do that . . . (Sneeky M$oft, special settings to products thru "trace" flags....)
2. Another way to do it, would be to partition the database in 650MB segments or so, and set it up to burn to the CDRW for "Offline Use" which is basically read-only access to the MDF and NDF files, with the index and management data still residing on a hard drive.....
Later,
-Hal
Hope you really don't have to suck it up too much, (ever tried to syphon gasoline...??) but here are a couple of other ideas:
1. What about a network share? I dis-remember, but there is a way to backup (SQL backup) to a UNC, and you may have to set a trace flag to do that . . . (Sneeky M$oft, special settings to products thru "trace" flags....)
2. Another way to do it, would be to partition the database in 650MB segments or so, and set it up to burn to the CDRW for "Offline Use" which is basically read-only access to the MDF and NDF files, with the index and management data still residing on a hard drive.....
Later,
-Hal
- Thread starter
- #5
The main problem for me is that my data has to be kept for around 5 years (there's legal issues involved), so I can't tie up hard drives for that long. Therefore, I need to get the data off my machines and onto something that I can store for years.
Your first suggestion is what I finally did (somewhat), but again, I don't have my own tape drive right now...so I did the network share and my network admin copied the .mdf files to his tape library. He is picky and wouldn't let me do a SQL backup to his tapes because he hadn't tried it yet and didn't want to do something untried. The tapes will be overwritten in about 3 months. He doesn't have extra tapes, so I can't remove the tapes that I used.
The second suggestion is something I never thought of. I think I will test that suggestion out. If it works on my smaller pilot database, I can restore the data, that has already been copied to tape, back to the database and then copy to CDRW.
Thanks for the ongoing assistance/suggestions.
-Bill
Your first suggestion is what I finally did (somewhat), but again, I don't have my own tape drive right now...so I did the network share and my network admin copied the .mdf files to his tape library. He is picky and wouldn't let me do a SQL backup to his tapes because he hadn't tried it yet and didn't want to do something untried. The tapes will be overwritten in about 3 months. He doesn't have extra tapes, so I can't remove the tapes that I used.
The second suggestion is something I never thought of. I think I will test that suggestion out. If it works on my smaller pilot database, I can restore the data, that has already been copied to tape, back to the database and then copy to CDRW.
Thanks for the ongoing assistance/suggestions.
-Bill
- Status
Similar threads
- Locked
- Question