SQLServerRestoreDatabaseFromFIle

-----------------------------------------------------------------
-- 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
*/