SQL-Transact Backup all Databases In a MS SQL Server

The Problem

My client wants to backup all the databases in their MS SQL Server Server – 1,000 large databases – to a shared network drive.

I want to run a script, get coffee and come back to check when the backup is completed.

Solution

Created a script to backup all the databases on the server

Before you start anything, make sure the following conditions are met:

  • Network path exists
  • Map the path to a drive
  • The Network path is reachable from the server
  • The path has permissions to save files
  • There is enough space on the path to save all the back up files

Now, the script:

use [master]

DECLARE @databaseName VARCHAR(100) -- database name
DECLARE @backupPath VARCHAR(100) -- path for backup location
DECLARE @fileName VARCHAR(100) -- filename used for backup files


-- specify database backup directory

SET @backupPath = 'K:\BackupFolder\' 

--read database names on the server
DECLARE db_cursor CURSOR for
SELECT name
FROM master.dbo.sysdatabases
WHERE name not IN ('master','model','msdb','tempdb' )
order by name


--backup process
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @databaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @backupPath + @databaseName + '_' + @datestamp + '.bak'
BACKUP DATABASE @databaseName TO DISK = @fileName WITH INIT, CHECKSUM;
FETCH NEXT from db_cursor INTO @databaseName
END
CLOSE db_cursor
DEALLOCATE db_cursor


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s