Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    11

    Unanswered: 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!.

  2. #2
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    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.
    Johan

  3. #3
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    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
    -bpd

  4. #4
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    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.
    -bpd

  5. #5
    Join Date
    Aug 2003
    Posts
    11
    Thanks for the advice.

    All suggestions taken on board.

  6. #6
    Join Date
    Sep 2003
    Posts
    32
    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

  7. #7
    Join Date
    Sep 2003
    Posts
    32
    nevermind that last question, it was too easy!

    Thanks
    Tommy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •