How to backup multiple SQL Server Databases in SQL Server
Example:
You need to take backup of multiple databases from SQL Server Instance and be created with date_time.
Below is a script you can use(Cursor);
USE MASTER
GO
DECLARE @BackupPath varchar(100)
--Provide the backup path
SET @BackupPath = 'C:\Backup\'
DECLARE @DBName AS varchar(200)
DECLARE Cur CURSOR FOR
--Change the select query for the DBs you like to backup
SELECT name
FROM sys.databases
WHERE database_id >4 --(i.e excluding the following 'master, tempdb, model and msdb')
OPEN Cur
FETCH NEXT FROM Cur INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL varchar(max) = NULL
DECLARE @DBNamewithDateTime varchar(128) = NULL
SET @DBNamewithDateTime = @DBName + '_' + REPLACE(CAST(GETDATE()
AS date), '-', '') + '_' + REPLACE(CAST(CAST(GETDATE() AS time)
AS char(8)), ':', '')
SET @SQL = 'BACKUP DATABASE [' + @DBName + '] TO DISK = N''' +
@BackupPath + '' + @DBNamewithDateTime + '.bak''
WITH NOFORMAT, NOINIT, NAME = N''' + @DBNamewithDateTime
+ '-Full Database Backup'',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'
PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM Cur INTO @DBName
END
CLOSE Cur
DEALLOCATE Cur
You need to take backup of multiple databases from SQL Server Instance and be created with date_time.
Below is a script you can use(Cursor);
USE MASTER
GO
DECLARE @BackupPath varchar(100)
--Provide the backup path
SET @BackupPath = 'C:\Backup\'
DECLARE @DBName AS varchar(200)
DECLARE Cur CURSOR FOR
--Change the select query for the DBs you like to backup
SELECT name
FROM sys.databases
WHERE database_id >4 --(i.e excluding the following 'master, tempdb, model and msdb')
OPEN Cur
FETCH NEXT FROM Cur INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL varchar(max) = NULL
DECLARE @DBNamewithDateTime varchar(128) = NULL
SET @DBNamewithDateTime = @DBName + '_' + REPLACE(CAST(GETDATE()
AS date), '-', '') + '_' + REPLACE(CAST(CAST(GETDATE() AS time)
AS char(8)), ':', '')
SET @SQL = 'BACKUP DATABASE [' + @DBName + '] TO DISK = N''' +
@BackupPath + '' + @DBNamewithDateTime + '.bak''
WITH NOFORMAT, NOINIT, NAME = N''' + @DBNamewithDateTime
+ '-Full Database Backup'',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'
PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM Cur INTO @DBName
END
CLOSE Cur
DEALLOCATE Cur
I am thankful to you for this article because you are providing such good information as I see. It gives me good information and helps me. Share more articles like this. 10GBPS Dedicated Server
ReplyDeleteIt's a nice article, Which you have shared here about the . Your article is very informative and I really liked the way you expressed your views in this post. Thank you.python coding course
ReplyDeleteI definitely enjoy this. It is a great website and has a nice article. I want to thank you. Good job! You guys do a great blog, Keep up the good work . CMA Dubai
ReplyDeleteWe appreciate you for providing us such informative content. This content is really helpful. Keep it up, we hope to see more content like this . If anyone interested in learning CMA course please Enroll with Learners Point Academy, in Dubai.
ReplyDeleteThanks for sharing this script! Adding the database name along with the timestamp makes backup management much easier, especially when handling multiple SQL Server databases. This is a practical solution for DBAs and SQL Server administrators.
ReplyDeleteFor anyone looking to build broader data and ERP skills alongside SQL Server, I found these learning resources useful:
Power BI Course: https://www.edoxi.com/ms-power-bi-training-in-dubai
SAP Courses: https://www.edoxi.com/sap-courses
Understanding SQL, Power BI, and SAP together can be valuable for professionals working with enterprise reporting, business intelligence, and data-driven decision-making