Friday, September 19, 2014

How to find the model name or serial number of a Windows server

Ok, ok...  Not necessarily tSQL, but definitely quite useful!  Today, for example, I was evaluating a customer's server, attempting to answer the question -- is the server adequate for the firm's enterprise reporting solution?  As-is, not even close.  But, I wanted to know the model of the machine, so that I could determine whether we could add more disk, and what our other resource limitations were.

Easy sneasy.  Open a command prompt on your server and type the following to return your computer model:

         wmic csproduct get name

Or, type in this at the command prompt to return the serial number for the server:
       
         wmic bios get serialnumber

Why is this helpful to you, as a DBA?  Well, with that information, you can get online and and look up the server specs.  In this case, it's an HP Proliant SL390s G7, which DOES allow for additional drives, based on the model, be it 1U, 2U or 4U.  For example, if it is a 4U, you can have up to eight 2.5" hot plug hard drive bays.



In my book, as a DBA you need to know you hardware layer just as much as you do the software layer.  This is a little tip that will help you do exactly that.

Monday, September 15, 2014

Using data to support the business, or to drive the decisions?

This is probably one of the best articles I have read in some time.  Written by Daniel Waisberg, published this month on www.thinkwithgoogle.com ~

No need to re-write it.  Please read it, see how Daniel suggests that we scope the project, answer a few very key questions, and build the framework.  Somewhat ironic, is the comparison to Architecture. I actually began my studies years ago as an Architect for homes, and he is right! Turning abstract design into reality is very much like structuring the data model. I think back to countless hours of reporting, and data analysis within the trading industry. It wasn't about the trade, or a specific data point. It was about the pre and the post, or the trending and the patterns. For example, the realtime PnL or the daily, month-to-date and even yearly statistics. Every time range provided a very different visual on the trading activity. I learned the market and the individual product definitions. I then provided one awareness to the traders, and another to upper management. That insight was used to predict gains (and losses), and drive the trade.  

Anyway, I was very impressed with Daniel's article, and I wanted to share it here as well. I have been on both sides of the fence, per se.  Supporting business operations with the data, and driving the business decisions. The former is overly complicated, very problematic, and not nearly advantageous as it need be.  Electronic tradding, or any other industry -- the latter is where we want to be.  I am certain.

Modeling our data with meaning.  Thinking with it, and gaining from it.  What else is there?

Friday, September 5, 2014

What are SQL Server Statistics?

Statistics store information about the distribution of data in your tables.  The optimizer uses this metadata to estimate the selectivity of a given statement, and create the optimal query plan.  To manage SQL Server efficiently, you really need a good understanding of the statistics.  In this post I will just provide some information that may help you to better understand, and manage your statistics

Statistics are automatically created on each of the index columns, when an index is created, and they are created automatically by the optimizer, when AUTO_CREATE_STATISTICS is enabled. Auto-created stats names include the column name and object ID in hexadecimal format, like this:     _WA_Sys_00000002_79FD19BE


Statistics can also be created manually, via the CREATE STATISTICS statement... though I, personally, have never had a need.  Generally speaking, the optimizer typically does quite fine. 

That's how they are created, but where are they stored? Statistics are stored in the sysindexes table.  We can also find additional information within the sys.stats and sys.indexes metadata views.  

By default, the statistics are updated automatically.  This occurs when 20% of the data in a table changes. It's important to know that this threshold can sometimes be a little high on the larger tables.  For this reason, it's not a bad idea to create a custom routine specific to the larger tables alone.  Or, even to just update the stats manually on those larger tables, on a periodic basis.  

How do you know if your statistics are being updated?  Run this statement to see if AUTO UPDATE is enabled:

       EXEC sp_helpdb YourDBName

The status line in the result-set should include 'IsAutoUpdateStatistics'.  If it doesn't, your statistics are not being updated.... ouch.  You'll want to correct that with this next statement, but remember -- if stats haven't been updated for a while, this 1st run can be pretty timely, and invasive.  Be sure to do this after hours:

      USE YourDBName;
      -- Enable Auto Update of Statistics
      ALTER DATABASE YourDBName
      SET AUTO_UPDATE_STATISTICS ON;
      GO
      -- Update Statistics for whole database 
      EXEC sp_updatestats
      GO

