How to backup several MSSQL databases

Created:

2016-11-16 13:01:34 UTC

Modified:

2017-08-08 13:34:30 UTC

0

Was this article helpful?


Have more questions?

Submit a request

How to backup several MSSQL databases

Applicable to:

  • Plesk 12.0 for Linux
  • Plesk 12.5 for Windows

Question

There are several MSSQL databases on the server. Is it possible to backup them all automatically?

Answer

To perform that action, use the following instruction:

  1. Create a directory for the backups, for example C:\\Backup

  2. Connect to the MSSQL server via Management Studio using either Windows or SQL Server Authentication.

  3. Click New Query

  4. Copy and paste the following text:

    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

    -- specify database backup directory
    SET @path = 'C:\\Backup\\'

    -- specify filename format
    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') -- exclude these databases

    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

    Note: The variable @path specifies the path to the directory that was defined in step #1.

  5. Click Execute and all databases, except the system ones, will be backed up to the specified directory.

Have more questions? Submit a request
Please sign in to leave a comment.