| Author |
Topic |
|
ravl13
Starting Member
38 Posts |
 Posted - 2011-10-04 : 13:41:38
|
| Greetings,I'm trying to create documentation for a small non-profit on what to do if their server machine's harddrive fails. I have been trying to figure out the simplest way for them to make backups and do a database restore, since they currently don't have the money for an IT guy. There is only one database on the server.What I would like to do is:1. Be able to create a .bak file on a separate disk, and also to an external hard drive. A new back-up would manually be made once a week, and when the drives start getting full, they can delete the older .baks2. In the event of computer failure, or they accidentally do something stupid like delete a table for instance, they would then reinstall SQL server (only if the hard disk failed), right-click on the database folder of their instance of SQL Server in management studio, select database restore... wizard, browse for the .bak on another drive/external drive and pick a restore point, and click ok to do the restore.I figured that the "full" backup type would work fine with the above desired steps, but there is a problem when I attempt to restore with the database restore wizard. When I click "OK" and attempt to do the restore, I get the following message:TITLE: Microsoft SQL Server Management Studio------------------------------Restore failed for Server 'CHEMALY2\DURHAMTEST'. (Microsoft.SqlServer.SmoExtended)------------------------------ADDITIONAL INFORMATION:System.Data.SqlClient.SqlError: The tail of the log for the database "Paradigm" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log. (Microsoft.SqlServer.Smo)------------------------------BUTTONS:OK------------------------------Why am I getting this message? I just want to use the wizard to restore the database to the state it was when I made the backup. I would've thought that a "full" backup would have everything I need to restore a database. Why is it whining about a logfile? I don't care about recovering changes/new records that were added after the backup was made. This isn't a super-critical business database, this database just holds a single (large) table of contact information of members and their donations and such, and only a few records are added per week, so re-adding records isn't a problem after restoring to last week's database snapshot, for instance.Simplicity is key here: I'd like the employees of this organization to use the Database restore wizard UI if necessary, and not have to worry about writing backup/restore queries. Is there an option that I can set somewhere that would resolve the error message that I get when using the database restore wizard?NOTE: I am attempting to restore the database to a prior state while the database is currently running, and am using SQL Server Express 2008 R2. This is to emulate if someone deletes an entire column in a table for example, or updates many values incorrectly, and they want to revert to a backup they made just before attempting the changes. Thanks in advance,-Robert V. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-04 : 14:08:23
|
quote:
Originally posted by ravl13
Why am I getting this message? I just want to use the wizard to restore the database to the state it was when I made the backup. I would've thought that a "full" backup would have everything I need to restore a database. Why is it whining about a logfile?
Because you're in full recovery model and, in full recovery model SQL will protect you against losing data by overwriting the database without backing the log file up. That's done, because the point of full recovery is to be able to restore the database to any point in time without any data loss.It also means you nee regular log backups.Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
 |
