Pastable version of Backup All MS SQL Server Databases

<div style="overflow:auto;border:solid 1px #ccc;background:#000;color:#F8F8F8">
          <div class="section">
            <span style="margin:0;padding:4px 6px;font-size:12px;float:right;">SQL Statement</span>
            <pre style="float:left;margin:0 10px;border-right:0;color:#666;">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</pre>
            <pre class="sunburst">Use Master

Declare @DatabaseName sysname
Declare @SQLCommand <span style="color:#99CF50;">varchar</span>(<span style="color:#3387CC;">1024</span>)
Declare curDBName Cursor For
<span style="color:#E28964;">Select</span> [Name] <span style="color:#E28964;">From</span> Master..Sysdatabases
<span style="color:#E28964;">Where</span> [Name] Not <span style="color:#E28964;">In</span> (<span style="color:#65B042;"><span style="color:#65B042;">'</span>tempdb'</span>)

Open curDBName
Fetch curDBName Into @DatabaseName

While (@@fetch_status = <span style="color:#3387CC;">0</span>)

<span style="color:#E28964;">Begin</span>
        if databasepropertyex (@DatabaseName,<span style="color:#65B042;"><span style="color:#65B042;">'</span>Status'</span>) = <span style="color:#65B042;"><span style="color:#65B042;">'</span>online'</span>
        <span style="color:#E28964;">Begin</span>
                <span style="color:#E28964;">Select</span> @SQLCommand = <span style="color:#65B042;"><span style="color:#65B042;">'</span>Backup Database '</span> + @DatabaseName + 
                <span style="color:#65B042;"><span style="color:#65B042;">'</span> To Disk = '</span><span style="color:#65B042;"><span style="color:#65B042;">'</span>D:<span style="color:#DDF2A4;">\B</span>ackups<span style="color:#DDF2A4;">\D</span>atabases<span style="color:#DDF2A4;">\'</span> + @DatabaseName + <span style="color:#65B042;">'</span></span>.bak<span style="color:#65B042;"><span style="color:#65B042;">'</span>'</span> With Format<span style="color:#65B042;"><span style="color:#65B042;">'</span></span>
<span style="color:#65B042;">                execute (@SQLCommand)</span>
<span style="color:#65B042;">        End</span>
<span style="color:#65B042;">        Fetch curDBName Into @DatabaseName</span>
<span style="color:#65B042;">End</span>
<span style="color:#65B042;"></span>
<span style="color:#65B042;">Close curDBName</span>
<span style="color:#65B042;">Deallocate curDBName</span>
</pre>
          </div>
        </div>

<a href="http://www.refactormycode.com/codes/497-backup-all-ms-sql-server-databases" style="color:#fff" title="As seen on RefactorMyCode.com"><img alt="Small_logo" src="http://www.refactormycode.com/images/small_logo.gif" style="border:0" /></a>

counter free hit invisible