How To Backup All Databases On an Instance On SQL Server

dbtut November 19, 2018 MSSQL

You can backup all databases on an Instance with the help of the following script (except tempdb)

You should write the path you want to backup instead of “C:\Backup”.

DECLARE @name VARCHAR(50) -- Database Name
DECLARE @path VARCHAR(256) -- Backup Path 
DECLARE @fileName VARCHAR(256) -- Backup File Name
DECLARE @fileDate VARCHAR(20)  --Date to add to backup file name

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


-- filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)


DECLARE db_cursor CURSOR FOR 
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('tempdb')  -- exclude tempdb
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  WITH COPY_ONLY
       FETCH NEXT FROM db_cursor INTO @name  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

DECLARE @name VARCHAR(50) -- Database Name

DECLARE @path VARCHAR(256) -- Backup Path

DECLARE @fileName VARCHAR(256) -- Backup File Name

DECLARE @fileDate VARCHAR(20) --Date to add to backup file name

--database backup directory

SET @path = 'C:\Backup'

-- filename format

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('tempdb') -- exclude tempdb

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 WITH COPY_ONLY

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

How To Backup All Databases On an Instance On SQL Server

About dbtut

Leave a Reply Cancel reply