|
|
ravl13
Starting Member
38 Posts |
Posted - 2011-10-04 : 14:15:11
|
| Alrighty, that works fine. Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ravl13
Starting Member
38 Posts |
Posted - 2011-10-04 : 14:22:20
|
| Thanks for the link Gail for the explanation. Since Tara agrees as well, I would like to change the backup type to simple, but when I use the backup database wizard, it only shows "Full", "Differential", and "Transaction Log" for the Backup Types field. The Recovery Model field says "FULL", but it is greyed out and I can't change it to "Simple", which is where I assume the change needs to be made. How can I change the recovery model to simple? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ravl13
Starting Member
38 Posts |
Posted - 2011-10-04 : 14:30:44
|
| ok |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-04 : 14:33:18
|
| "I don't care about recovering changes/new records that were added after the backup was made"Why? "A new back-up would manually be made once a week" are the operators going to be able to remember every change they made in a week? If that's the level of sophistication you need then why not just use, say, an Excel spreadsheet? If they are a small organisation how easily will they "catch up" if they lose a week's data? IMHO all the more reson to provide then with a recent backup so they lose as little data as possible.If they are going to use SQL Server they might as well have some benefit from its high-availability. Its relatively trivial to get SQL Server to make a backup of the transaction log every, say, 15 minutes. Their worst case data loss is then 15 minutes. yes, this makes a lot of backups, but apart from some overhead the collective size of the backups is not more than doing them "once in a while"."I'd like the employees of this organization to use the Database restore wizard UI if necessary"The standard GUI restore knows about the backups that were made, so should be possible to restore without too much hand-holding.But whilst its a nice thought, I'm sceptical about the scenarios where they would be a safe pair of hands to make a restore ("little knowledge is a dangerous thing" and all that).1) Some data was accidentally deleted. OK, in this situation they are probably safe to do the restore. With more money, and thus some IT support, just the data they lost (rather than "everything") could be restored.2) Database is corrupted. In this scenario restoring from backup is not really the answer. It needs diagnosis to discover what the hardware fault is (that is, bar some exceedingly obscure bug, the only reason why the database will have become corrupted), then repair the hardware, then consider if you need to restore. If you use high availability mode (i.e. the extra backups) it is probable that there will be no data loss, but it requires some somewhat specialist skills to determine that and make the necessary Restore.3) Machine is dead. In that instance it needs a new SQL Server installation. The right service packs have to be applied, and it may well be that databases need restoring to different location etc. For a new install there will be no backup history, so no point-and-click to make the restore. It will also be important to either set up the database and maintenance jobs etc as they were before, or have identical hardware and restore the MASTER and MSDB databases at the outset so that all "knowledge" about the system is restored too. I don;t think this would be safe for an end user to do unaided.Microsoft SHOULD be provided this as a no-brainer Wizard. Sadly the Wizard that they do provide is best described as brain DEAD IMO - pity, as its a lost opportunity, and when it is needed a well thought through Wizard could make a huge difference to the outcome (short of having a skilled consultant on site at that moment in time). |
|
|
|
ravl13
Starting Member
38 Posts |
Posted - 2011-10-04 : 15:04:37
|
| I did consider the use of a spreadsheet, but there are two problems with that:A) It'd be difficult for two people to work on the spreadsheet at a timeB) Excel's sorting function is not enough to pick out the people they need. For example, what if they wanted people who both 1) didn't have any blank address information 2) Either a) made a donation of at least $100 in the last 5 years or b) made a donation of at least $50 in the last 10 years and also have made a donation of at least $150 at any point in time.I don't know how you could do something like that in Excel.I'm also not a DBA, I'm just an intern basically, who knows some SQL, so I don't know how to do a lot of SQL DBA stuff. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-04 : 15:10:04
|
quote:
Originally posted by tkizer
By the way, if Gail says something, you can bet it's right and is more trustworthy than my info. While I am very skilled, she is more knowledgeable. Pay no attention to my post count. 
Why thank you.  Payment in coffee next week?As for post count, I have 27 000 posts at another site, this is not my main haunt.--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-04 : 15:12:09
|
quote:
Originally posted by ravl13
I'm also not a DBA, I'm just an intern basically, who knows some SQL, so I don't know how to do a lot of SQL DBA stuff.
May I suggest a good book for you to pick up?http://www.sqlservercentral.com/articles/books/76296/</blatant self-promotion>--Gail ShawSQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2011-10-04 : 15:57:54
|
quote:
Originally posted by GilaMonster
quote:
Originally posted by tkizer
By the way, if Gail says something, you can bet it's right and is more trustworthy than my info. While I am very skilled, she is more knowledgeable. Pay no attention to my post count.
Why thank you. Payment in coffee next week?As for post count, I have 27 000 posts at another site, this is not my main haunt.--Gail ShawSQL Server MVP
...and pray tell..what site that might be?Hey if we're adding up post counts...(do they have to contain actually good info?)Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
[resolved] The simplest backup system possible
Look forward to meeting you!Tara Kizer