RSS

Monthly Archives: December 2011

SQL Restore gotcha

I was recently doing some production restores to a new environment and noticed something a bit strange.  I scripted out the restore commands and was running them one after another.  As I was watching the paint dry I noticed in my script that I had a typo in one of my commands. I decided to stop the restore, fix it and re-run.

I got a fairly normal error stating that the files were still there and could not be overwritten without either “with replace” or “with move”.  As a server admin, I didn’t like either of those boring options so I did a DROP on the database that was currently in the “Restoring…” state and kicked off the restore again.  Bam, same error.  So I did the drop again, and this time went to the file system and deleted the data and log files. Running the restore this third time worked just fine.

This struck me as quite odd. I didn’t have a lot of time to look into it, so I tried to recreate the issue at home. Drop’s usually get rid of the files, so I put on my testing gloves.

I first assumed that a database drops delete files off the filesystem.  Ding that is correct.

Second, I assumed that my issue must have been because the database was in “restoring…” mode. EEEnnn!(or however you would spell the buzzer sound) incorrect.

I have SSD at home so the only way I could get any of my testing databases to stay in restoring mode for more than a millisecond was with no recovery. The drop command worked just fine and the files were gone. For a split second, I had almost given up, unable to re-create the issue, but that wouldn’t make for a very interesting blog post.

I decided I needed to get a good amount of data into my database.  First I tried a simple “go 100000” with a simple insert command, that bought me a whole extra millisecond on my restore command. Next, to beef up my database I decided to get fancy.

INSERT INTO b(bloby)
SELECT * FROM OPENROWSET(BULK N'C:SQLServer2008R2SP1-KB2528583-x64-ENU.exe', SINGLE_BLOB) as d;
go 10

Blobs (aka image or the non-deprecated varbinary(max)) are a bit inefficient to store in SQL or any RDBMS. 10 copies of the service pack inside my db gave me enough time to cancel the restore command while it was in progress.

RESTORE DATABASE [Mandy] FROM  DISK = N'C:dbMandy.bak' WITH  FILE = 1,  NOUNLOAD, STATS = 10

Cancel, run a drop command and the file is gone.  Again, I was stumped but since I still had that bug in my head I tried running the same sequence of events but running the drop command from another spid.  Sure enough I couldn’t re-run the database command because the drop on another spid didn’t delete the files.

Msg 3142, Level 16, State 1, Line 1
File "Mandy" cannot be restored over the existing "C:dbMandy.mdf". Reissue the RESTORE statement using
WITH REPLACE to overwrite pre-existing files, or WITH MOVE to identify an alternate location.
Msg 3142, Level 16, State 1, Line 1
File "Mandy_log" cannot be restored over the existing "C:dbMandy_log.ldf". Reissue the RESTORE statement
using WITH REPLACE to overwrite pre-existing files, or WITH MOVE to identify an alternate location.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Personally, I think the intuitive approach would be to delete the files from the filesystem at the point the restore is canceled. That is how a copy/paste works and a canceled restore is useless so MSSQL may as well clean up the steaming pile of database.

So to recreate the issue:

1. make a big database with blob
2. back up the database
3. drop the database
4. start a restore
5. cancel the restore query
6 open new query window(spid) and drop the database that is “restoring…”
7. re-run the restore (without replace)

The important thing to note is, if you get the “with move” or “with replace” message, you probably have some files still hanging around that you should delete.  Either that, or you are doing the restore in the wrong environment and SQL Server is begging you not to do that.

Advertisements
 
Leave a comment

Posted by on December 27, 2011 in SQL Admin

 

C++ or Python

My wife asked me what intro to programming class she should take so I asked her the options. They were python and c++.

At first I laughed a bit and repeated the word, python? Really?

If someone was talking to me about computers and said:

nerd: “I’m a full time developer”
me: “Very cool, what language?”
nerd: “Python”
me: “Really? lol. That is unfortunate.”

I’ve discussed Perl quite a bit recently but haven’t gotten into Python since high school. I decided to poke around a bit before I advised on what class to take. The major downfall of c++ is how difficult it is. The one thing that makes it easy is the Microsoft IDE. I couldn’t find out for sure if the class would use the 2010 C++ IDE from MS. However, I had a pretty good guess they would be using something else. I guessed this because Python is an option, so there has to be a dark side to the decision making when it comes to classes. I had this strange feeling that maybe there was an uptick in Python usage because of the Android OS or the fairly recent needs for cross-platform code. But I don’t think universities make moves based on year-to-year usage.

I am fully aware there is a pretty vibrant nerd culture. The Linux folks who are Microsoft haters will spend countless hours developing something that is “cross-platform” just so they can get some street cred for not using anything Microsoft. I will give street cred to someone who develops systems with a $0 budget, I appreciate that. But to achieve the $0 goal, your time has to be worth nothing.

I’ve spent a lot of time getting linux systems working and writing non-ms code. Using notepad as your IDE was a big one on the forums back in the day. I read several instructions to use notepad instead of that other “crap” because notepad is the only way to go. If you are wanting to write code as a hobby, then by all means use notepad and languages like Python. But, don’t expect to contribute to society while you lurch over a CRT screen with some kind of simple “File not found” or other run-time error. And I know its a run-time error because your guess and check style of “debugging”.

C++ would be the obvious choice for me and should be for any hard core computer person. But I wouldn’t recommend it to my wife just yet.

Python is interpreted at run-time. It does not have to be compiled like c++. This is somewhat alluring to a beginner because they can look at and edit the source code in whatever text editor they would like. That source code file is the only thing that runs, so it doesn’t get confusing. All that is needed is the interpreter to convert the language to machine readable code.

I decided to dig a little bit deeper and write a small Python program.

http://sthurlow.com/python/lesson01/

py_lesson_1_and_2

Above is IDLE. It is a pretty lousy attempt a development environment. As you can see I point out a flaw in python right away. It assumes floating point numbers out of the box. This dates the language quite well because nobody in the 21st century would want to default to a floating point number.

Here is a better example of python. The script files end in .py. The editor does at least have syntax highlighting and string concatenations work with the + operator.

py_lesson_3

And now I have an example of the C++ IDE. Its as powerful as they come, however with great power comes a great many challenges.

c_lesson_3

I had several errors in my code to fix before my application would run. That’s pretty sad but at least I can admit it. I am not a fan of the :: syntax or the need for semicolons to end the line. Also, the main routine line seems a bit “syntaxy”. It wasn’t my first c++ program but I still needed this cheat sheet: http://www.learncpp.com/cpp-tutorial/06-writing-your-first-program/

My intro choices in CIS were VB.NET and Java which I took both of those classes. Had I chosen the computer science path I could have taken COBOL, C++, C, Visual BASIC. Yes, I listed COBOL… don’t believe me? ( http://www.cs.wmich.edu/lowercourses.php )

Its never really the language that gets me to dislike, its the environment in which you use the language. Python is for scriptors, not programmers or developers which I describe some of those differences here: ( https://nujakcities.wordpress.com/2010/08/12/express-vb-net-my-fav/ ). There are players in the game like dropbox and Eve online that do have a pretty large investment in Python ( http://www.python.org/about/quotes/ ) but I still can’t suggest it to anyone that is serious about development. But, for those of you forced to take a “programming” class, I would pick python over C++ if you want the easier route.

 

 
1 Comment

Posted by on December 13, 2011 in Uncategorized