>What does it take to restore database from backup ?
>I have several backup copies, I need to restore at least one to new new machine. Original machine that hosted this database is
>now history. I need to restore it from backup and do some reporting.
>
>What is the best way to get database (data) up an running so I can read them via ODBC

A script like this:

use master

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'myDB')
	DROP DATABASE [myDB]
create database myDB on 
	(name = 'myDB',
	filename=N'o:\sqldb\MSSQL10_50.SQLE7\MSSQL\DATA\myDB.mdf')
	log on (name = 'myDB_log',
	filename=N'o:\sqldb\MSSQL10_50.SQLE7\MSSQL\DATA\myDB.ldf')
restore filelistonly
	from disk='f:\rad\somepath\clients\myDB\myDB20111024.bak'

restore database  [myDB]
	from disk='f:\rad\somepath\clients\myDB\myDB20111024.bak'
	with replace, 
	move 'myDB' to 'o:\sqldb\MSSQL10_50.SQLE7\MSSQL\DATA\myDB.mdf',
	move 'myDB_log' to 'o:\sqldb\MSSQL10_50.SQLE7\MSSQL\DATA\myDB.ldf' 

The important part is "with replace", or else if the logical names, aka internal database names, don't match, it will roll its thumbs for a minute and then tell you that it can't do it - database in use or some other mysterious message. Note that it never hints at number of open connections to the db, who/what has them open, which process IDs, nothing.

Also note that (last time I tried) stuffing the paths into variables just doesn't work. For some reason, SQL (at least version 2000, but probably later ones as well) insists on literals here. Why? Take some hostages from Redmond and see whether they would confess.

Looking at this script, it looks rather redundant to me, but hey, that's TSQL, couldn't get it to work until I had all of this. Spent a whole hour or two of hair pulling frustration over this back in 2004... I'm not going through the frustration again. I've seen Toad do this as a six-liner, but that's just too much clicking around (and then, of course, I have to remember which seemingly innocent clause hides the "with replace", so I forget to flip that from false to true (couldn't they call that just "overwrite", or have some Set Safety Off?).