Auto-generate a DOS full cold backup script



My OraFAQ Blog

Contact me

This SQL script creates a DOS batch file named copy_files.out. It does not back up tempfiles, since it's not necessary to back up temporary tablespaces. (Here are instructions on opening your database without its temporary tablespace.)

Set the location of %backup_dir% in copy_files.out and rename it copy_files.bat to use it.

set heading off
set pagesize 0
set echo off
set feedback off
set verify off
set linesize 500
col cmdstr for a500
spool copy_files.out
prompt REM Script to take full database backup
prompt REM generated by make_cold_backup_scr.sql
prompt REM does not back up tempfiles
prompt REM don't forget to fill in location of %backup_dir% below
prompt REM
prompt REM copyright 2003 N Roshak
prompt REM
prompt set oracle_home=c:\oracle\ora81
prompt set backup_dir=N:\NODRIVE
select distinct 'copy ' || leaf 
|| ' %backup_dir%' || substr(leaf,instr(leaf,'\',-1,1), length(leaf))
as  cmdstr
(select name as LEAF from v$datafile
select name from v$controlfile
select member from v$logfile)
spool off

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...