SQL Server Query to Take Full backup of all the user database at one go.

SQL Query to Take Full backup of all the User Databases at one go.

(Image Source: Linkdin)

IF you are an SQL Server administrator and dealing with multiple database backups then this query will help you take all the user-created databases. In the Query, you need to change the destination path to where you want to keep the database on local storage.

- Open the SSMS, and connect the SQL server.

- Open SQL Query window editior and paste the query over there.

DECLARE @dbName	NVARCHAR (255); 
DECLARE @SQL NVARCHAR(4000); 
 
DECLARE DBBackupCur 
CURSOR FOR 
    SELECT name FROM sys.databases WITH (NOLOCK) 
    WHERE name NOT IN ('master','model','msdb','tempdb')  
    OPTION (RECOMPILE); 
 
OPEN DBBackupCur;     
FETCH NEXT FROM DBBackupCur INTO @dbName 
WHILE (@@fetch_status <> -1) 
BEGIN 
    IF (@@fetch_status <> -2) 
    BEGIN 
        SET @SQL = N'BACKUP DATABASE [' + @dbName + N'] TO DISK = ''E:\DBBackup\' + @dbName + N'.bak'''; 
        EXECUTE sp_executesql @SQL 
        PRINT N'Backup completed: ' + @dbName 
    END; 
    FETCH NEXT FROM DBBackupCur INTO @dbName; 
END; 
CLOSE DBBackupCur; 
DEALLOCATE DBBackupCur; 
GO

This Query will help you take backup of all the User databases except the System databases. 

Hola!!!! I hope this article helps you. Leave a Comment and don't forget to check our website.