Quickly and Safely Move a Microsoft SQL Server Database (MDF and LDF Files) to a New Physical Location (Including Setting Read Write Access)

To move a Microsoft SQL Server database to a new physical location you need to detach, copy, reattach and set permissions for the database MDF and log LDF files associated with the database. I needed to do it as the drive on which each type of database required file (MDF/LDF) was located was to be separately backed up, as per the site backup regulations. I was using SQL Server 2008 R2.

Microsoft’s recommended way of doing this is to use SQL Management Studio. Create a query and detach your database “mydb” by entering and running the following:

use master
go
sp_detach_db ‘mydb’
go

Now copy the MDF and LDF files to their new location and reattach (locations and file names are for this example only):

use master
go
sp_attach_db ‘mydb’,’E:\DATA\mydb.mdf’,’F:\DATA\mydb_log.ldf’
go

You can check the basic properties of the database (and that the file locations have been correctly set) using:

use mydb
go
sp_helpfile
go

The final thing I needed to do was to set the file/folder permissions so that the database could go from read-only to read/write. I first set the folder permissions for my two new folders (“E:\DATA\” and “F:\DATA\” as above). To do this I needed to add the following user to the security settings with full control:

sqlservermssqluser$COMPUTERNAME$mssqlserver

This didn’t actually set the files (server permissions error) so I set the permissions for the “E:\DATA\mydb.mdf” and “F:\DATA\mydb_log.ldf” files individually the same way.

Now open up another query window and type the following to enable read/write access to the database:

use master
go
alter database mydb set read_write with no_wait
go

Now your database is set up exactly as it was previously, only the associated files have moved physical location.

Simple and secure MySQL database backup to gzip using mysqldump in Linux

As part of a larger daily backup cron job script I needed to quickly backup my MySQL databases to individual compressed “gzip” .GZ files. The command to do this is very easy, just run the command and pipe it to “gzip”:

mysqldump -u USERNAME -pPASSWORD DATABASENAME | gzip > OUTPUTFILE.gz

This requires you to actually put in the USERNAME and PASSWORD on the command line, which is obviously a bad idea due to logging of commands and other security reasons.

The MySQL recommended way of doing this is to instead use a separate file containing the login details. You use “mysqldump” with the argument “–defaults-extra-file” and specify the location of a configuration file such as “/root/mysqldetails.cnf”. It is a good idea to create this file and “chown” as root and “chmod” it to be “0400″ which will make it read-only by the “root” user.

chown root:root /root/mysqldetails.cnf
chmod 0400 /root/mysqldetails.cnf

The file itself is a very simple text file and just looks something like:

[client]
host = localhost
user = USERNAME
password = PASSWORD

So now this file has been created and the permissions set correctly, the mysqldump command looks like:

mysqldump –defaults-extra-file=/root/mysqldetails.cnf DATABASENAME | gzip > OUTPUTFILE.gz

The result is OUTPUTFILE.gz which is a compressed copy of your DATABASENAME database, without showing anyone the username and password required to access the database. The “mysqldump” command is very useful and more information can be found in the MySQL documentation.

Disappearing Menus & Menu Items in DotNetNuke

We had to restore a backup of one of our virtual machines (hosted on Microsoft Virtual Server) as the content for one of the DotNetNuke sites had been removed in an editing accident. I copied the backup vhd/vmc files across and restarted the server using the web interface only to find when I checked the website that the DotNetNuke menu, and our Inventua menu were both empty! I logged in and the “host” menu item appeared along with its subtree but still there was nothing else in the menu.

First thought, maybe it’s my browser? I was in Firefox so switched to IE and the same thing, no menu items. I cleared the cache and again, nothing. I even tried another PC, with the same result.

We have had this problem before with DotNetNuke, which is doing its best to convert us to another CMS as a result of slow speed and flaky reliability. Last time we just restored from a backup and the menu reappeared fine, making us believe it was a problem with the database getting corrupted or something (unlikely but a reasonable assumption).

Next I tried clearing the DotNetNuke cache by deleting everything in “[dotnetnuke_install_dir]Portals[portal_number]Cache” and retarting IIS with “iisreset” in a command window on the virtual pc.

Then i deleted browser temp files and refreshed the page, success! So the solution to disappearing menus in DotNetNuke when restoring from a backup is to clear out all the DotNetNuke cache and restart IIS.

also: I highly recommend the Inventua sidemenu module for DotNetNuke. Forget trying to make the default menu skin properly and try this, its been great for us.