Serendipituos Code

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