-----------------------------------------------------------------

-- Restore database from file

-----------------------------------------------------------------

use master

go

declare @ backupFileName varchar ( 100 ) , @ restoreDirectory varchar ( 100 ) ,

@ databaseDataFilename varchar ( 100 ) , @ databaseLogFilename varchar ( 100 ) ,

@ databaseDataFile varchar ( 100 ) , @ databaseLogFile varchar ( 100 ) ,

@ databaseName varchar ( 100 ) , @ execSql nvarchar ( 1000 )

-- Set the name of the database to restore

set @ databaseName = 'myDatabase'

-- Set the path to the directory containing the database backup

set @ restoreDirectory = 'aboslute_path_to_restore_directory' -- such as 'c:\temp\'

-- Create the backup file name based on the restore directory, the database name and today's date

set @ backupFileName = @ restoreDirectory + @ databaseName + '-' + replace ( convert ( varchar, getdate () , 110 ) , '-', '.' ) + '.bak'

-- Get the data file and its path

select @ databaseDataFile = rtrim ( [Name] ) ,

@ databaseDataFilename = rtrim ( [Filename] )

from master.dbo.sysaltfiles as files

inner join

master.dbo.sysfilegroups as groups

on

files.groupID = groups.groupID

where DBID = (

select dbid

from master.dbo.sysdatabases

where [Name] = @ databaseName

)

-- Get the log file and its path

select @ databaseLogFile = rtrim ( [Name] ) ,

@ databaseLogFilename = rtrim ( [Filename] )

from master.dbo.sysaltfiles as files

where DBID = (

select dbid

from master.dbo.sysdatabases

where [Name] = @ databaseName

)

and

groupID = 0

print 'Killing active connections to the "' + @ databaseName + '" database'

-- Create the sql to kill the active database connections

set @ execSql = ''

select @ execSql = @ execSql + 'kill ' + convert ( char ( 10 ) , spid ) + ' '

from master.dbo.sysprocesses

where db_name ( dbid ) = @ databaseName

and

DBID <> 0

and

spid <> @@ spid

exec (@ execSql )

print 'Restoring "' + @ databaseName + '" database from "' + @ backupFileName + '" with '

print ' data file "' + @ databaseDataFile + '" located at "' + @ databaseDataFilename + '"'

print ' log file "' + @ databaseLogFile + '" located at "' + @ databaseLogFilename + '"'

set @ execSql = '

restore database [' + @ databaseName + ']

from disk = ''' + @ backupFileName + '''

with

file = 1,

move ''' + @ databaseDataFile + ''' to ' + '''' + @ databaseDataFilename + ''',

move ''' + @ databaseLogFile + ''' to ' + '''' + @ databaseLogFilename + ''',

norewind,

nounload,

replace'

exec sp_executesql @ execSql

exec ( 'use ' + @ databaseName )

go

-- If needed, restore the database user associated with the database

/*

exec sp_revokedbaccess 'myDBUser'

go

exec sp_grantdbaccess 'myDBUser', 'myDBUser'

go

exec sp_addrolemember 'db_owner', 'myDBUser'

go

use master

go