T-SQL to backup all user SQL Server databases at a time
49 views 21:42 0 Comments27 June 2023
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.
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.
DECLARE @DatabaseName sysnameDECLARE @BackupPath nvarchar(500)DECLARE @SQL nvarchar(max)-- Set the backup pathSET @BackupPath ='C:\SQL_DATA\Backup\'-- Replace with your desired backup path-- Create a cursor to loop through all user databasesDECLARE db_cursor CURSOR FORSELECTnameFROM sys.databasesWHERE database_id >4-- Exclude system databasesOPEN db_cursorFETCHNEXTFROM db_cursor INTO @DatabaseNameWHILE @@FETCH_STATUS =0BEGIN-- Generate the dynamic SQL statement for each databaseDECLARE @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 commandEXEC sp_executesql @SQLFETCHNEXTFROM db_cursor INTO @DatabaseNameENDCLOSE db_cursorDEALLOCATE db_cursor
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 locationDECLARE @BackupLocation NVARCHAR(500) ='C:\SQL_DATA\Backup\';-- Temporary variablesDECLARE @DBName NVARCHAR(128);DECLARE @SQL NVARCHAR(MAX);DECLARE @BackupFileName NVARCHAR(500);-- Cursor to iterate through user databasesDECLARE BackupCursor CURSOR FORSELECTnameFROM sys.databasesWHERE database_id >4ANDstate=0; -- Exclude system databases and offline databases-- Open the cursorOPEN BackupCursor;-- Fetch the first database nameFETCHNEXTFROM BackupCursor INTO @DBName;-- Iterate through each databaseWHILE @@FETCH_STATUS =0BEGIN-- Build the backup file nameSET @BackupFileName = @BackupLocation + @DBName +'_'+REPLACE(CONVERT(NVARCHAR(10), GETDATE(), 120), '-', '_') +'_'+REPLACE(CONVERT(NVARCHAR(8), GETDATE(), 108), ':', '') +'.trn';-- Build the backup commandSET @SQL ='BACKUP LOG '+QUOTENAME(@DBName) +' TO DISK = N'''+ @BackupFileName +''''+'WITH COMPRESSION, INIT, STATS = 10, FORMAT, '+'NAME = N'''+ @DBName +' - Transaction Log Backup'';';-- Execute the backup commandEXEC sp_executesql @SQL;-- Fetch the next database nameFETCHNEXTFROM BackupCursor INTO @DBName;END-- Close the cursorCLOSE BackupCursor;DEALLOCATE BackupCursor;
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.