Search This Blog

Loading...

2009-06-30

backup sql server 2005 / 2008 databases



DECLARE @DBName varchar(255)
 
DECLARE @DATABASES_Fetch int
 
DECLARE DATABASES_CURSOR CURSOR FOR
select
DATABASE_NAME   = db_name(s_mf.database_id)
from
sys.master_files s_mf
where
-- ONLINE
s_mf.state = 0 
 
-- Only look at databases to which we have access
and has_dbaccess(db_name(s_mf.database_id)) = 1 
 
-- Not master, tempdb or model
and db_name(s_mf.database_id) not in ('Master','tempdb','model') and 
-- UNCOMMENT THIS ONE TO BACKUP ONLY THE ocms_dev db_name(s_mf.database_id) in ('ocms_dev' )
-- ALTERNATIVELY SPECIFY ONLY ONE DB , uncomment this line
--  and db_name(s_mf.database_id) in ('ocms_dev')
group by s_mf.database_id
order by 1
 
OPEN DATABASES_CURSOR
 
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
 
WHILE @@FETCH_STATUS = 0
BEGIN
declare @DBFileName varchar(256)    
set @DBFileName = @DbName + '_' + replace(convert(varchar, getdate(), 112), '-', '.') + '.bak'
--REMEMBER TO PUT HERE THE TRAILING \ FOR THE DIRECTORY !!!
declare @sqlToRun varchar(4000)
set @sqlToRun = 'BACKUP DATABASE [' + @DBName + '] TO  DISK = N''D:\GenApp\DATA\Backups\' + 
@DBFileName + ''' WITH NOFORMAT, INIT,  NAME = N''' + 
@DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 100'
-- debug print @sqlToRun
-- debug return 
exec (@sqlToRun ) 
 
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END
 
CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR

Labels

