Sep 5, 2011

SQL script to backup and restore DB

For issue solving purpose frequently I take backups of live SQL database to our development server,
at start I was using the wizard in the SQL management studio to take database backups,
I wanted to do all those things with single click,
I came accross this gr8 blog post,
I modded the script to automatically backup source database(@sourcedatabaseName) and restore it into destination database(@databaseName):

-----------------------------------------------------------------
-- *****    Restore Database Script - Mod by Nitin Sawant   *****
--
-- Modify only three variables:
-- 1. @sourcedatabaseName
-- 2. @databaseName
-- 3. @restoreDirectory
--
-- initial version: http://blog.tech-cats.com/2007/10/sql-server-script-to-restore-database.html
-----------------------------------------------------------------
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), @sourcedatabaseName nvarchar(1000)
-----------------------------------------------------------------
-- Set the name of the database to restore
set @sourcedatabaseName = 'LIVE_DB'--Source database name
set @databaseName = 'TESTING_DB'--Destination database name
set @restoreDirectory = 'D:\' -- such as 'c:\temp\', the directory where the .bak of source DB will be created
-----------------------------------------------------------------

--Take a Fresh DB backup
DECLARE @Dt DATETIME
SET @Dt = GETDATE()
DECLARE @BkAddress NVARCHAR(1000)
--the database name and today's date
set @backupFileName = @restoreDirectory + @sourcedatabaseName + '_'+DATENAME ( day , @Dt )+''+LEFT(DATENAME ( month , @Dt ),3)+''+DATENAME ( year , @Dt )+''+REPLACE(RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7),':','.')+'-'+DATENAME ( s , @Dt )+'.bak'
PRINT 'Creating backup of the database at:' + @BkAddress
EXEC('Backup Database '+@sourcedatabaseName+' To Disk ='''+ @backupFileName+'''')

-- 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
*/
Please note that this script works only if the two databases are hosted on same server.

You can download the .sql file of the script: RestoreDatabase.sql