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 |
|
cbrinson
Starting Member
36 Posts |
 Posted - 2002-07-20 : 18:14:05
|
| I will come right out and say that I am clueless when it comes to administering my SQL Server 2000 database. The problem I am having is that my site is going down periodically because the transaction log is growing until it fills up the hard drive and causes my COM component that does session management to fail. I had my ISP setup DB backups but they seem to be part of the problem. The C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP directory is full of these HUGE .TRN and .BAK files and further cause the hard drive to fill up. Are there any articles or books that someone can refer me to for setting up backups etc for the totally clueless. I have done the search on the site and those articles are over my head. They talk about backup and recovery strategy when I don't even know the basics of backup and recovery.I just feel like I need to learn the following and can't find any info at this basic level:- How to back up my DB and transaction log in a way that the files periodically overwrite each other rather than filling up the hard drive.- How to recover a table or records from a table if I were to accidentally execute a query to delete a whole table.I am sure there is a lot more but those are the main things. I mean, right now I am doing these backups but I have no idea how to go about recovering data. For example, how would you address the following?Say a trusted user on the site maliciously deletes various site data from various DB tables. Since my site is very dynamic, other users may perform inserts, updates and deletes from other tables before I find out that this occurred. How would I restore the data the malicious user deleted while not rolling the DB back to that initial state such that the new users changes have been affected?I really appreciate any help,Chris |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-20 : 20:01:09
|
Probably the best place to start:http://www.sqlteam.com/FilterTopics.asp?TopicID=104Details of the BACKUP and RESTORE commands are in Books Online, and it does also discuss backup strategies a little bit.quote:
How would I restore the data the malicious user deleted while not rolling the DB back to that initial state such that the new users changes have been affected?
Something like this would require a 3rd party tool like Lumigent's Log Explorer:http://www.lumigent.com/And even then, it's going to be very tricky to perform that kind of recovery. Basic security and permissions control will prevent, or at least seriously hinder, a malicious user from doing something like this. You should simply revoke all delete permissions to all users but the system admin. DELETE operations should be performed exclusively through stored procedures, where greater control can be exercised. This one thing alone will practically eliminate the chance of malicious or accidental data deletion. Relying on some kind of restoration or recovery software is not a good replacement for properly controlling permissions and data manipulation rights in the first place. |
 |
|
|
cbrinson
Starting Member
36 Posts |
Posted - 2002-07-20 : 20:53:00
|
| Here is the scenario that concerns me for deletes. I have a content management system that allows select users to add/edit/delete articles from within a web based interface. If an admin were to delete an article accidentally or purposefully it would be nice if I could restore it. It is not quite as hard to restore back to the point in time before the article was deleted though, right? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-20 : 21:53:46
|
quote:
It is not quite as hard to restore back to the point in time before the article was deleted though, right?
Yes, if you have the backup strategy set up for this contingency (Full Recovery Mode set and backups on a regular schedule), you have the ability to RESTORE to a point in time (there's a parameter in the RESTORE command precisely for this purpose) The thing is that you restore everything; therefore if you wanted to preserve new rows inserted AFTER the accidental delete happened, you'd have to use Log Explorer to do it, or perform the insert operations again after the data was restored.Don't forget that there are server roles that allow administrative functions like backups, security maintenance, etc. while also restricting access to the data itself. You might want to consider limiting the number of people who can do both, meaning the people who have full system administration permissions. I don't know if you're only using these as examples or if you really anticipate having these problems. If they are real concerns, you really should look into restricting access to the absolute minimum a person needs, and having a procedure in place for senior admins to perform more advanced functions. You might also want to consider a deferred delete, where a row is flagged as inactive but not actually deleted until periodic reviews are performed, say once a month. |
|
|
|
cbrinson
Starting Member
36 Posts |
Posted - 2002-07-20 : 22:20:09
|
| I was just using that as an example but I am leaning toward the deferred delete option.The articles I am reading about backups just seem too high level. There is a lot I just don't understand. For example, right now I have no items listed under the Management->Backup icon in Enterprise Manager. But backup files are being generated. If I look under Management->SQL Server Agent->Jobs I can see a couple jobs that seem to be doing backups. But both have failed the last time they ran. (Due to lack of hard drive space I imagine.) Is it better to just create an item in the Backup folder to do backups or use a job? I don't understand the difference.Also, I have a pile of .BAK and .TRN files. Yet, when I was messing around on my dev server just now it makes you specify a file to back up to. Why would it create multiple backup files if you have to specify a single file? I have to find some really basic book for SQL Server administration. Nothing really seems to be aimed at new administrators.ChrisEdited by - cbrinson on 07/20/2002 22:37:09 |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-07-21 : 10:17:46
|
| Not that I would normally recommend them , but in your case it would make sense, use Database Maintenance Plans. These use a simple wizard interface to allow you to set up your plans allowing you to easily control how many old backup files to keep. It will take care of deleting old backups and thus allow you to mamge your diskspace more easily. In terms of deleted articles, if this application's code is under your control and users only have access via the application then just add an extra column to indicate the state of the record i.e. deleteted. Your code then just filters on records where deleted='N' or similar. This way the user thinks they have deleted a record and to all intents and purposes they have but you can quickly and easily restore it by reseting the delted flag. Another alternative would be a delete trigger that inserts the delted row(s) into an archive table. I'd go for the flag option myself and do a periodic review either manual or a job that does a full delete of rows marked for deletion after a suitable time period has elapsed and you can be sure the user meant to delete the record.HTHJasper Smith |
|
|
|
|
|
|
|
|
Administration basics? Articles etc.?