perl (41) Cheat Sheet (25) how-to (24) windows (13) oracle (12) sql server 2008 (12) Unix (11) linux (11) sql (11) cmd windows batch (10) mssql (10) cmd (9) script (9) textpad (9) netezza (8) sql server 2005 (8) cygwin (7) meta data mssql (7) metadata (7) code generation (6) Informatica (5) cheatsheet (5) energy (5) utilities (5) bash (4) excel (4) future (4) generic (4) html (4) perl modules (4) programs (4) settings (4) sh (4) tsql (4) поуки (4) принципи (4) Focus Fusion (3) Solaris (3) cool programs (3) development (3) economy (3) example (3) freeware (3) fusion (3) morphus (3) mssql 2005 (3) nuclear (3) nz (3) parse (3) sftp (3) shortcuts (3) sofware development (3) source (3) sqlplus (3) table (3) vim (3) .Net (2) C# (2) China (2) GUI (2) Google (2) GoogleCL (2) Solaris Unix (2) ascii (2) awk (2) batch (2) cas (2) code2html (2) columns (2) configuration (2) conversion (2) duplicates (2) export (2) file (2) free programs (2) logging (2) mssql 2008 (2) mysql (2) next big future (2) nsis (2) nzsql (2) ora (2) prediction (2) publish (2) python (2) release management (2) report (2) security (2) single-click (2) sqlserver 2005 (2) sqlserver 2008 (2) src (2) ssh (2) template (2) tools (2) vba (2) video (2) xlt (2) xml (2) youtube videos (2) *nix (1) .vimrc (1) BSD license (1) Bulgaria (1) Dallas (1) Database role (1) Dense plasma focus (1) Deployment (1) ERP (1) ExcelToHtml (1) GD (1) GDP (1) HP-UX (1) Hosting (1) INC (1) IT general (1) ITIL management bullshit-management (1) IZarc (1) Java Web Start (1) Khan Academy (1) Linux Unix rpm cpio build install configure (1) Linux git source build .configure make (1) ListBox (1) MIT HYDROGEN VIRUS (1) OO (1) Obama (1) PowerShell (1) Run-time (1) SDL (1) SIWA (1) SOX (1) Services (1) Stacks (1) SubSonic (1) TED (1) abstractions (1) ansible hosts linux bash (1) ansible linux deployment how-to (1) ansible yum pip python (1) apache (1) apache 2.2 (1) application life cycle (1) architecture (1) archive (1) arguments (1) avatar (1) backup (1) bash Linux open-ssh ssh ssh_server ssh_client public-private key authentication (1) bash stub (1) bin (1) biofuels (1) biology (1) books (1) browser (1) bubblesort (1) bugs (1) build (1) byte (1) cas_sql_dev (1) chennai (1) class (1) claut (1) cmdow (1) code generation sqlserver (1) command (1) command line (1) conf (1) confluence (1) console (1) convert (1) cool programs windows free freeware (1) copy-paste (1) csv (1) ctags (1) current local time (1) cygwin X11 port-forwarding mintty xclock Linux Unix X (1) cygwin bash how-to tips_n_tricks (1) cygwin conf how-to (1) data (1) data types (1) debt (1) diagram (1) digital (1) disk (1) disk space (1) documentation (1) dos (1) dubai (1) e-cars (1) electric cars (1) electricity (1) emulate (1) errors (1) excel shortcuts (1) exponents (1) export workflow (1) extract (1) fast export (1) fexp (1) file extension (1) file permissions (1) findtag (1) firewall (1) for loop (1) functions (1) fusion research (1) german (1) google cli (1) google code (1) google command line interface (1) gpg (1) head (1) helsinki (1) history (1) hop or flop (1) host-independant (1) how-to Windows cmd time date datetime (1) ideas (1) image (1) informatica function conversion fixed width (1) informatica repo sql workflows sessions file source dir (1) informatica sql repository (1) install (1) java (1) jump to (1) keyboard shortcuts (1) ksh (1) level (1) linkedin (1) linux bash ansible hosts (1) linux bash commands (1) linux bash how-to shell expansion (1) linux bash user accounts password (1) linux bash xargs space (1) linux cheat-sheet (1) linux cheatsheet cheat-sheet revised how-to (1) linux how-to non-root vim (1) linux ssh hosts parallel subshell bash oneliner (1) london (1) make (1) me (1) metacolumn (1) metadata functions (1) metaphonre (1) method (1) model (1) movie (1) multithreaded (1) mysql how-to table datatypes (1) n900 (1) nano (1) neteza (1) netezza bash linux nps (1) netezza nps (1) netezza nps nzsql (1) netezza nz Linux bash (1) netezza nz bash linux (1) netezza nz nzsql sql (1) netezza nzsql database db sizes (1) non-password (1) nord pol (1) nps backup nzsql schema (1) number formatting (1) nz db size (1) nz netezza cheat sheet (1) nzsql date timestamp compare bigint to_date to_char now (1) on-lier (1) one-liners (1) one-to-many (1) oneliners (1) open (1) open source (1) openrowset (1) outlook (1) parser (1) password (1) paths (1) perl @INC compile-time run-time (1) perl disk usage administration Linux Unix (1) perl modules configuration management (1) permissions (1) php (1) picasa (1) platform (1) powerShell cmd cygwin mintty.exe terminal (1) ppm (1) predictions (1) prices (1) principles (1) productivity (1) project (1) prompt (1) proxy account (1) public private key (1) publishing (1) putty (1) qt (1) read file (1) registry (1) relationship (1) repository (1) rm (1) scp (1) scripts (1) scsi (1) sed (1) sendEmail (1) sh stub (1) shortcuts Windows sql developer Oracle (1) silicon (1) smtp (1) software procurement (1) sofware (1) sort (1) sql script (1) sql_dev (1) sqlcmd (1) sqlite (1) sqlite3 (1) sshd (1) stackoverflow (1) stored procedure (1) stub (1) stupidity (1) subroutines (1) svn (1) sysinternals (1) tail (1) tar (1) temp table (1) templates (1) teradata (1) terminal (1) test (1) testing (1) theory (1) thorium (1) time (1) tip (1) title (1) tool (1) umask Linux Unix bash file permissions chmod (1) url (1) urls (1) user (1) utility (1) utils (1) vb (1) vim perl regex bash search for string (1) vim-cheat-sheet vim cheat-sheet (1) vimeo (1) warsaw (1) wiki (1) wikipedia (1) window (1) windows 7 (1) windows programs (1) windows reinstall (1) wisdoms (1) workflow (1) worth-reading (1) wrapper (1) xp_cmdshell (1) xslt (1) youtube (1)

Blog Archive

Translate with Google Translate

My Blog List

Loading...