iHwy Development Blog

The database restore wizard in SQL Studio has always been kind of a pain to wade through and get right. Usually the hassle part comes from having to modify the directory path that the backup will be restored to, because the path it was backed up to is different than the one it will be restored to.

Here's a simple two step script that we use to restore backups, regardless of what path they were originally backed up to. First, run this to basically "introspect" the backup that you want to restore. Modify the path so it points to the actual path where your backup file is:

RESTORE FILELISTONLY FROM  DISK = N'C:\somewhere\mydb_backup.bak'
GO

That will give you back some results that show you the LogicalName and PhysicalName of the data and log backup files. For the next step, you just need the LogicalName(s). I usually just copy them out of the results and paste them into this script where necessary:

RESTORE DATABASE [mydb] -- name of your newly restored db
FROM  DISK = N'C:\somewhere\mydb_backup.bak' -- same as step 1
WITH  FILE = 1, 
MOVE N'mydb_Data' TO N'C:\somewherenew\mydb.mdf',  -- db LogicalName and new db file path/name
MOVE N'mydb_Log' TO N'C:\somewherenew\mydb_1.ldf',  -- log LogicalName and new db log path/name
NOUNLOAD, 
STATS = 10
GO

That's it. This should cover the most common restore, where you have a backup file that you want to restore and create a new db at the same time.

We have a copy of this script in our team utils directory and just modify the file and db names in it whenever we need to do a restore. No more hunt and pecking through the wizard.

I had a case recently where I had to query some data from a Microsoft SQL Server 2005 database and insert a row in the results before the result set. Here's a spiffy little example of how I did it. Neat little trick that can come in handy for cases like this.

-- First you create a var that's a table with cols that match you desired resultset:

declare @tbl table (
  categoryID int, categoryName nvarchar(100)
)

-- Then insert the data you need in the first row:

insert into @tbl (categoryID, categoryName)
 values (-1, 'Unknown')

-- Then select and insert the rest of the data:

insert into @tbl (categoryID, categoryName)
select categoryID, categoryName
from category
order by categoryName

-- Check the results:

select * from @tbl

This was very handy recently inside of a Cold Fusion cfquery tag for adding a "dummy" category to a list of categories. The dummy category got the "Unknown" category name and then the rest of the categories were added to the table. Then in some other code some sets of products got related back to the categories in the table. Products that didn't have a categoryID assigned to them fell into the "Unknown" category for display in an heirarchical tree we did with some jQuery.

This is a neat little trick I picked up. If you've ever needed to create a delimited list from a single column of data stored in SQL Server and felt like it required unnecessary arm wrestling due to SQL syntax limitations, this could be handy for you next time, too:

Let's say you have a column called "lastName" and want to get a comma-delimited list of last names. Here's a way:

-- set up a var for the list

declare @list varchar(1000)
set @list = ''

-- create the delimited list using a query

select @list = @list + lastName + ', ' from person

-- trim off the trailing comma and space if necessary

if @list <> '' set @list = left(@list, len(@list) - 1)

-- take a look at the results

print @list

Here are some example results:

Rubble, Flintstone, Jetson, Munster

Neat trick.

Here's a short script that allows a Microsoft SQL Server administrator to restrict the databases that a SQL Server user will see when they access the database server using SQL Server Management Studio. You'd think that this info was easy to find, but trust us, it's not. It's out there, but usually requires weeding through a lot of irrelevant info to get to the main points:

1. First, using Object Explorer in SQL Management Studio, expand the Security > Logins node of the tree. Then right click and select "New Login..."

2. Enter a login name and some SQL Server authentication info (password) for the user. Leave the "Default database" at "master". Do NOT select any user database in this step. Do NOT go to the User Mapping page in the Login creation dialog to assign the user to any db's, either. The user you are creating will not be the user on any specific databases.

3. Now run this for each db you want your new user to be able to see when they login to SQL Management Studio:

use [someDB] -- use the real db name
go
sp_changedbowner 'theUser' -- use the new username
go

4. Next, deny the new user "public" access to ALL databases:

use master
go
deny VIEW any DATABASE to theUser
go

That pretty much does it. You should now be able to login to SQL Management Studio as that user and only see the databases that they own.

Think of it this way: by making them the owner, you're specifying what databases they can see in SQL Management Studio. Don't mix this up with setting an existing database user as db_owner. It's not the same thing.