Sql-server – Script to backup all databases

automationbackupscriptingsql server

I'm working on automating backups of SQL Server databases on SQL 2008 R2 Express. These backups are initiated daily by the Windows Task Scheduler, which execute a batch file, which in turns executes a SQL Script. I cannot use the SQL Agent because this is the express edition.

Currently, I am writing a script which backs up each database one by one. However, this is quite high maintenance – every time a database is added, dropped, or renamed, I have to modify this script to reflect these changes. I would instead like to write one single SQL script which backs up all databases at once, without the need to explicitly backup each database one by one.

This is currently the script used to backup a single database:

Print 'Backing up MyDatabase...'
BACKUP DATABASE MyDatabase
TO DISK = 'F:\Backups\SQL Databases\MyDatabase.bak'
   WITH FORMAT,
      NAME = 'Full Backup of MyDatabase';
GO

How can I go about enumerating a list of all databases and performing a loop to do this backup to all databases?

Just some additional notes of the backup process:

Scheduled Task in Windows executes a batch file at 9:00 PM daily
Batch file calls OSQL which executes the SQL script
The OSQL outputs a text file saving the results
There are about 30 databases to be backed up
Some of the databases are over 2GB (so it will take some time)
A third-party backup service obtains the resulting backup files and pushes them off-site at midnight every night

Best Answer

I believe you can refer to ola.hallengren. SQL server Backup scripts.

As stated on how to use for SQL server Express:

SQL Server Express has no SQL Server Agent. Therefore, the execution of the stored procedures must be scheduled by using cmd files and Windows Scheduled Tasks. Follow these steps.

Download MaintenanceSolution.sql.

Execute MaintenanceSolution.sql. This script creates the stored procedures that you need.

Create cmd files to execute the stored procedures; for example: sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt

In Windows Scheduled Tasks, create tasks to call the cmd files.

Schedule the tasks.

Start the tasks and verify that they are completing successfully.

Now you can modify this easily as per you're needs.

Note: In MaintenanceSolution, you can separately only download SQL server Backup scripts individually.

Related Solutions

Sql-server – Scheduled backup task doesn’t always backup all databases despite always saying job successful

I would add TRY/CATCH blocks to handle errors and log them. The DB could be in single user, being restored or whatever.

Without this, errors can abort in a way that no errors are logged (statement, batch, scope, connection etc)

With TRY/CATCH then everything except for compile or connection aborting errors are logged? but I doubt this is the case.

I'd also use sys.databases which replaces sysdatabases and read more flags:

-- declares etc

BEGIN TRY

    DECLARE db_cursor CURSOR FOR  
    SELECT name, state, user_access
    FROM sys.databases 
    WHERE name NOT IN ('tempdb')

    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @name, @state, @user_access

    WHILE @@FETCH_STATUS = 0   
    BEGIN   

        SET @fileName = @path + @name + '.bak'
        IF @state = 0 AND user_access = 0
        BEGIN
            BEGIN TRY
                BACKUP DATABASE @name TO DISK = @fileName  WITH INIT
            END TRY
            BEGIN CATCH
                -- log but do not rethrow so loop continues
            END CATCH
            WAITFOR DELAY '00:00:20'
        END
        ELSE
           --log user and/or state issues

        FETCH NEXT FROM db_cursor INTO @name 
    END   

    CLOSE db_cursor   
    DEALLOCATE db_cursor

END TRY
BEGIN CATCH
  -- some useful stuff here
END CATCH

Sql-server – Taking backup of all the databases in SQL Server

Note: First create a folder on D: drive. (e.g. D:\User_DataBackup\)

Step 1: Create a procedure that are given below.

Create PROCEDURE [dbo].[UserDataBaseBackUp]
AS

BEGIN
SET NOCOUNT ON;

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 = 'D:\User_DataBackup\' -- as same as your created folder' 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),104)
DECLARE db_cursor CURSOR FOR
  SELECT name
  FROM MASTER.dbo.sysdatabases
  WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
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
  FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
END

Step 2: Execute the above procedure.

 EXEC [UserDataBaseBackUp]

You can also schedule this procedure. This procedure is tested hopefully it will help.

Best Answer

Related Solutions

Sql-server – Scheduled backup task doesn’t always backup all databases despite always saying job successful

Sql-server – Taking backup of all the databases in SQL Server

Related Question