T-SQL to backup all user SQL Server databases at a time

Introduction

In today’s world, data is very important. We need to make sure that our data is safe and secure. One way to do this is to back up our databases regularly. In this article, we will learn how to use T-SQL to create a simple and efficient way to back up all of our user databases at once.

Functionality

The T-SQL script presented in this article allows us to back up all of our user databases at once, except for system databases. The script uses a cursor and dynamic SQL to go through each database and generate a backup command. This dynamic approach means that any new databases that are added to the server will be included automatically, without us having to manually update the script, except the backup location path.

The script also includes an option to compress the backup files, which reduces their size and makes them more efficient to store. Compressing the backup files helps us save storage space without compromising the integrity of the backup.

Finally, the script includes a condition to generate backup file names in a specific format: “DBname_YYYY_MM_DD_HH_MM.bak”. This naming convention includes the database name followed by the current date and time, making it easy to identify and organize our backup files.

T-SQL

DECLARE @DatabaseName sysname
DECLARE @BackupPath nvarchar(500)
DECLARE @SQL nvarchar(max)

-- Set the backup path
SET @BackupPath = 'C:\SQL_DATA\Backup\' -- Replace with your desired backup path

-- Create a cursor to loop through all user databases
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE database_id > 4 -- Exclude system databases

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Generate the dynamic SQL statement for each database
    DECLARE @BackupFileName nvarchar(500)
    SET @BackupFileName = @BackupPath + @DatabaseName + '_' + REPLACE(CONVERT(nvarchar(16), GETDATE(), 120), ':', '_') + '.bak'
    SET @SQL = 'BACKUP DATABASE ' + QUOTENAME(@DatabaseName) + ' TO DISK = ''' + @BackupFileName + ''' WITH COMPRESSION'

    -- Execute the backup command
    EXEC sp_executesql @SQL

    FETCH NEXT FROM db_cursor INTO @DatabaseName
END

CLOSE db_cursor
DEALLOCATE db_cursor

SQL

To perform T-log compression backup for all user databases in SQL Server and specify the backup location, you can use the following T-SQL script:

-- Specify the backup location
DECLARE @BackupLocation NVARCHAR(500) = 'C:\SQL_DATA\Backup\';

-- Temporary variables
DECLARE @DBName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @BackupFileName NVARCHAR(500);

-- Cursor to iterate through user databases
DECLARE BackupCursor CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE database_id > 4 AND state = 0; -- Exclude system databases and offline databases

-- Open the cursor
OPEN BackupCursor;

-- Fetch the first database name
FETCH NEXT FROM BackupCursor INTO @DBName;

-- Iterate through each database
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Build the backup file name
    SET @BackupFileName = @BackupLocation + @DBName + '_' +
                          REPLACE(CONVERT(NVARCHAR(10), GETDATE(), 120), '-', '_') + '_' +
                          REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 108), ':', '') + '.trn';

    -- Build the backup command
    SET @SQL = 'BACKUP LOG ' + QUOTENAME(@DBName) + ' TO DISK = N''' + @BackupFileName + ''' ' +
               'WITH COMPRESSION, INIT, STATS = 10, FORMAT, ' +
               'NAME = N''' + @DBName + ' - Transaction Log Backup'';';

    -- Execute the backup command
    EXEC sp_executesql @SQL;

    -- Fetch the next database name
    FETCH NEXT FROM BackupCursor INTO @DBName;
END

-- Close the cursor
CLOSE BackupCursor;
DEALLOCATE BackupCursor;

SQL

Conclusion

Implementing an automated and efficient database backup strategy is essential for data protection and disaster recovery. By using T-SQL, we can create a simple solution to back up all of our user databases at once, while benefiting from features such as compression and organized file naming. This approach not only simplifies the backup process, but it also ensures that our backups are consistent and up-to-date, safeguarding our critical data.

I hope this article was helpful. Please let me know if you have any questions.