You sometimes need to backup a complete instance including the user and the system databases.
Plus if you are working with a cluster with several instances, you could need to move the backups on a local drive.
The following script will help you to do that and will create a folder on the setted volume, with you instance name.
I also use the compression and the COPY_ONLY option that allow to backup databases no interfering with the production backup plans you may have (Translog LSN management)
---------------------------------------
-- DECLARE VARIABLES
---------------------------------------
DECLARE @dbname VARCHAR(50) -- database name
DECLARE @BackupPath VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @FinalBackupDirectory VARCHAR(250) -- used for file name
DECLARE @LOC INT
DECLARE @VAR VARCHAR(250)
DECLARE @FINAL VARCHAR(250)
DECLARE @Command VARCHAR(250)
DECLARE @InstanceName VARCHAR(250)
DECLARE @ReturnCode INT
DECLARE @BackupDrive VARCHAR(250)
DECLARE @BackupDirectory VARCHAR(250)
DECLARE @FullDirectory VARCHAR(250)
---------------------------------------
-- Variables definition
---------------------------------------
SET @BackupDrive = 'D'
SET @BackupDirectory = 'Backup'
SET @FullDirectory = 'Full'
-- The final name will look like
-- D:\Backup\InstanceName\DatabaseName\Full\DatabaseName.bak
---------------------------------------
-- Get the Date
---------------------------------------
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
---------------------------------------
-- ENABLE XP_CMDSHELL
---------------------------------------
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
---------------------------------------
-- Create cursor with all Databases
-- to backup
---------------------------------------
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('tempdb') -- Exclusions de bases
---------------------------------------
-- Create the final backup directory
---------------------------------------
SELECT @InstanceName = CONVERT(sysname, SERVERPROPERTY('instancename'));
IF @InstanceName is NULL
BEGIN
SET @InstanceName ='Default'
END
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
---------------------------------------
-- Build the complete directory
---------------------------------------
SET @FinalBackupDirectory = @BackupDrive +':\' + @BackupDirectory + '\' + @InstanceName + '\' + @dbname + '\' + @FullDirectory
---------------------------------------
-- Build the command to create
-- directory and execute it
---------------------------------------
BEGIN TRY
SET @Command = 'md ' + @FinalBackupDirectory
EXEC master.dbo.xp_cmdshell @Command;
END TRY
BEGIN CATCH
END CATCH
---------------------------------------
-- If the backup directory is already
-- created, erase the content
---------------------------------------
BEGIN TRY
SET @Command = 'del ' + @FinalBackupDirectory + '*.BAK'
EXEC master.dbo.xp_cmdshell @Command;
END TRY
BEGIN CATCH
END CATCH
---------------------------------------
-- Isolate the Database path
---------------------------------------
SET @LOC = 0
SELECT @VAR = REVERSE(physical_name) from sys.master_files WHERE database_id = DB_ID(@dbname)
AND UPPER(RIGHT(physical_name,3))='MDF';
SET @LOC = CHARINDEX('\', @VAR)
SET @VAR = REVERSE(@VAR)
SET @BackupPath = LEFT(@VAR,LEN(@VAR) - @LOC +1)
---------------------------------------
-- Create backup database name
---------------------------------------
SET @fileName = @BackupPath + @dbname + '_' + @fileDate + '.BAK'
---------------------------------------
-- Current Database backup
---------------------------------------
BACKUP DATABASE @dbname TO DISK = @fileName WITH COMPRESSION, COPY_ONLY
---------------------------------------
-- Current Database Checkup
---------------------------------------
SET @Command = 'RESTORE VERIFYONLY FROM DISK = N''' + @fileName + ''''
EXECUTE (@Command)
IF @@ERROR <> 0
RAISERROR('Error verifying SQL backup.', 16, 1) WITH LOG
---------------------------------------
-- Build the command to move
-- the backup and execute it
---------------------------------------
SET @Command = 'move ' + @fileName + ' ' + @FinalBackupDirectory
EXEC master.dbo.xp_cmdshell @Command;
---------------------------------------
-- Go to the next database
---------------------------------------
FETCH NEXT FROM db_cursor INTO @dbname
END
---------------------------------------
-- Deallocate Cursor
---------------------------------------
CLOSE db_cursor
DEALLOCATE db_cursor
---------------------------------------
-- DISABLE XP_CMDSHELL
---------------------------------------
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE