Thursday, February 26, 2009

Backing up your SQL databases on the fly

step 1. create a stored procedure in your master database using the tscript below:

1.1 Dont forget to edit the script and specify the destination folder of your backup routine (default is E:\SQLData\Backup\).

1.2 create an audit table in your master database such that you can monitor which database has been included in your backup at when (see the sample script below).

GO
CREATE TABLE [dbo].[aITBSU_DatabaseBackupLog](
[BackUpId] [int] IDENTITY(1,1) NOT NULL,
[databasename] [varchar](200) NULL,
[databaseid] [int] NULL,
[createdate] [datetime] NULL,
[path] [varchar](500) NULL,
[lastdate] [datetime] NULL,
[lastuserid] [int] NULL,
CONSTRAINT [PK_aITBSU_DatabaseBackupLog] PRIMARY KEY CLUSTERED
(
[BackUpId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF



-- code starts here
CREATE procedure [dbo].[aAdminBackupAllDatabase]
as
begin

declare @dbName varchar(200)
declare @dbid int
declare @dbcreatedate datetime

declare cur cursor fast_forward local read_only for
select [name],database_id,create_date from sys.databases where [name] not in('tempdb','dsu_administrationAudit','dsu_log')
open cur
fetch next from cur into @dbName,@dbid,@dbcreatedate
while @@fetch_Status=0
begin

declare @dtFileName varchar(50)
declare @dtPath varchar(50)


set @dtFileName= @dbName + '_backup_' + dbo.tGetPadZero(2,datepart(dd,getdate())) + dbo.tGetPadZero(2,datepart(mm,getdate())) + dbo.tGetPadZero(4,datepart(yyyy,getdate())) + dbo.tGetPadZero(2,datepart(hh,getdate())) + dbo.tGetPadZero(2,datepart(mi,getdate())) + dbo.tGetPadZero(2,datepart(ss,getdate())) + dbo.tGetPadZero(4,datepart(ms,getdate()))
set @dtPath='E:\SQLData\Backup\'

declare @nSQL nvarchar(1000)

-- set @nSQL='BACKUP DATABASE [' + @dbName + '] TO DISK = N' + '''' + @dtPath + @dtFilename + '.bak ' + '''' + ' WITH NOFORMAT, NOINIT, NAME = N' + '''' + @dtFileName + dbo.tGetPadZero(4,datepart(ms,getdate())) + '''' + ', SKIP, REWIND, NOUNLOAD, STATS = 10 '

set @nSQL='BACKUP DATABASE [' + @dbName + '] TO DISK = N' + '''' + @dtPath + @dtFilename + '.bak ' + '''' + ' WITH NOFORMAT, NOINIT, NAME = N' + '''' + @dtFileName + dbo.tGetPadZero(4,datepart(ms,getdate())) + '''' + ', SKIP, NOREWIND, NOUNLOAD, STATS = 10'


exec sp_executesql @nSQL

insert into [master].[dbo].[aITBSU_DatabaseBackupLog]([databasename]
,[databaseid]
,[createdate]
,[path]
,[lastdate]
,[lastuserid])

values(@dbName,@dbid,@dbcreatedate,@dtPath,getdate(),123456)



fetch next from cur into @dbName,@dbid,@dbcreatedate
end
close cur
deallocate cur

end
-- code ends here
step 2. create a server-side backup routine (through sql jobs) using your SQL agent and run it on a pre-defined schedule (i.e. daily every 3hours).

step 3. configure your backup software to point to the above backup folder and put the files in a separate machine, so you can restore it safely anywhere, anytime you want.