Automations

Automatically Restore All Databases Using Single Network Share

You probably have a network storage location with all your Database Backup files in it. One day you’ll need to place those databases ( or restore the databases ) on a new server so you old server can be properly decommissioned. Here’s some SQL Logic that will automatically do that for you. Doesn’t matter how many database backups you have; this will do them all.

use master;
set nocount on

declare @create_restore_logic varchar(max) set @create_restore_logic = '' select @create_restore_logic = @create_restore_logic + '
use master;
set nocount on
go
declare @database_name varchar (255) declare @backup_file_name varchar (255) set @database_name = ''' + replace(name, '''', '''''') + '''
set @backup_file_name = ''\\MyNetworkShareLocation\' + replace(name, '''', '') + '.bak'' 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 = (select ''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

select/**/ -- replace this line with: exec
(
@restore_line0
+ @restore_line1
+ @restore_line2
+ @move_files
+ @stats
)
go
'
from sys.databases where name not in ('master', 'model', 'tempdb', 'msdb') order by name asc

select (@create_restore_logic) for xml path (''), type 

Notice the last line “for xml path”… This will give you another tab ( xml tab ) in management studio so you can see ALL the output in it’s entirety. If satisfactory stimply change the laste ‘select’ to an ‘exec’, or you can paste the entire xml output into another management studio window, and babysit the whole process.

Hope it’s helpful.

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