Backup All SQL Server Databases

Read Time:34 Sec

I came across this script to backup all SQL Server databases:

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'E:BackupsDynamics' 

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

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','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 

 FETCH NEXT FROM db_cursor INTO @name
END 

CLOSE db_cursor
DEALLOCATE db_cursor

Author

Stewart Schatz

Career: Principal CNC Consultant for Syntax Systems Limited specializing Oracle JD Edwards EnterpriseOne and the technology that supports it. Side Hustle: Owner/Operator of E1Tips.com Location: Lancaster, PA USA What I like to do: Invest in Family, Explore Technology, Lead Teams, Share Knowledge/Experience, Hunt, Hike, etc.

stewart@9rains.com

http://stewartschatz.com

Average Rating

5 Star

4 Star

3 Star

2 Star

1 Star

(Add your review)

How to Print a Directory Structure Using PowerShell: Step-by-Step Guide

If you need to print out the directory structure and list the files within, this PowerShell script will help you...

Stewart Schatz

June 7, 2024June 14, 2024

Windows God Mode… What!?!

Windows God Mode is a hidden feature in the Windows operating system that allows users to access all of the system’s control panel options and settings in a single place.

Stewart Schatz

January 5, 2023October 23, 2023