Backup & Restore

Restore And Rename Database Automatically

This logic will automatically restore a database with a new name. All you need to do is provide the database name you want and the path for the database backup file. There’s some extra notations in the logic so you can see where you will need to uncomment a line in case to avoid physical name conflicts.

If you want to include some extra logic to make the backup before the restore logic runs, create/add something like this.

declare	@cmd varchar(max)
set	@cmd = 'backup database ['' +  @database_name + ''] to disk = ''' + @backup_file_name  + ''' with format'
exec	(@cmd)

Here’s the restore logic…

use master;
set nocount on
declare		@database_name			varchar	(255)
declare		@backup_file_name		varchar	(255)
declare		@create_restore_logic		varchar (max)
set		@database_name			= 'MyDatabaseName' -- Enter your database name here
set		@backup_file_name		= '\\MyNetworkShareLocation\MyDatabaseBackup.bak'  -- enter your backup file here
set		@create_restore_logic		= ''
select		@create_restore_logic		= @create_restore_logic + 
'
use master;
set nocount on
go

declare       @filelistonly        table
(
              logicalname          nvarchar		(128)
,             physicalname         nvarchar		(260)
,             [type]               char			(1)
,             filegroupname        nvarchar		(128)
,             size                 numeric		(20,0)
,             maxsize              numeric		(20,0)
,             fileid               bigint
,             createlsn            numeric		(25,0)
,             droplsn              numeric		(25,0)
,             uniqueid             uniqueidentifier
,             readonlylsn          numeric		(25,0)
,             readwritelsn         numeric		(25,0)
,             backupsizeinbytes    bigint
,             sourceblocksize      int
,             filegroupid          int
,             loggroupguid         uniqueidentifier
,             differentialbaselsn  numeric		(25,0)
,             differentialbaseguid uniqueidentifier
,             isreadonl            bit
,             ispresent            bit
,             tdethumbprint        varbinary	(32)
)
insert into 
              @filelistonly        exec			(''restore filelistonly from disk = ''''' + @backup_file_name + ''''''')
declare       @restore_line0       varchar		(255)
declare       @restore_line1       varchar		(255)
declare       @restore_line2       varchar		(255)
declare       @stats               varchar		(255)
declare       @move_files          varchar		(max)
set           @restore_line0       = (''use master; '')
set           @restore_line1       = (''exec master..sp_killallprocessindb ''''' + @database_name + ''''';'')
set           @restore_line2       = (''restore database [' + @database_name + '] from disk = ''''' + @backup_file_name + ''''' with replace, recovery, '')
set           @stats               = (''stats = 20;'')
set           @move_files          = ''''
select        @move_files          = @move_files + ''move '''''' + logicalname + '''''' to '''''' + physicalname + '''''','' + char(10) from @filelistonly order by fileid asc
--  if you are renaming the database you will need to comment out the line above, and uncomment the line below to avoid physicalname conflicts.
--  select        @move_files          = @move_files + ''move '''''' + logicalname + '''''' to '''''' + reverse(right(reverse(physicalname), len(physicalname) - charindex(''\'',reverse(physicalname),1) + 1)) + ' + @database_name + ' + ''_'' + fileid +  right(physicalname, 4)  + '''''','' + char(10) from @filelistonly order by fileid asc
exec 
(
              @restore_line0
+             @restore_line1
+             @restore_line2
+             @move_files
+             @stats
)
go
'

--  uncomment the execution line below to run this logic.
--  exec	(@create_restore_logic) 
select		(@create_restore_logic) for xml path (''), type

By the way…
Just found a bug… So here’s the fix.
This will correct the ‘name’ & ‘integer’ error you might get when running this logic.

Replace the old segment with this new segment:

--  if you are renaming the database you will need to comment out the line above, and uncomment the line below to avoid physicalname conflicts.
-- select        @move_files          = @move_files + ''move '''''' + logicalname + '''''' to '''''' + reverse(right(reverse(physicalname), len(physicalname) - charindex(''\'',reverse(physicalname),1) + 1)) + 
''' + @database_name + ''' + ''_'' + cast(fileid as varchar(4)) +  right(physicalname, 4)  + '''''','' + char(10) from @filelistonly order by fileid asc

Hope this is useful.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s