Just how can I backup an SQL server (2000 and also 2005) data source?
Presently we backup our MSSQL 2000 and also 2005 data sources making use of software program to replicate documents to tape every night. Data source dimensions are 14-16Gb and also 500Mb.
As SQL server can backup a data source to a documents would certainly we be far better organizing SQL server to create a backup documents and afterwards supporting these documents to tape? Additionally if we utilize this method is it feasible to in some way create a log of purchases that have been finished given that the last backup so we can recreate the data source and also use the added purchases?
Scripted backup to disk is very easy and also runs really promptly:
osql - S [ip_of_server ] - Q" BACKUP DATABASE [database_name ] TO DISK = 'C: \ Backups \ Backup.bak'"
Doesn't function to support to various area over the network (not that this is an excellent suggestion anyhow) with a UNC course. It is less complicated anyhow to make back-ups in your area and also store them encrypted.
Hard drive is economical, and also significantly trusted. We removed our tapes a while back and also have never ever recalled.
As you've seen by the solutions, the majority of individuals do backup to disk first, after that to tape. What I've usually seen (and also advised) is to backup quickly to disk that gets on the web server itself (this can be SAN affixed storage space, the key is the backup isn't created throughout the network). As soon as this prompt backup is done, it is after that replicated to a main backup web server. You maintain a prompt duplicate of what you require to recover in your area. On that particular backup web server you maintain numerous days well worth of back-ups. In this way if you do need to rollback to a previous day or 2, you're not asking for a tape. And also certainly, you support that main backup web server to tape. To make sure that covers your capacity to recoup.
Relative to the sorts of backup you need to be doing, if you intend to recoup all purchases and also have the ability to do what we call point recuperation, you require to see to it the data sources have their recuperation version readied to complete recuperation and also you're mosting likely to intend to do Full back-ups in addition to purchase log back-ups. You might intend to intermix differential back-ups to lower the quantity of documents you are recovering. Every one of this is covered in publications on - line and also it'll offer a far better summary than any one of us can in a discussion forum blog post.
Regarding 3rd event items like LiteSpeed and also Red Gate's SQL Backup, they made use of to be faster than indigenous backup. That's due to the fact that they make use of an API that SQL Server really did not. This held true in SQL Server 2000, yet I'm not exactly sure if it is still the instance. Nonetheless, they do security and also compression therefore might be well worth factor to consider offered the dimension of your DBs.
Do you suggest you presently support the database MDF and also LDF documents to tape? I assume this can be ALRIGHT, as long as the database is offline while you do it (i.e. database separated or SQL Service shut down), yet if you attempt and also do that while the database remains in usage you will possibly wind up with back-ups that do not function due to the fact that the documents will certainly have transformed while being replicated.
Developing a complete SQL Backup and also placing that onto tape is far more usual in my experience.
Additionally, to address your 2nd inquiry, SQL Server does sustain Transaction Log back-ups. The MSDN documents on the Full Recovery Model would certainly be an excellent area to start studying.
There's actually no factor NOT to make use of SQL Server's indigenous backup capability. It's wonderful, recognizes purchase logs, and also offers you a lot of the capability you require. (Third event SQL Server Backup remedies are wonderful due to the fact that they collaborate with the APIs that Microsoft has actually revealed, and also generally give security and also compression assistance - and also compression not just conserves disk room, yet commonly lowers backup and also recover times.)
So yeah ... I would certainly advise doing back-ups WITH SQL Server.
And also see to it to do REGULAR purchase log back-ups. (I would certainly advise doing them every 15 mins or much less - normal log documents back-ups aid maintain your log documents lean/mean 1 and also for one reason or another end - customers are constantly irritated when the web server collisions and also they need to remodel every one of their job over the last x hrs given that the last logfile or full/differential backup.) After that, for redundancy objectives, make use of robocopy, syncback, documents system duplication or something to relocate duplicates of those back-ups to an additional area to shield versus hardware/disk collisions or fires in the information facility and more.
Do not hesitate to look into these 2 free video clips for even more details and also suggestions :
Note : My largest bug bear with SQL Server (all versions) is purchase log back-ups.
We run a complete backup our SQL db's every night and also purchase logs every 30 mins, to disk (on an additional server, various SAN) to day stamped documents (rather straightforward SQL manuscript and also SSIS).
Our manuscript additionally develops the "restore.sql" manuscript to recover the last complete backup and also all the purchase logs approximately that day.
We after that whiz up documents over 2 days old, delete any kind of over 30 days old (yet maintain month end back-ups in an archive). We reproduce this repel - website (we're good luck we have 200 miles in between huge websites and also huge WAN link in between them.)
We after that backup this to tape. (Belt, supports and also extra supports! Primarily for political factors)
We run a great deal of SQL Server data sources and also business devices are either also pricey or simply do not offer us the bang for the dollar. Yet I would certainly advise RedGate SQLBackup for smaller sized installments