SQL Server Db stuck in restore state

It is possible to end up with a database stuck in a restoring state either you have specified the restore with no recovery or the client has disconnected whilst the restore is running or you have run out of disk space.

If the situation is that the database has fully restored but has not not recieved the recover command the following tSQL will put it back to an operational state

RESTORE DATABASE WITH RECOVERY

DLink DNS 323

The DNS 323 is a relatively cheap ~$150 Network Attached Storage device (NAS) that I use at home to store all the important stuff (family photos etc), back and synchronize my work directories on all my computers and to stream movies to our PS3 (unfortunately cannot stream microsoft's TV format - .wtv)

It has a little 200mhz ARM processor running a cut down version in Linux and a web interface to make all setup and changes.

Here is the link to its homepage
http://www.dlink.com/products/?pid=DNS-323
Basic services offered are:

• NAS disk share
• AV server for streaming media
• USB port to attach a network printer (see notes on SharePort utility below- don't even bother trying to use it as a print server as DLink advertises)
• ftp server
• iTunes server
• + a few others
  
• + you can install other Linux packages if you are technical
  

Overall I am very happy with it for the last 4 years. It has gone through a series of drives and a series of RAID configurations as I have filled it up and run out of space. There are few things to both know and be aware of with it though - here is a list of what I am aware of

Disk Size - 2TB now supported
With firmware v1.8 or v1.9 installed it is capable of holding 2 X 2TB disks. Yay. Be careful of the disks you buy though. DLink has tested only Western Digital WD20EADS 2TB drives. There are potential problems with (most) 2TB disks on the market using the newer 4K sectors and AFT technology. Look into it very carefully before you buy a disk. I just went with the DLink recommended WD disks - be careful they are the exact ones though - the WD20EARS 2TB drives do not work (1 character different - beware)

Speed
The 323 is very slow in file transfers to windows machines. A fair bit of this is due to the SAMBA configuration on the 323, a fair bit due to the limited CPU and a fair but due to the windows configurations and network card used. Basically this device is not up to much with its little ARM processor and the SAMBA configuration as set up is very CPU intensive with 64K buffers. In addition file transfers will be slower if the windows machine has a low powered CPU. Still - it streams HD no problems for me.

Use FTP. If you are doing a lot of large file transfers turn on the built in ftp server and use ftp to transfer the files. You will find it will be double the speed. Filezilla is a good ftp client for windows.

SharePort Utility
This DLink utility shares the USB port on the back of the 323 across the network. I use it to share a Lexmark 3200 series printer. The concept is great - apparently if you attach a powered USB hub you can share multiple devices.

I couldn't get SharePort working but eventually struck on the magic combination. I had initially installed v1.17 addon and on the 323 and was using 1.17 windows utility software on my PC. It could not see any in USB device inserted into the 323

The magic combination (for me) was SharePort v1.17 addon on the 323 and SharePort v3.0 windows utility software on my PC.

I will update this post if I find anything else to be aware of about this great little box. Feel free to add comments if you know of any other "gotchya's"

Listing MySQL functions and Procedures

Maintaining and updating stored procedures and functions in MySQL often will require root access which in practice means that you might need to have some interaction with your hosting provider if you wish to use them.

Operating the the standard phpMyAdmin interface gives no support for them other then the ability to create them through script.

The following bits of SQL are useful for viewing stored procedures and functions in a hosted database accessed through phpMyAdmin

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE="FUNCTION"
AND ROUTINE_SCHEMA="database"

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE="PROCEDURE"
AND ROUTINE_SCHEMA="database"

Also the "SHOW PROCEDURE STATUS" will give details of stored procedures and functions

Dealing with MySQL Function

Newer Versions of MySQL have the binary log which will generate te following error

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

To fix this you need incude the following keywords

• CONTAINS SQL indicates that the routine does not contain statements that read or write data. This is the default if none of these characteristics is given explicitly. Examples of such statements are SET @x = 1 or DO RELEASE_LOCK('abc'), which execute but neither read nor write data.

• NO SQL indicates that the routine contains no SQL statements.

• READS SQL DATA indicates that the routine contains statements that read data (for example, SELECT), but not statements that write data.

• MODIFIES SQL DATA indicates that the routine contains statements that may write data (for example, INSERT or DELETE).

Ther referenced artical indicates using
SET GLOBAL log_bin_trust_function_creators = 1;

References
http://mvnrepository.blogspot.com/2009/08/this-function-has-none-of-deterministic.html


Example
DELIMITER $$

DROP FUNCTION IF EXISTS `adadmin_adem`.`fDeleteTheme` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `fDeleteTheme`(id_in INTEGER) RETURNS int(11)
DETERMINISTIC
READS SQL DATA
MODIFIES SQL DATA
BEGIN


DECLARE counter INTEGER;
DECLARE parent_id INTEGER;


SELECT count(id_theme) FROM s_themes where parent = id_in INTO counter;


IF counter = 0 THEN

SELECT parent FROM s_themes where id_theme = id_in INTO parent_id;
DELETE FROM adem.s_themes WHERE id_theme = id_in;
END IF;

RETURN parent_id;

END $$

DELIMITER ;