trentgardner.net

Move SQL Server Database Files

Posted on August 2, 2011 by Trent Gardner

At work we had a SQL Server 2008 R2 database server with about 15 or so databases on it, by default SQL had been configured to put all the *.mdf, *.ldf files in the system drive.

The goal was to move them to a secondary drive that had more than ample space. From reading the Microsoft KnowledgeBase, I realised I would have to detach the database, move the files and reattach the database from the new location. As a programmer, I’m inherently ~~lazy~~ efficient, so I decided to write a batch script to do all the heavy lifting. Now, arguably the process of writing the batch took longer than it would have taken for me to just manually move the databases, but where’s the fun in that? Who knows, you may even find it useful and save some time yourself.

So here it is:

@echo off
 
if [%3] EQU [] (
	echo Usage: sql_migrate ^<SQLSERVER^> ^<SOURCEFOLDER^> ^<DESTFOLDER^>
	GOTO:EOF
)
 
SET SQLSERVER=%1
SET SOURCEFOLDER=%~2
SET DESTFOLDER=%~3
 
SET DBQRY=select DB_NAME(r.database_id), '@' + REPLACE(REPLACE(r.physical_name,'%SOURCEFOLDER%\',''),'%SOURCEFOLDER%','') AS DataFile, '@' + REPLACE(REPLACE(l.physical_name,'%SOURCEFOLDER%\',''),'%SOURCEFOLDER%','') AS LogFile from sys.master_files r inner join sys.master_files l on r.database_id = l.database_id AND l.type_desc = 'LOG' WHERE r.type_desc = 'ROWS' AND DB_NAME(r.database_id) NOT IN ('master','model','msdb','tempdb')
 
SET TEMPFILE=%temp%\sqlmigratedb.tmp
 
sqlcmd -S %SQLSERVER% -Q "%DBQRY%" -o "%TEMPFILE%" -W
 
for /f "skip=2 tokens=1,2,3 delims=@" %%a in (%TEMPFILE%) do (
	if NOT [%%b] EQU [] (
		IF EXIST "%SOURCEFOLDER%\%%b" (
			echo Moving %%a
			sqlcmd -S %SQLSERVER% -Q "ALTER DATABASE %%a SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
			sqlcmd -S %SQLSERVER% -Q "sp_detach_db '%%a'"
			copy /Y "%SOURCEFOLDER%\%%b" "%DESTFOLDER%\%%b"
			copy /Y "%SOURCEFOLDER%\%%c" "%DESTFOLDER%\%%c"
			sqlcmd -S %SQLSERVER% -Q "sp_attach_db '%%a','%DESTFOLDER%\%%b','%DESTFOLDER%\%%c'"
			sqlcmd -S %SQLSERVER% -Q "ALTER DATABASE %%a SET MULTI_USER "
 
			if %errorlevel% LEQ 1 (
				echo Deleting Existing Files
				del "%SOURCEFOLDER%\%%b"
				del "%SOURCEFOLDER%\%%c"
			)
		)
	)
)
 
del "%TEMPFILE%"

It works by pulling a list of databases out of SQL, then for each database:

Setting the database to single-user mode (Killing any existing connections)
Detach the database
Copy the database files to the destination folder
Attach the database from the destination
If all went well, delete the originals

You would then use it like:

sql_migrate.bat . "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA" d:\data
Moving ONQWholesale
        1 file(s) copied.
        1 file(s) copied.
Deleting Existing Files
Moving AdventureWorks
        1 file(s) copied.
        1 file(s) copied.
Deleting Existing Files

You can download the batch from here.

Posted in SQL | Tagged batch, server, sql, windows | 1 Comment

Serialize JSON with jQuery

Posted on May 9, 2011 by Trent Gardner

I was reading an interesting article on about JSON serialization and decided to wrap it up in as a jQuery extension to include in my development library. Most of the code has been borrowed from the original implementation, I’ve just jQueryifed things. So here it is:

jQuery(function($) {
    $.extend({
        serializeJSON: function(obj) {
            var t = typeof(obj);
            if(t != "object" || obj === null) {
                // simple data type
                if(t == "string") obj = '"' + obj + '"';
                return String(obj);
            } else {
                // array or object
                var json = [], arr = (obj && obj.constructor == Array);
 
                $.each(obj, function(k, v) {
                    t = typeof(v);
                    if(t == "string") v = '"' + v + '"';
                    else if (t == "object" & v !== null) v = $.serializeJSON(v)
                    json.push((arr ? "" : '"' + k + '":') + String(v));
                });
 
                return (arr ? "[" : "{") + String(json) + (arr ? "]" : "}");
            }
        }
    });
});

And to use it, you would do something like

$(document).ready(function () {
 
    var obj1 = {
        b1: true,
        s1: "text string",
        n1: 12345,
        n2: null,
        n3: undefined,
        a1: [1, 1, 2, 3, 5, 8, [13, 21, 34]],
        o1: {
            a: [3, 2, 1],
            b: {
                c: 42,
                d: [3.14, 1.618]
            }
        }
    };
 
    alert($.serializeJSON(obj1));
 
});

Posted in jQuery | Tagged jQuery, JSON, serialize | 3 Comments

ASP.NET WebMethods with jQuery and AJAX

Posted on April 20, 2011 by Trent Gardner

I haven’t updated this blog in a long, long time, but today I was working on something interesting and thought I should write it down before I forget.

I was working on an ASP.NET application and needed to perform an AJAX request, I could have used the Microsoft AJAX framework, but frankly I don’t like it. Using the Microsoft ScriptManager element pulls in a lot of extra bloat that really isn’t necessary and the project was already using jQuery for animations.
ASP provides these things called WebMethods (You’d probably be familiar with these if you’ve ever implemented a ASP.NET WebService), basically they’re a server side function that can be invoked similar to that of a REST API, a la Flickr.

Basically in your ASP.NET page code-behind file you just need to add a static / shared method that is decorated with a special WebMethod attribute.

VB.NET

<System.Web.Services.WebMethod()> _
Public Shared Function HelloWorld(ByVal name As String) As String
    Return String.Format("Hello world, meet {0}", name)
End Function

[System.Web.Services.WebMethod]
public static string HelloWorld(string name)
{
    return string.Format("Hello world, meet {0}", name);
}

On the front end, I wrote a jQuery extension to consume these methods. This just needs to be included anywhere on the page.

jQuery(function ($) {    
    $.extend({
        webMethod: function (method, data, onSuccess, onFail) {
            var loc = window.location.href.split('?')[0];
            if (loc.substr(loc.length - 1, 1) == "/")
                loc = loc + "Default.aspx";
 
            // Serialize the data object with no whitespace 
            // (.NET requirement)
            var pairs = [];
            for (var i in data) {
                pairs.push(i + ':' + data[i]);
            }
            data = '{' + pairs.join(',') + '}';
 
            // Perform the post operation 
            $.ajax({
                type: "POST"
                , url: loc + "/" + method
                , 'data': data
                , contentType: "application/json; charset=utf-8"
                , dataType: "json"
                , success: onSuccess
                , error: function (jqXHR, textStatus, errorThrown) {
                    // The .NET error and stacktrace is hidden 
                    // inside the XMLHttpRequest response
                    if($.isFunction(onFail))
                        onFail($.parseJSON(jqXHR.response));
                }
            });
        }
    });
});

To invoke the above you’d do something like:

jQuery.webMethod('HelloWorld', { 
    name: 'Trent'
}, onSuccess, onFailure);
 
function onSuccess(data) {
    // The response from the function is in the attribute d
    alert(data.d);
}
 
function onFailure(data) {
    alert(data.Message + "\n" + data.StackTrace);
}

This will perform an AJAX request to the server and invoke your server side WebMethod.
If anyone has any comments or feedback, please let me know.

Posted in .NET, jQuery | 7 Comments

Create a RAID 1 Array with an Existing Drive

Posted on April 8, 2009 by Trent Gardner

So, last month the IT department at DIRC was made redundant due to funding issues (The joys of working for the not-for-profit sector), as a result myself and colleague David were out of a job. If you’re looking for a new media consultant, Dave’s your man.

Luckily, after a weeks vacation and procrastinating about updating my resume / beginning to find a new source of income to cover my rent, I was offered a new job at http://www.tobacco.net.au – Cheap Cigarettes made in Australia. The work’s completely different, so it’s a great new learning curve, and hopefully the addictive qualities of nicatine should keep me in a job! 😉

I’ve got a new HP DL585 server that I’m using for development, and let me say, this thing is a beast. I set it up about 2 weeks ago with Fedora Core 10, but the problem was all our hard drives didn’t arrive with the server, and I needed to get it up and running. I needed to create a RAID 1 mirror of the 36GB 15k SAS system drive but only had 1 of the drives.

This morning, the second drive arrived and I had to figure out how to create a mirror of the existing drive, without having to reinstall the entire OS again or lose any data. Thanks to the power of the command line it made light work of a seemingly daunting problem.

First, create an image of your drive onto another temporary drive:
Mount a partition to store the backup image of the system drive, in this case I used /dev/cciss/c0d1p1

mkdir /media/backup
mount -t ext3 /dev/cciss/c0d1p1 /media/backup

Create an image of the drive you’re wanting to use for the RAID array (/dev/cciss/c0d0)

dd bs=4k if=/dev/cciss/c0d0 of=/media/backup/backup.img conv=noerror,sync

Secondly, install the new drive being used as the mirror and create the RAID 1 array using the controller.

Finally, reboot with the Fedora CD in the drive and enter recovery mode. Just skip past any menus that come up until you’re presented with a command prompt.
Now mount the partition with the backup image

mkdir /mnt/backup
mount -t ext3 /dev/cciss/c0d1p1 /mnt/backup

Restore the system drive

dd bs=4k if=/mnt/backup/backup.img of=/dev/cciss/c0d0

Once complete, take the Fedora CD out of the drive and reboot, and the system should be like you never touched it.

This new job has me doing 12 hour days when including travel to and from, so I’ve barely had time to myself. When I get a few days off, I’ll try and get a new update on Flickr Manager pushed out.

Posted in Linux | Tagged fedora, hdd, raid, raid 1, work | 4 Comments

What’s new in Flickr Manager 2.3

Posted on February 15, 2009 by Trent Gardner

In this latest installment of Flickr Manager, you may have noticed some changes to the media panel. The first change is the separation of the browse panel into two separate tabs, “My Photos”, “My Photosets”. Secondly, a new option to hide the licensing information when browsing public photos allows for more photos to be displayed per page, as seen below:

The method for insertion of images has changed to give you greater control over your Flickr photos. The save button will update the photo information directly on Flickr. When inserting a photo, the title field will reflect the caption to be displayed in the Lightbox or Highslide overlay (No save is necessary for this to take effect).

Finally this update includes some security updates which address several vulnerabilities, and it’s highly recommended that you upgrade.

Posted in Plugins | 40 Comments

Flickr Manager I18N

Posted on January 1, 2009 by Trent Gardner

To bring in the new year, I bring you Flickr Manager 2.1. The updated version contains a number of bug fixes, interface revamps, but the main new feature is the support for internationalization.

EDIT:

A Launchpad translation project has been setup: Click here to view a list of all currently available translations. Very briefly, Launchpad will enable a community of translators to help translate the plugin into whatever language is needed.

How to translate with Launchpad

Setup a Launchpad.net account.
Select your preferred languages. This will be the language you want to translate.
You should see your language show up in this list. Simply click the name to get started with the translations. The system will try to offer suggests where available (retrieved from other open source projects).
Your translations are saved as you proceed. When it comes time to package a release, your translation will be automatically included in the release for everyone to use. Thanks for your help!

Posted in Uncategorized | Tagged flickr, flickr-manager, i18n, internationalization, plugin, wordpress | 16 Comments

WFM Update

Posted on April 29, 2008 by Trent Gardner

WordPress Flickr Manager 2.0 has been released.

I’ve created a new page here that will contain all future updates and information about the plugin.

Thanks,
Trent

Posted in Uncategorized | 18 Comments

Flickr Manager gets photo sets in 1.5.0

Posted on February 12, 2008 by Trent Gardner

In this final release of 1.5.0 I’ve added photo set support that allows you to browse your personal photo sets as well as take advantage of the Lightbox photo set functionality when inserting photos into posts. An example:

Wordcamp Melbourne 2007

Also PHP 5 is now required to use this plugin otherwise you’ll receive the following error:
ERROR: You’re currently running 4.x.x and you must have at least PHP 5 in order to use Flickr Manager!

If you don’t have PHP 5 and have no way of installing it I believe version 1.4.5 will still function in some of the later releases of PHP 4.

The new Browse Panel

[download#2#image]

Posted in Uncategorized | Tagged flickr-manager, plugin, wordpress | 106 Comments

Flickr Manager gets a new Framework

Posted on February 8, 2008 by Trent Gardner

So some of you may have noticed already that yesterday I released version 1.5.0a of my Flickr Manager plugin. The main point of this version update was because of all the development that’s been going on lately, the code has been getting rather thrown together and confusing, so I’ve restructured and rewritten most of the code to make it easier to understand and develop future releases. Because of such a major overhaul a few bugs may have slipped through so this is the reason for releasing it as an alpha. So please if you try the new release of the plugin and are experiencing problems let me know with as many details as possible. Otherwise if you’re not game enough to try out the new version please revert back to 1.4.5 (available here).

I’ve also added a “Limit size of images in the browse window” option, which keeps all the images in the browse panel to maintain a constant size of either square or thumbnail depending on your preference to save loading times.

[download#2#image]

Posted in Uncategorized | Tagged flickr-manager, plugin, wordpress | 10 Comments

How to translate with Launchpad

Archives