Post reply

backup all databases in SQL 2008

  • January 20, 2012 at 7:56 am

    #255214

    Hi,

    Need a script to compress and backup all the databases in sql 2008, except system databases.

    Please let me know if someone has it.

    Thanks.

  • January 20, 2012 at 8:27 am

    #1436543

    DECLARE @name VARCHAR(50) -- database name

    DECLARE @path VARCHAR(256) -- path for backup files

    DECLARE @fileName VARCHAR(256) -- filename for backup

    DECLARE @fileDate VARCHAR(20) -- used for file name

    SET @path = 'C:\Backup\'

    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

    DECLARE db_cursor CURSOR FOR

    SELECT name

    FROM master.dbo.sysdatabases

    WHERE name NOT IN ('master','model','msdb','tempdb')

    OPEN db_cursor

    FETCH NEXT FROM db_cursor INTO @name

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

    BACKUP DATABASE @name TO DISK = @fileName WITH COMPRESSION

    FETCH NEXT FROM db_cursor INTO @name

    END

    CLOSE db_cursor

    DEALLOCATE db_cursor

    Robert Murphy, Microsoft MCITP (Database Administrator 2008) and MCAD Certified

  • January 20, 2012 at 8:56 am

    #1436566

    exec sp_msforeachdb 'if db_id(''?'')>4 backup database [?] to disk = ''C:\?.bak'' with init, compression;'

  • January 20, 2012 at 9:06 am

    #1436576

    padhis (1/20/2012)

    exec sp_msforeachdb 'if db_id(''?'')>4 backup database [?] to disk = ''C:\?.bak'' with init, compression;'

    Does the same thing but isn't support by Microsoft 😛

    Robert Murphy, Microsoft MCITP (Database Administrator 2008) and MCAD Certified

  • January 20, 2012 at 9:15 am

    #1436591

    Thanks a lot all. It works great.

  • January 22, 2012 at 2:48 pm

    #1436922

    Ola Hallengren has a great script for that:

    http://ola.hallengren.com/sql-server-backup.html

    No need to re-invent the wheel.

    -- Gianluca Sartori

  • January 28, 2013 at 10:19 am

    #1581716

    Try SQL Backup Master, which can back up all non-system databases at once. Basic edition is free.

  • May 9, 2017 at 4:09 pm

    #1941905

    Robert Murphy UK1 - Friday, January 20, 2012 9:06 AM

    padhis (1/20/2012)

    exec sp_msforeachdb 'if db_id(''?'')>4 backup database [?] to disk = ''C:\?.bak'' with init, compression;'

    Does the same thing but isn't support by Microsoft 😛

    who cares!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply