Summary
This
article explains how to create an automated backup of VariTable 4 SQL Server
2005 (and higher) databases.
More Information
(1) Create a new folder ‘c:\varitable db backup’.
(2) Create a new file backupdb.sql in the VariTable installation folder and
past the following code in the file.
------------------------------------------------------------------------------------------------------------------------------------------------------------
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')
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 = datename(dw, getdate()) + ' - ' +
replace(replace(@DBName,':','_'),'\','_')
exec ('BACKUP DATABASE [' +
@DBName + '] TO DISK = N''c:\varitable db backup' +
@DBFileName + ''' WITH NOFORMAT,
INIT, NAME = N''' +
@DBName + '-Full Database Backup'',
SKIP, NOREWIND, NOUNLOAD, STATS = 100')
FETCH NEXT FROM DATABASES_CURSOR INTO
@DBName
END
CLOSE
DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR
------------------------------------------------------------------------------------------------------------------------------------------------------------
(3) Create
a new file backupdb.cmd in the VariTable installation folder and past the
following line in script file. Be sure to change [SERVERNAME\INSTANCE_NAME] and [PASSWORD] as defined
for the SQL Server and user sa.
-----------------------------------------------------------------------------------------------------------------------------------------------------------
del “c:\varitable db backup\*”
/Q
osql -S [SERVERNAME\INSTANCE_NAME]
-U sa –P [PASSWORD] -i backupdb.sql
-o backupdb.log
------------------------------------------------------------------------------------------------------------------------------------------------------------
(4) Start backupdb.cmd
to test the backup and check if backup files are created in folder c:\varitable
db backup.
(5) Add backupdb.cmd
to be run as a task once a day at 4am with administrator user rights.