So, how does SQL Server actually use the statistics?  The optimizer uses this metadata to estimate how many rows each operator in the query plan will operate on.  That estimation is used to calculate the cost of each operator, and identify the cheapest query plan for the statement you are running.  Outdated, inaccurate or missing statistics can dramatically impact this process.  If the optimizer cannot accurately estimate this cost, you are going to see substantial performance degradation.

Performance not quite what it should be?  Well... when were your statistics last updated? Real easy to check:  

     SELECT 
          OBJECT_NAME(object_id) [Table],
          STATS_DATE(object_id, stats_id) [LastUpdated],
          name [Statistic]
     FROM 
         sys.stats
    WHERE 
         OBJECT_NAME(object_id) NOT LIKE 'sys%'
    ORDER BY 
         STATS_DATE(object_id, stats_id) DESC

    TIP:   Keep that one in your quick list. Somebody says 'why is it so slow?'.... start by 
             checking the stats.

Alright, so how do you read your stats?  I've listed several different methods below.  Run them on your system, take a look at your output.  This should help to better familiarize yourself with the statistics overall.  

     -- using sys.partitions and sys.indexes
     SELECT
     OBJECT_NAME(p.object_id) [Table],
             p.index_id [IndexID],
             i.name [Index],
             p.rows [#Rows]
      FROM
     sys.partitions p INNER JOIN sys.indexes i 
        ON p.object_id = i.object_id
                AND p.index_id = i.index_id
      WHERE   
      p.object_id = OBJECT_ID(N'Production.WorkOrder');

     -- using sys.dm_db_index_physical_stats
     SELECT  
       OBJECT_NAME(ips.object_id) [Table],
             ips.index_id [IndexID],
             i.name [Index],
             ips.record_count [#Rows]
     FROM
 sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks2012'),OBJECT_ID(N'Production.WorkOrder'),NULL,NULL,'DETAILED') ips JOIN sys.indexes i 
 ON ips.object_id = i.object_id
                  AND ips.index_id = i.index_id
     WHERE
    ips.index_level = 0;


     -- using sysindexes, sysobjects, sysusers, and information_schema.tables
     SELECT
         schemas.name [Schema],
         tbls.name [Table],
         i.name [Index],
         i.id [TableID],
         i.indid [IndexID],
         i.GroupID,
         i.rowmodctr [ModifiedRows],
          (SELECT MAX(rowcnt) 
           FROM sysindexes i2 
           WHERE i.id = i2.id
           AND i2.indid < 2) [RowCnt],
             CONVERT(DECIMAL(18,8),CONVERT(DECIMAL(18,8),i.rowmodctr) /
              CONVERT(DECIMAL(18,8),
(SELECT MAX(rowcnt) FROM sysindexes i2 WHERE i.id = i2.id AND i2.indid < 2) ) )      
           [ModifiedPct],
         stats_date( i.id, i.indid ) [LastStatsUpdate],
    'False' [Processed]
     FROM
          sysindexes i INNER JOIN sysobjects tbls
             ON i.id = tbls.id INNER JOIN sysusers schemas 
                 ON tbls.uid = schemas.uid INNER JOIN information_schema.tables tl
                    ON tbls.name = tl.table_name
                    AND schemas.name = tl.table_schema
                    AND tl.table_type = 'BASE TABLE'
     WHERE
          0 < i.indid 
         AND i.indid < 255
         AND table_schema <> 'sys'
         AND i.rowmodctr <> 0
AND (SELECT MAX(rowcnt) FROM sysindexes i2 WHERE i.id = i2.id AND i2.indid < 2) > 0


     -- using sys.stats and sys.dm_db_stats_properties
     SELECT 
          st.object_id [TableID],
          OBJECT_NAME(st.object_id) [Table],
          st.name [Index],
          STATS_DATE(st.object_id, st.stats_id) [LastUpdated],
          modification_counter [RowsModified]
     FROM
          sys.stats st CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) sp 
    WHERE
          STATS_DATE(st.object_id, st.stats_id) <= DATEADD(DAY,-1,GETDATE())
         AND modification_counter > 0
         AND OBJECTPROPERTY(st.object_id, 'IsUserTable' ) = 1


That's it for now.  It's pretty simple -- if your statistics are out of date, or missing, the optimizer is not able to make optimal query plan choices.  This will impede query performance.  In some cases very dramatically.  I have given you a lot of choices for viewing your statistics, and I have even sampled an actual statistic update.  I encourage you to take a look at Ola Hallengren's database maintenance solution, which includes a good piece for maintaining SQL Server statistics.   See here:   

       https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Another good reference is at Brent Ozar's site, the sp_Blitz script.  Among many other things, this procedure will tell you where your auto stats updates are disabled.  I encourage you to take a look at this page, but -- don't stop with the statistics.  You can use sp_Blitz to perform a very fast, yet hugely informative health check on your systems. Check it out.

      http://www.brentozar.com/blitz/auto-update-stats-disabled/


Lastly, take a look here for more in depth information regarding the statistics:
   
      http://msdn.microsoft.com/en-us/library/ms190397.aspx

Saturday, August 30, 2014

Monitoring SQL Server database transaction log usage

In this post I will provide a method for monitoring your database transaction logs using DBCC SQLPERF(logspace), which can be used to return transaction log space usage statistics for your databases.  We know there are many different causes for the transaction logs to expand.  It could be a very large or long-running transaction like index maintenance, or even a data archival routine (with lots of deletes).  You may also be using a FULL Recovery Model, but you aren't performing transaction log backups... or you're not performing them as frequently as you should.  

There are many causes for log growth, and you should be aware of that expansion so that you can act before a problem occurs.  This tip is a good way to keep an eye on that transaction log growth and utilization.  

First we need a table to store the output of DBCC SQLPERF(logspace).  We will use this table to store log use statistics over time.  This will enable you to analyze historically, which will be helpful for trending and diagnosing database and log growth.   

     -- create table to store output of DBCC SQLPERF(logspace)
     CREATE TABLE dbo.MonitorLogs (
        LogID INT PRIMARY KEY IDENTITY(1,1),
        LogDate DATETIME NOT NULL DEFAULT(GETDATE()),
        DBName VARCHAR(100) NOT NULL,
        LogSizeMB DECIMAL(18, 4) NOT NULL,
        LogSpaceUsed DECIMAL(18, 4) NOT NULL,
        LogStatus INT NOT NULL
      )

Now this is how we will execute the DBCC statement, directing the output to our new table:

      INSERT dbo.MonitorLogs (DBName,LogSizeMB,LogSpaceUsed,LogStatus)
      EXEC ('DBCC SQLPERF(logspace)')

Now we need to 'create' another day of data so that we can test the process completely.  To do this, I am just going to copy the data just inserted to MonitorLogs, using DATEADD to modify the LogDate to yesterday:

     INSERT dbo.MonitorLogs (LogDate,DBName,LogSizeMB,LogSpaceUsed,LogStatus)
     SELECT DATEADD(d,-1,LogDate),DBName,LogSizeMB,LogSpaceUsed,LogStatus
     FROM dbo.MonitorLogs

Check the data, you will now see statistics from yesterday as well.  You will use this last statement to compare yesterday's data with today's.  The principal is just the monitoring of your transaction log space over time.

     DECLARE @Yesterday DATE = CONVERT(DATE, GETDATE()-1);
     DECLARE @Today DATE = CONVERT(DATE, GETDATE());

     SELECT 
     COALESCE(b.DBName,a.DBName) [Database],
     a.LogSizeMB [TodayLogSizeMB],
     b.LogSizeMB [YestLogSizeMB],
     a.LogSizeMB - b.LogSizeMB [SizeMBDiff],
     a.LogSpaceUsed [TodayLSpaceUsed],
     b.LogSpaceUsed [YestLSpaceUsed],
     a.LogSpaceUsed - b.LogSpaceUsed [UsedDiff]
    FROM
     dbo.MonitorLogs a FULL OUTER JOIN dbo.MonitorLogs b 
        ON a.DBName = b.DBName
    WHERE 
    CONVERT(DATE, a.LogDate) = @Yesterday 
    AND CONVERT(DATE, b.LogDate) = @Today
    ORDER BY 
    TodayLogSizeMB DESC


That's pretty much it.  Your result set will look like this:



This is just one way to get the job done.  In the newer releases you should take a look at both of these DMVs:    

sys.dm_tran_database_transactions   http://msdn.microsoft.com/en-us/library/ms186957.aspx
sys.dm_tran_active_transactions        http://msdn.microsoft.com/en-us/library/ms174302.aspx



Friday, August 29, 2014

SQL Server CPUs -- physical, virtual and core count

I received an email from one of my readers this morning, asking if there was a way to query CPU information from the server with tSQL.  I put together the piece below, and wanted to go ahead and share it here with all of you.  Real quick and easy, uses sys.dm_os_sys_info and xp_msver to return specific details about your CPUs, physical, virtual and core count.

    DECLARE @results TABLE (
        ID INT NULL,
        CNAME VARCHAR(100) NULL,
        IVALUE FLOAT NULL,
        CVALUE VARCHAR(128) NULL  )

    INSERT @results
    EXEC ('[master]..[xp_msver]');;

    WITH CPUinfo
    AS (
SELECT 
([cpu_count] / [hyperthread_ratio]) [#PhysicalCPUs],
                 CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count
  ELSE (([cpu_count]-[hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))    END [#CoresPerCPU],
     [cpu_count] [#VirtualCPUs],
            CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count
    ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] /            [hyperthread_ratio]))     END [TotalCores],
     ( 
       SELECT CVALUE FROM @results WHERE CNAME = 'Platform' ) [CPU Category]
       FROM sys.dm_os_sys_info            )

       SELECT
             [#PhysicalCPUs],
             [#CoresPerCPU],
             [#VirtualCPUs],
             [TotalCores],
         LTRIM(RIGHT([CPU Category], CHARINDEX('x', [CPU Category]) - 1)) [CPU Category]
       FROM 
          CPUinfo


Run it against your servers, you'll see different results, but this is what I get from my laptop:    
#PhysicalCPUs #CoresPerCPU #VirtualCPUs TotalCores CPU Category
1 4 4 4 x64

Take a look at both of these for a little more information:
        xp_msver                     http://msdn.microsoft.com/en-us/library/ms187372.aspx
        sys.dm_os_sys_info      http://msdn.microsoft.com/en-us/library/ms175048.aspx



Friday, August 1, 2014

What filegrowth are you using for your database files?

The default FILEGROWTH setting is 10% for SQL Server's database files.  I don't know why they haven't changed it yet, but it is not recommended to keep it as 10%, or even to keep it as a percentage.  If the files grow at 10%, there could be a lot of repetitive filegrowths. Repeat filegrowth will result in fragmentation, which will directly impact your performance.  In addition to fragmentation, when the files are expanded, the current processing could be delayed, or even timeout.  The larger your databases become, the more problems you will have with the 10% filegrowth.


It is recommended to use 'In Megabytes' option for the database filegrowth.  What MB you use is dictated by the size of your database, and the type of activity.  Or, how much the data is changing, and at what frequency.  For larger databases, I typically start with 100MB, but I have used anywhere between 100MB to 1024MB.  

This script is just a quick call to sys.master_files, to return details regarding your current data file configuration.  Execute it as-is for your system databases, or remove the WHERE clause to target all databases.  

/*
Helpful query to return the databases file details for each database.    */

SELECT 
DB_NAME(mf.database_id) [Database],
physical_name  [PhysicalFileName],
CONVERT (DECIMAL(20,2),(CONVERT(DECIMAL, size)/128)) [FileSizeMB],
CASE mf.is_percent_growth 
                        WHEN 1 THEN 'Yes' ELSE 'No' END AS [PctGrowth],
CASE mf.is_percent_growth 
                       WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
       WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB' END [FileGrowth],
CASE mf.max_size WHEN 0 THEN 'Growth is not allowed'
WHEN -1 THEN 'Will grow until disk is full'
                    ELSE CONVERT(VARCHAR, mf.max_size) END [MaxSize]
FROM
sys.master_files mf 
WHERE
DB_NAME(mf.database_id) IN('master','model','msdb')  --- Adjust as needed.

Results:


As you can see above, the filegrowth was changed from 10% to 10MB.  Understand, these are the system databases, within which there will be fewer changes and less activity. This is the reason for the small filegrowth of 10MB.  You will not be able to use the same filegrowth for every database.  You'll want to review each database selectively, and determine the most appropriate setting for each.


Here I have listed a few of my must-haves this topic.  Definitely take a look.

This provides examples for altering the filegrowth and other file/filegroup properties.

Or see step 7 here, for a good practice for managing log filegrowth:

Brent Ozar's 'SQL Server Setup Checklist' AND the 'Instant File Initialization':  

Yes, you should enable 'Instant File Initialization?'

Thursday, July 31, 2014

Should you enable Instant File Initialization for your SQL Server?

Yes.  You should.  Now let me explain.

What is 'File Initialization' ?  When the database data files talk to the disk, they have to be 'initialized'.  This means that any newly allocated space is overwritten with zero values (0×0). Windows does this to avoid problems by accessing old data, previously stored on the same disk. This zero File Initialization causes operations like this to take much longer:
  • Create new database
  • Auto-growth of any data file
  • Backup or restore
Ok. So what is 'Instant File Initialization' ?  This is a Windows setting that will allow SQL Server to skip the zero initialization.  Instant File Initialization will claim the space to be used WITHOUT pre-filling it with zeros.  The impact?  This setting will make data file growth faster.  Much faster.  

Instant File Initialization can be enabled by granting the SQL Server service account the Perform Volume Maintenance Task privilege, as I have outlined below.  This only applies to data files.  The SQL Server database log files cannot be initialized like this.

Start / Run – type in ‘secpol.msc’
Left  side under Security Settings, go to Local Policies, then User Rights Assignment.


Right side under Policy, double-click ‘Perform volume maintenance tasks’.  
In ‘Perform volume maintenance task Properties’, choose ‘Add User or Group’.


Add in your SQL Server Service account.


Restart your SQL Server service.


This is actually one of my defaults in any SQL Server build.  I encourage you to enable it on your existing servers now, as well as any new builds in the future.  Take a look at each of these references before making any changes.  Let me know if I can provide anything further.



Wednesday, July 30, 2014

Relocate and Re-size the TempDB

Often times it becomes necessary to move the tempdb.  Maybe your data files are becoming too large, or you want to relocate the files to achieve more optimal performance.  It is fairly easy to move the tempdb, but it will require a restart of the SQL Server service.  In this example I have both changed the location of the files, and added a couple of new ones.  The end result is a completely isolated TempDB -- on it's own drive, and separate from the user and other system databases.

Let's run this first to confirm the logical file names for the tempdb data files: 
 USE tempdb;
 EXEC sp_helpfile

Your results will look something like this:

name      fileid   filename                       filegroup        size        maxsize    growth    usage
tempdev       1            C:\MSSQL\Data\tempdb.mdf       PRIMARY        1092 KB   Unlimited   10%         data only
templog        2            C:\MSSQL\Log\templog.ldf         NULL                512 KB   Unlimited   10%         log only


The first step is to move TempDB to its own drive. I only set a 1mb file size because SQL Server does something a little screwy here. It does not matter if we tell it to use a different drive letter. SQL will still look for the given amount of free space on the drive that TempDB currently sits on. So, if SQL is installed at C:\Program Files\, and we try to create a 10GB TempDB file on a different drive, the server will look for 10GB on the C drive. Its a bug I learned about here:
      http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-2-after-the-install/

   -- Step 1.
   USE master;
   GO
   ALTER DATABASE TempDB MODIFY FILE (
      name = 'tempdev', 
   filename = 'F:\MSSQL\TempDB\tempDB.mdf', 
   size = 1MB
   )
   GO

   ALTER DATABASE TempDB MODIFY FILE (
   name = 'templog', 
   filename = 'F:\MSSQL\TempDB\templog.ldf', 
   size = 1MB
   )
   GO


At this point, you will need to restart the SQL Server service, in order for the changes to take effect. Take a look at the F:\ drive (or whatever you used), and note your new files. 

The second step is to expand the TempDB data file to the correct full size, and create three additional TempDB files. I always use Paul Randal's advice, which is to make one data file for every processor core. Sometimes I need more, but I pretty much always start with 4 and go from there.

You can see that I expanded tempdev to 250MB, and created the 3 new files at 250MB each, with 0 FILEGROWTH.  This is VERY important.  We don't want them to auto-grow frequently in production, because this causes disc fragmentation, and impedes activity during the file growth.  Ideally, we preallocate TempDB to take whatever disc we have reserved for it.  Basically, build it proactively, not re-actively.  

  -- Step 2.
  -- Expand tempdev
    USE master;
    ALTER DATABASE TempDB MODIFY FILE (
   name = 'tempdev', 
   filename = 'F:\MSSQL\TempDB\tempDB.mdf', 
   size = 250MB,
   filegrowth = 0
     )
     GO

   -- Add three new data files
     USE master;
     ALTER DATABASE TempDB ADD FILE (
   name = 'tempdev2', 
   filename = 'F:\MSSQL\TempDB\tempdev2.ndf', 
   size = 250MB, 
   filegrowth = 0
     )
     GO
     USE master;
     ALTER DATABASE TempDB ADD FILE (
      name = 'tempdev3', 
   filename = 'F:\MSSQL\TempDB\tempdev3.ndf', 
   size = 250MB, 
   filegrowth = 0
     )
     GO
     USE master;
     ALTER DATABASE TempDB ADD FILE (
   name = 'tempdev4', 
   filename = 'F:\MSSQL\TempDB\tempdev4.ndf', 
   size = 250MB, 
   filegrowth = 0
     )     
     GO

Now I will rename the original 'tempdev' data file to 'tempdev1'.  This is totally NOT a requirement. I just like to keep all of my files consistently named.  (tempdev1, tempdev2, tempdev3, etc.):

   /* Rename files */
   ALTER DATABASE tempdb
   MODIFY FILE (
       NAME ='tempdev', 
       NEWNAME = 'tempdev1'
   )

Lastly, I will increase the size of the tempdb log to 2GB.  Remember, this is just an example.  The size of your tempdb log will be dictated by your SQL Server utilization.

   /* Increase TempDB Log file.  */
   ALTER DATABASE tempdb
   MODIFY FILE (
      NAME = 'templog', 
      SIZE = 2048MB
   );


There are many other different references out there on tempdb performance, such as the ones I've linked to below.   I encourage you to do some research.  TempDB is not something where we have a lot of room for errors.  Take a look at these references, and let me know if I can help any further.


Script to backup all SQL Server databases

This post covers two topics --  First, it is a very simple method to backup all of the user databases. Secondly, it is an example of a cursor.  I typically stay away from cursors within any real data manipulations, but I don't mind using them for simple functions such as this. The tSQL is a very simple BACKUP command, and the cursor just allows us to walk through all of the databases with that command, very quickly and very efficiently.  

/*
Use cursor to backup all user databases.  */

DECLARE 
@DBName VARCHAR(25),   -- database name  
@BkupPath VARCHAR(255),   -- the path for backup files  
@BkupFileName VARCHAR(255),   -- the backup filename
@FileDate VARCHAR(10)   -- to put date into filename
 
-- Where is the backup directory?
SET @BkupPath = 'C:\MSSQL\Backup\'  
 
-- Construct the BkupFileName
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
 
-- Declare your cursor
DECLARE DBCursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude the system databases
 
-- Open your cursor
OPEN DBCursor   
FETCH NEXT FROM DBCursor INTO @DBName   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
  SET @BkupFileName = @BkupPath + @DBName + '_' + @FileDate + '.BAK'  
  BACKUP DATABASE @DBName TO DISK = @BkupFileName  
 
  FETCH NEXT FROM DBCursor INTO @DBName   
END   
 
-- Close your cursor
CLOSE DBCursor   
DEALLOCATE DBCursor

Note, you can revise the resulting .bak filename, if you'd like something different than 'DatabaseName_YYYYMMDD.bak'.  Also note the WHERE clause on the cursor declaration. You could adjust this to IN or NOT IN, and customize the database list to whatever databases you'd like to target.  This is the clause that I used in my test:     WHERE name IN ('master','model','msdb')

And this is my output:


Take a look at that run time.  All three sys dbs backed up to in 5 seconds.  Not bad.

That's pretty much it.  Like I said, you should modify that WHERE clause to suit your needs.  This is a very flexible, easily customized routine for backing up multiple databases via cursor. 

See here for more information on cursors and the BACKUP command: