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

16 comments:

  1. It is gems like this that make the blogosphere so important. Thank you for taking the time to put your hard work online.

    ReplyDelete
  2. It's people appreciation what makes it worth doing, so thank you.

    ReplyDelete
  3. You have NO idea how long I have been trying to find out to backup and restore -- to make it easy and foolproof.

    But you just made my day.

    Thank you for sharing your expertise. You could have just gone on your way and used your "Aha!" moment for your own use. You chose to give others a lift. Thank you VERY much!

    I'll think of you every time I back up and restore!

    ReplyDelete
  4. Thank you for saying so. It's people's appreciation that make all this worth posting. Which reminds me, I should start writing again :-)

    ReplyDelete
  5. AWSOME POST! I am somtimes restoring my development environment sometime 3 to 4 times a week.

    ReplyDelete
  6. I backup a DB and restore to the same server but with a different DB name. For instance, I back "pro" and restore to "ptest". Will this script work in this scenario or will it need to be tweaked?

    ReplyDelete
  7. Doug, the script would work in your scenario assuming that you specify the name of the database to restore as and that database already exists.

    ReplyDelete
  8. I added a variable for the original DB name so it would get the right file name to restore. Your script works great, thanks for sharing.

    ReplyDelete
  9. thanks a LOT for such a wonderful script !!

    ReplyDelete
  10. Awesome - I was able to modify it very easily to fit our environment! Thanks a bunch.

    ReplyDelete
  11. Thank you so much for posting this. But had a quick question. In my scenario i have four different backup files created by red gate backups. How can I assign these files to the restore script. Any ideas would be appreciated.

    ReplyDelete
  12. I haven't worked with Red Gate backups so I don't know.

    ReplyDelete
  13. Brilliant Stuff, thanks a million.

    ReplyDelete
  14. Brilliant!!

    Thank you for posting this.

    ReplyDelete

// //]]>