Best way to backup a database,

Hi,

Does anyone have a preference on how they back up their database?

Currently I do it via a SQL Server Agent Job, that runs some T-SQL to backup the system database, and backup the user databases and transaction logs (where appropriate).

I was thinking about moving this to a VBScript for the reason that it will allow me to (easily) write to a log and email the relevant people (i.e. if a backup succeeds or fails).

Question is, in SQL (T-SQL), is there an easy way to write to a text file, and send out an email? (does sendmail require outlook to be installed on the server?)

Thanks again!.You can set up your backups through Enterprise Manager. When you enable a schedule for the backup, the backup is added as a job. For that job you can setup notifications eg mail and adding results to the application log.
To send mails, sql server needs a mapi compliant mail program, which can be Outlook.|||I do my backups with a T-SQL script. You can send emails from the script using xp_sendmail. Why write a text file log? You can write to a database table instead, which can provide a lot more functionality for a logviewer GUI.

Instead of doing incremental backups of my larger databases (which get progressively larger), I write a complete backup everyday to a network disk that has seperate folders for each day. I also do a shrink and translog truncate before the backup runs. Here's my script for the backup step:

DECLARE @.day_of_week VARCHAR(15),
@.server_name VARCHAR(25),
@.db_location_string VARCHAR(128),
@.log_location_string VARCHAR(128),
@.database_name VARCHAR(128)

DECLARE database_cursor CURSOR FOR
SELECT [name] as DBNAME FROM sysdatabases
WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb')

SET @.day_of_week=DATENAME(dw, GETDATE())
SET @.server_name=@.@.SERVERNAME

OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @.database_name

WHILE @.@.FETCH_STATUS=0
BEGIN
SET @.db_location_string='\\myServer\SQL Backups\' + @.day_of_week + '\SQL\' + @.server_name + '\' + @.database_name + '.bak'
SET @.log_location_string='\\myServer\SQL Backups\' + @.day_of_week + '\SQL\' + @.server_name + '\' + @.database_name + '_log.bak'
BACKUP DATABASE @.database_name TO DISK = @.db_location_string WITH NOINIT , NOUNLOAD , NAME = @.database_name, NOSKIP , STATS = 10, NOFORMAT
--BACKUP LOG @.database_name TO DISK = @.log_location_string WITH NOINIT , NOUNLOAD , NAME = @.database_name, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM database_cursor INTO @.database_name
END
CLOSE database_cursor
DEALLOCATE database_cursor|||Oh, if you don;t have your server set up for SQL Mail, which is, frankly, a pain, you can add a VBScript step to your back job that sends mail using the CDONTS object.

Configure your backup step to on failure, go to the send mail step, otherwise skip it.|||Thanks for the advice.

All suggestions taken on board.|||bpdWork:

Thank you so much for sharing your sql script. I might be able to use that in a new backup plan I am working on. One question, however, and I know this is asking a lot. Do you have another script that will restore all these databases?

Thanks
Tom|||nevermind that last question, it was too easy!

Thanks
Tommy