SQL Script to Dump SQL Databases

[Paul_Grimshaw]

This is a SQL Script that I use on my test system to dump out all of my EV SQL databases to a BAK file and truncate the transaction logs. The only thing that needs to be changed is the location where you would like the BAK files to be created.:-


-- automated Dump procedure
-- dumps all databases in .bak files

use EnterpriseVaultDirectory
GO

Declare @BackupDir varchar(255)

-- CHANGE THIS IF NECESSARY

Set @BackupDir='D:\BACKUPEV\'

-- Use a Cursor to loop thru the Directory
-- in order to find all DB's

DECLARE dbcursor CURSOR FOR
select DatabaseName
from dbo.VaultStoreEntry

OPEN dbcursor

declare @dbname varchar(255)
declare @s varchar(255)

-- Perform the first fetch.
FETCH NEXT FROM dbcursor into @dbname

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
print ''
print 'Backing up : ' + isnull(@dbname,'noname')

-- create a Backup device called 'temp' with type of disk
-- and Filename DBNAME.bak

set @s = @BackupDir + isnull(@dbname,'noname') + '.bak'
EXEC sp_addumpdevice 'disk', 'xtemp', @s

-- Truncate the logfile
print ''
print 'Truncating log'
BACKUP LOG @dbname WITH TRUNCATE_ONLY
print 'Truncated log'
print ''
-- Dump the DB into that file

BACKUP DATABASE @dbname
TO xtemp
WITH DESCRIPTION = 'Enterprise Vault Backup', INIT


Usual legal disclaimer stuff that if you are going to try this script on a production system then please get it verified and tested on a suitable testing platform. ETC ETC :)