Backup script 1

[lechuck]

Hi

I've created a litle backup database script that users that don't have the MS SQL Server Enterprise Manager could use.

The script looks like this

DECLARE @dbName varchar(100)
DECLARE @backupFolder varchar(200)
DECLARE @path varchar(250)
DECLARE @db varchar(150)

/***** CHANGE this information to suit your needs *****/
SET @dbName = 'myDatabase'
SET @backupFolder = 'c:\DB_Backup\'
/******************************************************/

SET @db = @dbName + '_bak'
SET @path = @backupFolder + @db + '.dat'
USE master
EXEC sp_addumpdevice 'disk', @db, @path

BACKUP DATABASE @dbName TO @db

The script is executet from DOS prompt with

osql -U sa -P  -i c:\pathToMyScriptFile.sql

The problem with this script is that the backup file will grow bigger and bigger (appends) each time the script is executed. Any tip why?
Some missing argument to the

BACKUP DATABASE

i guess...

I noticed that I could delete the row

EXEC sp_addumpdevice 'disk', @db, @path

after the first time the script hade executed.

 

[nigelrivett]

Look at the with init clause

see

http://www.nigelrivett.net/BackupAllDatabases.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Jamfool]

BACKUP DATABASE master TO DISK = N'C:\data\databases\MASTER(na).dat' WITH INIT, STATS = 25


use INIT which will write over the original. Also you could add stats, this will give you percentage completion e.g 25%,50% ...




Enclose posts in colours
colored text

 

[lechuck]

WITH INIT argument worked fine, thanks!