Script to Restore - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Script to Restore

Author

Topic

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2003-07-28 : 21:04:01

Here goes a script I use to restore my databases

1) Place the Backup files all in the same dir;
2) Update the line set @dirCont=, to point to your dir;
3) If you're going to apply logs or diferential, then drop the RECOVERY from the set @Script;
4) The script will generate the restore strings for you.

Remarks:It uses a dumb check for dir and file existance. You can change it. I'm too lazy to make it now, because its working good.

Also, it uses... NR dont stone me... CURSORS (shame on me!!!!!).

And we have a pattern in which all db backup file name uses "_db_" and log uses "_trn_"

/*Script To Restore with Recovery and move

Author - Sergio Monteiro*/
set nocount on

declare @yes tinyint
declare @File_name nvarchar(260)
declare @csr Cursor
declare @command nvarchar(256)
declare @Script nvarchar(1000)
declare @cFile_Name nvarchar(260)
declare @dirConst nvarchar(60)
declare @strConst nvarchar(80)

create table #Files
(
coutput nvarchar(2000)
)

create table #Dirs
(
coutput nvarchar(2000)
)

set @dirConst= 'G:\Restore_Intranet\'
set @strConst= 'dir ' + @dirConst + '*.bak'
/*--------------------------------------------------------------
Obtem do sistema operacional todos os arquivos de backup
utilizando xp_cmdshell (Permissao necessaria)
---------------------------------------------------------------*/
insert into #Files execute xp_cmdshell @strConst
/*-------------------------------------------------------------
retira o cabecalho do MSDOS
-------------------------------------------------------------*/
delete #files from (Select top 5 * from #Files) as b where #files.coutput=b.coutput
/*-------------------------------------------------------------
precisamos apenas do nome do arquivo, de modo que
retiramos as informacoes desnecessarias
---------------------------------------------------------------*/
update #files set coutput = rtrim(ltrim(right(coutput, Charindex(char(32), reverse(rtrim(coutput))))))

set @csr = Cursor static for
select coutput
from #Files
where coutput is not null
and (coutput not like '%bytes%'
and coutput not like '%free%')
order by 1

open @csr

fetch next from @csr into @File_name
while (@@Fetch_status=0)
begin
set @cFile_Name = @File_name
set @File_name = left(@file_name, charindex('_db', @file_name)-1)

/*----------------------------------------
Verifica se o banco de dados
ja existe.
-----------------------------------------*/

-- if exists(select name from sysdatabases where name = @file_name)
-- set @file_name = @file_name + '_renamed'

/*----------------------------------------
Verifica se os diretorios de destino
ja existem. Se nao, cria
Nota - Pode-se usar a procedure
xp_fileexist no lugar
do processo abaixo.
------------------------------------------*/
--Dados
truncate table #dirs
set @command = 'dir F:\' + @file_name + '\*.*'
insert into #dirs execute xp_cmdshell @command
select @yes = count(*)
from #dirs
where coutput like '%The system cannot find%'

if @yes = 1
begin
set @command = 'mkdir F:\' + @File_name + '\'
execute xp_cmdshell @command
end
--Transaction Log
truncate table #dirs
set @command = 'dir H:\' + @File_Name + '\*.*'
insert into #dirs execute xp_cmdshell @command
select @yes = count(*)
from #dirs
where coutput like '%The system cannot find%'

if @yes = 1
begin
set @command = 'mkdir H:\' + @File_name + '\'
execute xp_cmdshell @command
end
/*************************************************
Monta o script de Restore para o DB
*************************************************/

set @Script = 'RESTORE DATABASE ' + @file_name +
' FROM DISK=''' + @dirConst + @cFILE_NAME + '''' + char(13) +
' WITH RECOVERY,' + char(13) +
' MOVE ''' + @file_name + '_data'' to ''F:\'
+ @file_name + '\' + @File_name + '_data.mdf'',' + char(13) +
' MOVE ''' + @file_name + '_log'' to ''H:\'
+ @file_name + '\' + @File_name + '_log.ldf'',' + char(13) +
' REPLACE'

select @Script
--execute @Script
fetch next from @csr into @File_name

end

close @csr
deallocate @csr

drop table #Files
drop table #dirs

set nocount off

Sérgio

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources