0

Code snippets : SQL : Backup and FTP Database

-

When browsing repository of all scripts I did use in past, I found one which might be interesting. What this script does is:

  • Backups database to disk (name of database stored in @dbname, patch for backup stored in @archive_path, name of backup fine stored in @fname)
  • Compresses database backup using WinRAR (full command where to find WinRAR and how ot use it stored in @archive_cmd)
  • Generates set of commands for FTP client (series of xp_cmdshell commands redirecting output to F:\Backup\ftpcmds)
  • Uploads compressed backup to FTP server using command prompt FTP client command

I had that as small procedure executed automatically with SQL Server Agent. Script is for Microsoft SQL Server and I was running it on SQL Server 2000 and 2005. Here is code for that small procedure:

 

DECLARE @fname varchar(255)
DECLARE @fname_backup varchar(255)
DECLARE @fname_archive varchar(255)
DECLARE @archive_path varchar(255)
DECLARE @dbname varchar(255)
DECLARE @archive_cmd varchar(255)
DECLARE @put_cmd varchar(255)

SET @dbname = 'dbApplication'
SET @fname = @dbname + '_' + LTRIM(STR(DAY(GETDATE())))  + '.' + LTRIM(STR(MONTH(GETDATE()))) + '.' + LTRIM(STR(YEAR(GETDATE())))
SET @archive_path = 'F:\Backup\'
SET @fname_backup = @archive_path + @fname + '.BAK'
SET @fname_archive = @archive_path + @fname + '.RAR'
SET @archive_cmd = '"C:\Program Files\WinRAR\WinRAR.EXE" a ' + @fname_archive + ' ' + @fname_backup
SET @put_cmd = 'echo put ' + @fname_archive + '>> F:\Backup\ftpcmds'

PRINT @fname_backup
PRINT @fname_archive

BACKUP DATABASE @dbname TO DISK = @fname_backup WITH INIT

EXEC xp_cmdshell @archive_cmd
EXEC xp_cmdshell 'echo open 192.168.10.101> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo user dbupload>> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo password123>> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo cd DB_Backup>> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo bin>> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo ha>> F:\Backup\ftpcmds'
EXEC xp_cmdshell @put_cmd
EXEC xp_cmdshell 'echo bye>> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'ftp -d -i -n -s:F:\BACKUP\ftpcmds'

I hope that will be handy for someone to automate databsae backups and transfers.

0

Games : Screenshots : Skyrim – Visiting Sovngard

-

(more…)

0

First look : System Center Virtual Machine Manager 2012 installation screenshots

-

Installation screenshots from System Center Virtual Machine manager 2012 installation. I did installation in my virtual LAB environment as part of excercise with System Center 2012.

(more…)

0

Tips & Tricks : ShoreTel : Logon and Logoff extension

-

Quick procedure to logon or logoff phone extension to or from ShoreTel IP phone. Steps listed below will allow to bring your phone extension to phone located in remote site and then send back to home ShoreTel IP phone. Of course both phones have to be part of same ShoreTel infrastructure (have to be managed from same ShoreTel HQ server).

(more…)