Instance databases backup

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)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
---------------------------------------
--  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