Search

Thursday, October 18, 2007

SQL Server Script to Restore a Database from File

I frequently have to backup a database from the production machine and restore it in my testing environment. You can do this with SQL Server Enterprise Manager (or other similar tools) but it can get to be a pain if you have to it over and over again. So here is a script to the second part - restore a database from a file to sql server. For the first part, please see my related post called SQL Server Script to Backup a Database to File. To use this script you need to:
  1. Set the database name in the @databaseName variable to your database
  2. Set the @restoreDirectory variable to the absolute directory path where the database backup is found
  3. Run the script (with something like Query Analyzer)
The script will restore a backup of the specified database from the restore directory specified in the @restoreDirectory variable. It will look for a backup filename that consists of the database name, today's date and the '.bak' extension (example: myDatabase-10.18.2007.bak).

Download the script from http://tech-cats.net/blog/downloads/sql/SQLServerRestoreDatabaseFromFile.txt
-----------------------------------------------------------------
-- 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
*/
// //]]>