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