January 24, 2012 at 9:10 am
I recently created our company's disaster recovery plans which included a daily backup schedule. I walk in today, and one of our databases suddenly has 0 record counts for all 3 primary tables. The why is not the important part right now, but I believe I know it. That being said, I thought this would be no problem...I have backups, so I'll just restore what we have.
I've been using Simple backups to disk, and running them daily. This is not a large database, so it seemed like the best option. When I restored the database nothing changed. I was worried, so I deleted the existing database wondering if that may have some interaction that I just wasn't aware of. I ran the restore again, but still nothing. The database name reappears, the tables are back, but no data has been repopulated, and the Stored Procedure is also missing.
What am I missing in this? I tested this out last week and had no issues. Now, I'm here.
Code I use to run the backup:
--=== Use correct database
Use Master
Go
----------------------------------------
--=== Backup DatabaseNameWithheld
Alter Database DatabaseNameWithheld
Set Recovery Simple
Go
--------------------
Backup Database DatabaseNameWithheld
To Disk = 'C:\DB_Backups\DatabaseNameWithheld.bak'
Go
Code I use to restore:
Restore Database DatabaseNameWithheld
From Disk = 'C:\DB_Backups\DatabaseNameWithheld.bak'
Results displayed:
Processed 192 pages for database 'DatabaseNameWithheld', file 'DatabaseNameWithheld' on file 1.
Processed 2 pages for database 'DatabaseNameWithheld', file 'DatabaseNameWithheld_log' on file 1.
RESTORE DATABASE successfully processed 194 pages in 0.797 seconds (1.893 MB/sec).
Backup file size:
9,186 KB
Any help will be greatly appreciated. Fortunately this is more an attempt at understanding since we didn't have a ton of data on the system yet. I can just reload it manually for now. Just the same this will happen again, and I need to know what I did wrong.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
January 24, 2012 at 9:36 am
jarid.lawson (1/24/2012)
I ran the restore again, but still nothing. The database name reappears, the tables are back, but no data has been repopulated, and the Stored Procedure is also missing.
That means that at the time the backup was taken, the tables were empty and the procedure already deleted. Check an earlier backup, check the default trace, you should be able to figure out when the procedure was dropped at the least from the trace.
p.s. Why are you switching to simple recovery before doing the backup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2012 at 9:58 am
Gail, I see in the backup that he is not using WITH INIT. Does this mean that by default the backups are being appended?
Jared
CE - Microsoft
January 24, 2012 at 10:02 am
If the backup names are the same (and not just obfuscated out), then yes.
That said, if they were appended and the restore as simple as the code shows, it would have restored the oldest backup, and possibly more would have been noticed as missing or wrong.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2012 at 10:08 am
Yep, in 2008 the backup to a file defaults to append. http://msdn.microsoft.com/en-us/library/ms186865.aspx
It is possible that you have multiple backups in this file. You can use RESTORE HEADERONLY to find this out:
http://msdn.microsoft.com/en-us/library/ms178536.aspx
This might help you:
RESTORE HEADERONLY
From Disk = 'C:\DB_Backups\DatabaseNameWithheld.bak'
I would say that backups should be done to separate files, and with INIT.
January 24, 2012 at 10:43 am
As part of my signature so eloquently says, "DOH!!!"
I missed the With File = 4 during my restore. Thank you for catching that.
Now to touch on the best practices part. Is it better to append or replace the records during the back up / restore process?
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
January 24, 2012 at 10:59 am
Neither. All backups to a separate file with a datetime stamp as part of the file name.
Still wondering why you're switching to simple recovery before the backup...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2012 at 11:23 am
I'm with Gail. Separate backup file every day, with timestamp in the name.
Maintenance plans will do this. Ola Hallengren has a great script on this site that can help you with this: http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/62380/
January 24, 2012 at 12:22 pm
Separate file's each day, got it.
As for why I was switching to Simple before the backup, it is a bad habit of mine to explicitly define all the options each time I run a script. That way if someone switches it without me knowing, I'm guaranteed to get the exact setting that I want.
Should I not do this? What are the negative impacts of setting it to Simple each time?
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
January 24, 2012 at 12:33 pm
Switching to simple breaks your recovery chain, if you are backing up logs and expecting to recover to a point in time.
January 24, 2012 at 12:37 pm
Steve Jones - SSC Editor (1/24/2012)
Switching to simple breaks your recovery chain, if you are backing up logs and expecting to recover to a point in time.
I think he is saying that it is already set to Simple and he is not backing up t-logs. However, he includes that piece of script as "habit" in case somebody has switched it to another recovery model. I don't have the knowledge to say that it is doing anything at all when it is already set to simple, but someone else can answer that. As far as being worried that someone will change it... I have concerns about your security set up or the people that you work with 🙂
Jared
CE - Microsoft
January 24, 2012 at 12:40 pm
Does it matter if all I'm trying to do is have a daily backup, not a point in time back up? I can remove it, but I would like to keep it for internal reasons if it won't negatively impact a simply copy (yes I know that is a bad term for this, but it works for my description).
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
January 24, 2012 at 12:50 pm
jarid.lawson (1/24/2012)
Does it matter if all I'm trying to do is have a daily backup, not a point in time back up? I can remove it, but I would like to keep it for internal reasons if it won't negatively impact a simply copy (yes I know that is a bad term for this, but it works for my description).
No, but if you ever decide or need point-in-time and make the change to full recovery and start taking log backups and you forget to take that out, then it will have consequences.
There is no need to repeatedly set a recovery model. Once set it stays that way until it's set differently. This isn't an option that needs defining (ansi nulls, ansi defaults, arithabort, etc), it's a database setting that doesn't change. You don't explicitly set the database to read_write or multi_user before running a script, recovery model is the same.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2012 at 1:27 pm
Tend to agree with Gail. Set it. If it gets changed, investigate why, but it shouldn't be.
If you are only worried about daily backups, leave it in simple.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply