September 2, 2008 at 4:30 pm
SOLVED - Thanks!
I am not very experienced with database administration (I'm a C# software dev) and I must give explanations about how to restore from a full backup in the event that a nuclear explosion destroys the world and all that is left is the CD containing the full backup of your database you thoughtfully brought with you in the nuclear shelter.
So, first thing you do is steal the laptop of someone in the shelter and start working on your database to release some tension. The laptop is freshly formatted, so you install SQL Server and SQL Server Management Studio and then, what do you do?
Now, in the confusion, you can't remember anything about the database, so you can't rebuild the structure or even remember its name!
I tried going in SSMS, a right-click on "databases", then "Restore database...". This opened a utility for restoring a database, so I type in a name for the database I want to create and restore to and I select the source "From device" to browse to my file.
Until there, everything was fine, but when I select the file to return to the "Restore database" dialog, it gives me an error like this :
"Cannot restore backup device 'C:\demo.bak'. Operating system error 5 (error not found). RESTORE HEADERONLY is terminating abnormally (Microsoft Sql Server, Error 3201)"
I'm pretty sure the error can be found because I just did... Now, what am I doing wrong from my cold dark nuclear shelter?
September 2, 2008 at 10:09 pm
Do you get anything when you run the following command or do you get another error?
restore headeronly from disk='c:\demo.bak'
September 3, 2008 at 1:07 am
Interesting situation: The whole world is blown up, and you're concerned about restoring a database 😀 (Probably a 1TB Axapta database?)
Wilfred
The best things in life are the simple things
September 3, 2008 at 6:57 am
I just assumed that the database held the location of all the food stores, medical supplies, porn, etc.
September 3, 2008 at 11:49 am
Thanks for the replies! 🙂
The query based restore doesn't work either. I get the following error.
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'c:\demo.bak'. Operating system error 5(error not found).
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.
I tried the query on both master and a clean new database called Bob.
I'm thinking it may be that I may be doing my backup wrong or something. I learned to use SQL for the applications I write to query data correctly (and efficiently), but I hardly ever managed a server.
I made the backup by going in the context menu of the database in "Tasks -> Back up...". I left the defaults everywhere (full backup, the default destination is fine, it's empty and I just copy it to CD later).
I'm using Windows Authentification and my Windows user has sysadmin role so permissions shouldn't be an issue either. I also made sure the database files were deleted correctly (because I understand they may not be deleted if the database is in use when it is deleted).
One more thing, I'm using SQL Server 2005 Express with SSMS Express (without advanced services), but the procedure I need to write should apply to other editions as much as possible.
And I'm not looking for porn, I simply forgot to punch out before going in the shelter so my conscience won't let me worry about personal matters such as survival... 😉
September 3, 2008 at 12:05 pm
Ah, I just dug through some posts (see : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=283827&SiteID=1) and found my problem.
It seems restoring from a backup requires read permissions on the .bak file. And since SQL Server doesn't run with my user account's permissions, but those of some guy called "PROGRAMMEUR\SQLServer2005MSSQLUser$PROGRAMMEUR$SQLEXPRESS", it didn't have the NTFS permissions to read the backup file!
Stupid mistake... I hope this helps some other survivors!
Thanks for all replies 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply