DougHughes.net

MS SQL Backup and Restore Procedures

Continuing on my SQL search script posted yesterday, I am posting two stored procedures for backing up and restoring Microsoft SQL databases easily.

I use the backup script frequently when publishing or making large changes to databases. Obviously, this script should really be used in addition to a scheduled maintenance and backup plan and is not intended to replace that.

Another thing to keep in mind is that these two procedures just abstract calling the built in backup and restore commands. I think this syntax is easier to remember and the hard coded options are right for what I tend to do. Feel free to tweak them as needed. I tend to create these procedures in my master database.

Here’s the SQL for BackupDatabase.

CREATE PROCEDURE BackupDatabase
	@database varchar(100),
	@backupDir varchar(500)
AS

IF RIGHT(@backupDir, 1) = ''
	SET @backupDir = @backupDir + @database + '.bak'
ELSE
	SET @backupDir = @backupDir + '' + @database + '.bak'

BACKUP DATABASE @database
TO DISK = @backupDir
WITH STATS = 10

The BackupDatabase procedure accepts to parameters, the name of the database to backup and the path to the directory where the backup file will be written. The following is an example of how to call the procedure.

master..BackupDatabase 'Northwind', 'd:temp'

This example will backup the Northwind database to the d:temp directory, creating a file named Northwind.bak. One thing I might suggest doing is altering the procedure to append the current date to the end of the file name.

The RestoreDatabase procedure depends on a helper procedure which returns the files which are contained within a backup file. You can call this procedure directly, but there’s really not much point to this. Execute the following SQL to create the procedure.

CREATE PROCEDURE GetBackupFileList @backupFile varchar(500)
AS
RESTORE FILELISTONLY FROM DISK = @backupFile

Once you have created the GetBackupFileList procedure the RestoreDatabase procedure can be created by executing this SQL code:

CREATE PROCEDURE RestoreDatabase @backupFile varchar(500),
@newDatabaseName varchar(100)
AS
-- // create a temp table to hold the file list for the db
CREATE TABLE #fileList (
	LogicalName nvarchar(128),
	PhysicalName nvarchar(260),
	Type char(1),
	FileGroupName nvarchar(128),
	Size numeric(20,0),
	MaxSize numeric(20,0)
)

-- // Declare a variable to hold the restore statement
DECLARE @RestoreStmt varchar(500)

-- // start the restore stmnt
SET @RestoreStmt = '
RESTORE DATABASE ' + @newDatabaseName + '
FROM DISK = '' + @backupFile + ''
WITH
'
INSERT #fileList
EXECUTE GetBackupFileList @backupFile

--- // declare some vars to hold the filenames and types
DECLARE @LogicalName nvarchar(128)
DECLARE @PhysicalName nvarchar(260)
DECLARE @Type char(1)
DECLARE @LastSlash int

DECLARE fileListCsr CURSOR FOR
	SELECT LogicalName, PhysicalName, Type
	FROM #fileList
	OPEN fileListCsr

	FETCH NEXT FROM fileListCsr INTO @LogicalName, @PhysicalName, @Type
	WHILE @@FETCH_STATUS = 0
	BEGIN
	-- // get the path from @PhysicalName
	SET @LastSlash = CHARINDEX('', REVERSE(@PhysicalName))
	SET @PhysicalName = LEFT(@PhysicalName, LEN(@PhysicalName) - @LastSlash) + ''

	-- // check the type of this file
	IF @Type = 'D'
		SET @PhysicalName = @PhysicalName + @newDatabaseName + '_data.mdf'
	ELSE
		SET @PhysicalName = @PhysicalName + @newDatabaseName + '_log.ldf'

	-- // update the file paths
	SET @RestoreStmt = @RestoreStmt + '
	MOVE '' + @LogicalName + '' TO '' + @PhysicalName + '',
	'
	FETCH NEXT FROM fileListCsr INTO @LogicalName, @PhysicalName, @Type
END

-- // add stats
SET @RestoreStmt = @RestoreStmt + ' STATS = 10'
PRINT @RestoreStmt
CLOSE fileListCsr
DROP TABLE #fileList

-- // execute the restore stmnt
EXEC( @RestoreStmt )

This RestoreDatabase procedure accepts two parameters, the path to the backup file and the name of the database to restore. The following is an example which restores the backup file we created earlier to a database named NewNorthwind.

master..RestoreDatabase 'd:tempNorthwind.bak', 'NewNorthwind'

I’ve found these two procedures to be very useful over the years, I hope you do too.

Please let me know if you have any feedback.

Brute Force SQL Search Procedure

I’ve been working lately on writing extensions to a complex C# application which has gigantic Microsoft SQL database behind it. Unfortunately, the software is still alpha and only has limited documentation. This means I spend a lot of time guessing where data is stored. The problems lead me to write a very quick and dirty, brute force, stored procedure to search across all the tables and columns in a database.

Before I show the code, let me say that it is not really an example of a good practice. One could convincingly argue that it’s chock-full of bad practices. However, the only point behind the procedure is to find tables and columns which contain data.

Do not, I repeat, do not use this from a web page. It is only intended to be a quick tool you can use to search your database.

And with that disclaimer out of the way, here’s the procedure. I created the procedure in my master database, you might choose to put it somewhere else.

CREATE PROCEDURE search @DATABASE VARCHAR (400), @string VARCHAR (400) AS

--create a table which will hold all of the table names and columns
--FOR USER defined tables.

CREATE TABLE #TableCache (
	tableName VARCHAR (400),
	columnName VARCHAR (400)
)

CREATE TABLE #Matches (
	tableName VARCHAR (400),
	columnName VARCHAR (400)
)
--create the statement to get all of the userdefined tables and columns
--and insert them into the#TableCache
DECLARE @sqlStatement AS VARCHAR (8000)

--this does not search money, ntext, text, image types
SET@sqlStatement = '
INSERT INTO #TableCache
SELECT so.name as tableName, sc.name as columnName
FROM ' + @DATABASE + '.dbo.sysobjects as so JOIN ' + @DATABASE + '.dbo.syscolumns as sc
ON so.id = sc.id
WHERE so.xtype = ' U ' AND sc.xtype NOT IN (34, 99, 35, 60)'

EXEC (@sqlStatement)

--declare vars FOR the upcoming CURSOR
DECLARE @tableName VARCHAR (400)
DECLARE @columnName VARCHAR (400)

--create a cursor
DECLARE tableColumn CURSOR FOR
	SELECT *
	FROM #TableCache

--loop over the cursor
OPEN tableColumn FETCH NEXT
	FROM tableColumn
	INTO @tableName, @columnName
	WHILE @@FETCH_STATUS = 0

	BEGIN
		-- FOR every TABLE AND COLUMN IN the DATABASE CHECK TO see IF they contain
		-- the string being searched FOR. IF so, ADD them TO the #matches temp TABLE.
		SET @sqlStatement = '
		IF (
		SELECT COUNT(*)
		FROM ' + @DATABASE + '.dbo.' + @tableName + '
		WHERE ' + @columnName + ' like ' % ' + @string + ' % '
		) > 0
		INSERT INTO #Matches
		VALUES ('' + @tableName + '', '' + @columnName + '')
		'

		EXEC (@sqlStatement)

		--next row,	please
		FETCH NEXT FROM tableColumn INTO @tableName, @columnName
END

--destroy the cursor
CLOSE tableColumn
DEALLOCATE tableColumn

--find matching tables
SELECT *
FROM #Matches

--drop the temp table
DROP TABLE #TableCache
DROP TABLE #Matches

Assuming the procedure is in your Master database and that you have permissions to execute the procedure, you can call it from any database using this syntax.

</pre>
master..search 'yourDatabase', 'text to search for'

When executed you will receive a record set containing two columns, “table” and “column”. Each record indicates that a particular column in a particular table contains a string.

The procedure ignores columns types of image, ntext, text, or money. I may have missed a few other types which may cause errors. I leave these up to you to fix.

If you know of a better way to do this please add a comment!

Tutorial on Sending Manual HTTP Requests

From time to time I’ve been able to debug difficult problems by manually making an HTTP request. For example, say you’re using cfcontent to serve binary documents and forget the reset=”yes” attribute. You might end up with some invalid data before your actual binary data which would corrupt the document.

Obviously, in this example you would be smart enough to add the reset=”yes” attribute and get on with your life. But, maybe you’ve forgotten about it or there’s some other problem dealing with the HTTP response which you want to debug. In this case you could make a manual HTTP request to the affected URL and take a look at what exactly is coming back. A browsers job is to hide this from you, so you’ve got to go a layer lower.

A 30 Second Explanation/Review of HTTP:

HTTP, or Hypertext Transport Protocol, is the protocol used to send data to and from web servers. Any time you request a webpage or image over the web you’re using HTTP. That’s where the little http:// you’re always typing comes from.

HTTP connections are really TCP/IP connections over which HTTP instructions are sent. Without getting into details (where I would probably trip up), TCP/IP is a means of making connections to other computers. With TCP/IP you connect to a specific “port” on which another computer listens for a connection. These ports are numbered. Certain port numbers are usualy used for specific purposes. In the case of the web the standard port is 80. This means your web server listens for requests on Port 80 and your web browser makes connections to port 80.

HTTP instructions are a set of commands which tell the web server what it is that you’re requesting so that it can return it. I’m not going to get into details here, but the standard which is used everywhere is HTTP 1.1. The full HTTP standard (known as an RFC) can be seen in a number of places. Here’s one: http://www.w3.org/Protocols/rfc2616/rfc2616.html

When you request a page from a web server your browser makes and TCP/IP connection to the web server. HTTP instructions are sent to the web server and the web server responds with the data requested.

The Anatomy of a Simple HTTP Request

Here’s a very simple HTTP request:

GET /index.cfm?test=1 HTTP/1.1
host: www.myserver.com

Note: The request is followed by a blank line.

This request is, for all intents and purposes, the same thing as typing the following url into your browser:

http://www.myserver.com/index.cfm

Here is the breakdown of the first line of the HTTP request:

GET

This is the method to use when making the HTTP request. When sending manually you will probably want to use GET if possible as POST is a bit more complicated. There are several other methods that are less well known such as HEAD, PUT, DELETE. Each has it’s own purpose, but it’s out side the scope of this article to get into them.

/index.cfm?test=1

This is the absolute path to the file you’re trying to access on the server. URL parameters can be attached to this the way you’re used to doing. In this example URL variable test is passed as “1”. If no file is to be named then just pass “/”.

HTTP/1.1

This tells the server that this is an HTTP 1.1 request, which is pretty much guaranteed to be what you want. Http 1.0 is not sufficient for servers that use virtual hosts. To the best of my knowledge, no other versions of HTTP are widely supported or in use.

Everything which follows the first line are request headers. Request headers add additional data which may modify the server response. These can be things like host headers, cookies, instructions on the type of data the browser will accept and more. Aside from the host header, I’m not going to get into these in this article. If you want more details (and you want to sleep well) then read the RFC.

Headers follow the pattern of:

Header-name: header-value

The example HTTP request above shows a header named “host” with a value of “www.myserver.com”. This tells your server which domain name the request is for and it uses that to look up which virtual server to use.

Each header is put on its own line. Following the headers there must be one blank line. This is always blank.

Everything following the blank line is the body of the HTTP request. This is where posted form data is sent if you’re using the POST method. This can be very complicated if you’re sending binary data and I’m not going to get into that. Let it suffice to say that if you need to send form data the form values are usually sent in the same format as URL variables.

Here’s a quick example of a POST request which also sends URL variables:

POST /index.cfm?test=1 HTTP/1.1
host: www.myserver.com

postedVar=postedValue&anotherVar=Another%20Value

Note: This request has an empty line after the headers and after the request body.

Sending the HTTP Request

Now that you know how to craft HTTP requests sending them to the server is the next logical step. To do this, simply telnet to your server on port 80 and send the request.

I suggest first typing your request out in a text editor before making the connection. This is because you won’t see what you type into the opened connection echoed back to you.

You would establish a connection by opening a command prompt and typing:

telnet www.myserver.com 80

This will open a TCP/IP connection to port 80 on your web server. When you see a message saying you’re connected to the server, simply paste the request you wrote in text editor. Hit the enter key if you forgot the last empty line in the request and you will see an HTTP response come back from the server.

The response will include the full HTTP headers and the raw data sent by the server. This data can be binary data and will probably look like garbled text. In some cases you may need to increase the buffer in your command window so you can see the entire response.

At this point you can see the full HTTP response and you might be able to use the information in it to debug your problem.

Good Luck!

Error: Internet Explorer Cannot Download FileName from WebServer

I recently ran into a bizarre problem. On one particular II6 server, and not another, I was unable to download CSS and JS files when using Internet Explorer. I kept getting the message, “Internet Explorer cannot download FileName from WebServer“. I could download the files using Firefox. I even used telnet and manually sent an http request which correctly returned data. Very Odd.

The scenario is this: I have a particular application installed on four IIS servers: mine, my cube neighbors, a development server and another development server in a different office. Both my server and my neighbors server are IIS 5.1 running on Windows XP Pro. The two development servers are IIS 6 on Windows Server 2003.

I was experiencing a problem in a particular potion of the application when used from the local development server. (The problem being that the page just didn’t do a damn thing!) So, in the effort to debug the problem I viewed the page source and tried to download the JavaScript file which controls the page’s behavior. When it wouldn’t download I knew I had found the problem and prepared to do what was necessary to make sure the file existed or was available.

I was a bit surprised when I went to the server and found the file exactly where is was supposed to be. I assumed two things at this point.

1. IIS 6 was configured incorrectly and not serving .CSS files due to mime type configuration.

Or

1. There was a permissions problem on the file system.

A quick check threw 1 out the window. IIS was correctly configured to serve CSS files. I checked the permissions on the CSS file and surrounding file system structure and found that the files had correct permissions. What the heck was going on?

By accident I tried the URL to the JS file in Firefox and it downloaded! At that point I began trying my neighbors’ Internet Explorer browsers to see if they also had problems. As it turns out they did!

At this point I tested access to the same JS file on the other servers and they all worked. This prompted me to try using telnet and connecting to port 80 on the server with the problem. I manually sent the http request and data came right back. The problem seemed to be specific to this install of IIS 6 and Internet Explorer.

At this point I tried all the classics solutions, reboot my machine, the server, restart IIS. All a no-go.

Mind you, throughout this entire process I was attempting to Google for a result. Microsoft’s website alludes to a hot fix. They advise against using the hot fix to the point where you’ve actually got to call Microsoft to get it. I didn’t feel like doing that.

By a random chance I found this link to a seemingly unrelated problem: http://www.ariacom.com/forum/forums/forum.asp?forumid=12&page=0&select=54. For the heck of it I tried the provided solution and it worked!

Here’s what to do to solve the problem:

• Open IIS Admin
• View the properties for the website
• Click the HTTP Headers Tab
• Uncheck the Enable Content Expiration checkbox.

That’s all. As soon as I did that I was able to download CSS and JS files from the affected IIS 6 server. This resolved the problem I was having with the application I was working on.

Cfreturn Exits Methods

I recently blogged about a minor bug in the Alagad Image Component which left files locked. A reader suggested I talk more about it. The problem, in a nutshell, is that code placed after cfreturn in cffunctions will not execute.

As a rather contrived example, let’s say you want to create a method that will add two numbers, return the value, and call another method with the total. Your method might look like this:

<cffunction name="addEmUp" access="public" output="false">
 <cfargument name="num1" required="yes" type="numeric" />
 <cfargument name="num2" required="yes" type="numeric" />

 <!--- add the attrubutes and store in total --->
 <cfset var total = arguments.num1 + arguments.num2 />

 <!--- return the sum --->
 <cfreturn total />

 <!--- call another method and pass in total --->
 <cfset someOtherMethod(total) />
 </cffunction>

However, the someOtherMethod method will never be called. This is because the cfreturn tag returns the value of total and exits the method. In general, you should typically have your cfreturn tag as the last line in a function. So, the code above would look like this:

<cffunction name="addEmUp" access="public" output="false">
 <cfargument name="num1" required="yes" type="numeric" />
 <cfargument name="num2" required="yes" type="numeric" />

 <!--- add the attrubutes and store in total --->
 <cfset var total = arguments.num1 + arguments.num2 />

 <!--- call another method and pass in total --->
 <cfset someOtherMethod(total) />

 <!--- return the sum --->
 <cfreturn total />
 </cffunction>

There are, of course, uses for having cfreturn in various places in your method. For instance, you might have various paths though the method all of which need to return data at different points. You could place a cfreturn at the end of each of these paths. However, I prefer to create a variable to hold the value to return. Then, my last line of code returns that value.

In the end, to fix the bug in the Image Component, all I needed to do was make my cfreturn the last line of code in the problem method.

Bug Found In Mach-II 1.0.10 Plug-in Execution Order

Today I found a small bug in Mach-II 1.0.10 plugin execution order. One of the new features in 1.0.10 was that plugins would execute in the order defined. However, the configure method in plugins are still executed in random order.

I posted this to the Mach-II forums on fusebox.org and it was later confirmed by Sean Corfield. It is assumed that this will be addressed in a future release. Till then here’s how to fix the bug.

In your MachII/framework folder there is a file PluginManager.cfc. This file’s configure method is the culprit. In version 1.0.10 it reads as follows:

<cffunction access="public" name="configure" returntype="void">
    <cfset key=0 var/>
    <cfloop collection="#variables.plugins#" item="key">
        <cfset getPlugin(key).configure()/>
    </cfloop>
</cffunction>

According to Sean Corfield, this should read like this:

<cffunction access="public" name="configure" output="false" returntype="void">
    <cfset aPlugin=0 var/>
    <cfset i=0 var/>
    <cfloop from="1" index="i" to="#variables.nPlugins#">
        <cfset aPlugin=variables.pluginArray[i]/>
        <cfset aPlugin.configure()/>
    </cfloop>
</cffunction>

I would assume that making this change would be safe to make as future versions of Mach-II would probably implement the exact same functionality.