Ramblings of a DBA

Tara Kizer
posts - 118, comments - 633, trackbacks - 75

My Links

SQLTeam.com Links

News

Subscribe
Search this Blog

Archives

Post Categories

Image Galleries

Work

Wednesday, August 06, 2008

SQL Profiler best practices

SQL Profiler is a great tool that allows you to see what's going on inside SQL Server.  You can find out what your worst performing queries are, how often a query is executed, and loads of other stuff.  I'm typically interested in the things that are impacting performance such as long durations of stored procedures.

One important thing to know when running a trace using SQL Profiler is that the trace can degrade performance.  To minimize its impact, you should follow these best practices:

  1. Never run SQL Profiler on the database server
  2. Never trace directly to a table, instead trace to a file (You can query the active trace file using this technique)
  3. Filter the results, such as "Duration > 1000"
  4. Include only the events that you are interested in, such as SP:Completed and SQL:BatchCompleted

For more information on SQL Profiler, check out this article.

You should also know that a server-side trace, SQL Trace, can use even less resources than SQL Profiler.  SQL Trace allows you to create a trace programmatically by calling system stored procedures.  Due to this, you can automatically collect trace data via SQL jobs. 

For more information on SQL Trace, start here.

posted @ Wednesday, August 06, 2008 5:36 PM | Feedback (6)

Tuesday, August 05, 2008

How to get physical CPU count on a server

With hyper-threading and multi-core CPUs, it isn't easy to find out how many physical CPUs (or sockets) there are on a server. Recently we were given such a task to ensure we were properly licensed.


Read more here.

posted @ Tuesday, August 05, 2008 9:48 AM | Feedback (2)

Saturday, August 02, 2008

OT: Kevin Bacon Theory

Microsoft "confirms" the small world theory. We are linked by 6.6 degrees of separation.


Read more here.

posted @ Saturday, August 02, 2008 5:58 PM | Feedback (1)

Monday, June 30, 2008

SQL Server jobs on production instances

The most important thing that a Database Administrator does is backups.  To automate them, we schedule them as jobs.  But what other jobs are important on SQL Server instances?

Here are the jobs that I have on every SQL Server 2005 production instance:

Name

Code

Schedule

Notes

Backup System Databases

EXEC isp_Backup
    @path = 'F:\Backup\',
    @dbType = 'System',
    @bkpType = 'Full',
    @retention = 5,
    @liteSpeed = 'N'

Daily  
Backup Transaction Logs

EXEC isp_Backup
    @path = 'F:\Backup\',
    @dbType = 'User',
    @bkpType = 'TLog',
    @retention = 5,
    @liteSpeed = 'Y'

Every 15 minutes  
Backup User Databases

EXEC isp_Backup
    @path = 'F:\Backup\',
    @dbType = 'User',
    @bkpType = 'Full',
    @retention = 5,
    @liteSpeed = 'Y'

Daily  
Defragment Indexes

EXEC isp_ALTER_INDEX
    @dbName = @dbName,
    @statsMode = 'SAMPLED',
    @defragType = 'REBUILD',
    @minFragPercent = 10,
    @maxFragPercent = 100,
    @minRowCount = 1000

Daily or weekly Loop through each database, see this for sample code.
Delete Backup History

DECLARE @d datetime

SET @d = DATEADD(day, -30, GETDATE())

EXEC msdb.dbo.sp_delete_backuphistory @d

Daily or weekly See this for more details.
Delete Database Mail History

DECLARE @d datetime

SET @d = DATEADD(dd, -5, GETDATE())

EXEC msdb.dbo.sysmail_delete_mailitems_sp
    @sent_before = @d

Daily or weekly Needed only on systems that send mail with attachments using Database Mail.

See this for more details.
Integrity Checks EXEC isp_DBCC_CHECKDB Daily  
Update Statistics

EXEC isp_UPDATE_STATISTICS
        @dbName = @dbName,
        @sample = 25

Daily Might not be needed if your indexes are getting defragmented daily.

Loop through each database, see this for sample code.

 Do you have any jobs that you put on every production SQL Server instance that I didn't cover?  If you do, I'd love to hear from you.

posted @ Monday, June 30, 2008 9:46 AM | Feedback (16)

Tuesday, June 24, 2008

Security Tools to help customers with SQL injection attacks

Microsoft has released three security tools to help customers with SQL injection attacks. 

The tools are:

  • UrlScan version 3.0 Beta, a security tool that restricts the types of HTTP requests that Internet Information Services (IIS) will process. By blocking specific HTTP requests, the UrlScan helps prevent potentially harmful requests.
  • Microsoft Source Code Analyzer for SQL Injection Community Technology Preview (June 2008), a tool that can be used to detect ASP code susceptible to SQL injection attacks.
  • Scrawlr, a free scanner, developed by HP Web Security Research Group in conjunction with Microsoft, which will allow customers to identify whether their Web sites might be susceptible to SQL injection.

See this for details.

posted @ Tuesday, June 24, 2008 1:53 PM | Feedback (1)

How to transfer SQL logins between SQL Server 2005 instances

There are many ways to transfer SQL logins between SQL Server instances.  Here are a few of them:

  1. sp_help_revlogin
  2. Transfer Logins Task in a DTS package
  3. Transfer Logins Task in an SSIS package
  4. SQL Server Magazine method
  5. Manually (like that's even a consideration for an experienced DBA)

In an ideal world, an application will connect to a SQL Server instance using one account (or perhaps a couple extra) and then "authenticate" individual users via application code and a SQL table. 

We have a legacy system that has thousands of SQL accounts, one per user.  We've got over 8,000 users now.  Twice a year, we move all of our production applications and databases to our disaster recovery site to either test our DR readiness or so that we can perform wide scale maintenance at our primary site.  Due to this, we must synchronize the users between the sites for this one particular system.  Luckily, our other systems live in an ideal world.

All of the transfer logins methods listed above have issues (at least for us, your mileage may vary), so I decided to write my own. 

SET NOCOUNT ON

CREATE TABLE #Logins
(
    loginId int IDENTITY(1, 1) NOT NULL,
    loginName nvarchar(128) NOT NULL,
    passwordHash varbinary(256) NULL,
    sid varbinary(85) NOT NULL
)

-- openquery is used so that loginproperty function runs on the remote server,
-- otherwise we get back null
INSERT INTO #Logins(loginName, passwordHash, sid)
SELECT *
FROM OPENQUERY([LinkedServerName], '
SELECT name, CONVERT(varbinary(256), LOGINPROPERTY(name, ''PasswordHash'')), sid
FROM master.sys.server_principals
WHERE
    type = ''S'' AND 
    name NOT IN (''sa'', ''guest'') AND 
    create_date >= ''12/31/2005''
ORDER BY name')

DECLARE 
    @count int, @loginId int, @loginName nvarchar(128), 
    @passwordHashOld varbinary(256), @passwordHashNew varbinary(256), 
    @sid varbinary(85), @sql nvarchar(4000), @password varchar(514)

SELECT @loginId = 1, @count = COUNT(*)
FROM #Logins

WHILE @loginId <= @count
BEGIN
    SELECT @loginName = loginName, @passwordHashNew = passwordHash, @sid = sid
    FROM #Logins
    WHERE loginId = @loginId

    -- if the account doesn't exist, then we need to create it
    IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE name = @loginName)
    BEGIN
        EXEC master.dbo.sp_hexadecimal @passwordHashNew, @password OUTPUT

        SET @sql = 'CREATE LOGIN ' + @loginName + ' WITH PASSWORD = '
        SET @sql = @sql + CONVERT(nvarchar(512), COALESCE(@password, 'NULL')) 
        SET @sql = @sql + ' HASHED, CHECK_POLICY = OFF' 
        --PRINT @sql
        EXEC (@sql)

        --PRINT 'login created'
    END
    -- if the account does exist, then we need to drop/create to sync the password;
    -- can't alter as hashed isn't supported
    ELSE
    BEGIN
        SELECT @passwordHashOld = CONVERT(varbinary(256), LOGINPROPERTY(@loginName, 'PasswordHash'))

        -- only bother updating if the password has changed since the last sync
        IF @passwordHashOld <> @passwordHashNew
        BEGIN
            EXEC master.dbo.sp_hexadecimal @passwordHashOld, @password OUTPUT

            SET @sql = 'DROP LOGIN ' + @loginName
            --PRINT @sql
            EXEC (@sql)

            SET @sql = 'CREATE LOGIN ' + @loginName + ' WITH PASSWORD = '
            SET @sql = @sql + CONVERT(nvarchar(512), COALESCE(@password, 'NULL'))
            SET @sql = @sql + ' HASHED, CHECK_POLICY = OFF' 
            --PRINT @sql
            EXEC (@sql)

            --PRINT 'login "altered"'
        END
    END

    SET @loginId = @loginId + 1
END

DROP TABLE #Logins

The script depends on sp_hexadecimal.  You can get it here.

To transfer SQL logins between SQL Server 2005 instances using my script, run it on the server that will receive the updates.  "LinkedServerName" in the OPENQUERY statement needs to be changed to the linked server that points to the source server.

posted @ Tuesday, June 24, 2008 9:16 AM | Feedback (0)

Monday, June 23, 2008

How to get information about Windows users and groups using T-SQL

Every now and then, someone will ask in the SQLTeam forums how to find out what Windows users are members of a particular Windows group so that they can find out who has access to their SQL Server.  The question doesn't get asked often, but I can never remember what the answer is unless I google it.  Now I'll be able to find the answer here.

SQL Server provides an extended stored procedure, xp_logininfo, to get information about Windows users and groups. 

Syntax:

xp_logininfo [ [ @acctname = ] 'account_name' ]
     [ , [ @option = ] 'all' | 'members' ]
     [ , [ @privilege = ] variable_name OUTPUT]

xp_logininfo enables us to find out what Windows users are members of a particular Windows group.  For instance: EXEC master.dbo.xp_logininfo 'DomainName\GroupName', 'members'

xp_logininfo also enables us to find out what Windows groups a particular Windows user is a member of.  For instance: EXEC master.dbo.xp_logininfo 'DomainName\UserName'

@acctname must be full qualified.  If your Windows user or Windows group is local rather than in Active Directory, simply use WorkgroupName instead of DomainName.

If the Windows user or Windows group exists but does not have access to the SQL Server instance, you will get an empty result set returned.  It will not error.

posted @ Monday, June 23, 2008 9:36 AM | Feedback (0)

Wednesday, June 18, 2008

How to delete backup and restore history from msdb

Have you ever wondered why your msdb database is unusually large?  Did you know that SQL Server keeps historical information about every backup and restore operation in the msdb database?  It is recommended that you purge this backup and restore historical information on a scheduled basis.  Microsoft provides sp_delete_backuphistory to do the purge.  In SQL Server 2000, the stored procedure ran very slow as it cursored through the data.  As a result of the slowness, I wrote my own purge stored procedure.  I blogged about this a few years back. 

When we upgraded our systems to SQL Server 2005, I was curious if Microsoft had made sp_delete_backuphistory more efficient as I did not want to maintain my custom code.  I was ecstatic to find out that my wish had been granted.  I'll have to ask for money in my next wish before I run out of wishes!

For those of you who use my custom code (or similar code) to delete the backup/restore history in SQL Server 2005, start using sp_delete_backuphistory instead.

posted @ Wednesday, June 18, 2008 2:12 PM | Feedback (2)

Monday, June 16, 2008

Asynchronous Update Statistics

For the past several months, we have been overwhelmed with performance issues on one particular system.  We know what is causing it, but it is going to take time to modify the code, test the changes, and then deploy to production.  In the meantime, we made several configuration changes that helped performance.  Two of the changes were adding data files to the tempdb database and enabling the asynchronous option of automatic statistics updating (AUTO_UPDATE_STATISTICS_ASYNC). 

I covered our tempdb change in a previous blog.  In it, I described how to optimize tempdb and also provided a script to add the tempdb data files based upon the number of CPU cores.  I ran the script on each of our production instances and not just on the one where we are experiencing problems. 

Now let's talk about the async auto update stats change.  In previous versions of SQL Server, the auto update stats happened synchronously only.  In SQL Server 2005, we have the ability to do it asynchronously as well.  The default is synchronous.

How are the two options (sync and async) different?  When the setting is off and a statistics update is initiated due to out-of-date statistics in the execution plan, the query must wait until the statistics update is complete before compiling and then returning the result set.  When the setting is on, the query does not need to wait as the statistics update are handled by a background process.  The query will not get the benefit of the statistics update, however future queries will.

In order for the option to work, AUTO_UPDATE_STATISTICS must be enabled as well.  It will allow you to enable AUTO_UPDATE_STATISTICS_ASYNC without AUTO_UPDATE_STATISTICS being enabled.  I think it should at least warn you in case you forgot to enable AUTO_UPDATE_STATISTICS.

Here is how to enable the option:

ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ON

Enabling the async option affects your ability to put a database into single-user mode.  The option must be disabled to put a database into single-user mode as the async option uses a background thread which takes a connection in the database. 

To see which databases have the option enabled, check out the is_auto_update_stats_async_on column in sys.databases.

posted @ Monday, June 16, 2008 10:37 AM | Feedback (1)

Friday, June 13, 2008

Open Command Window Here

Most of you probably already have done this, but just in case you haven't and find yourself frequently using cmd to run commands such as sqlcmd, you will find this registry "hack" useful. 

I use sqlcmd on a weekly, if not daily, basis.  I'm constantly switching directories to where my scripts are.  Rather than switching directories in cmd, I can instead use the "Open Command Window Here" option in Windows Explorer.  To get this option on your machine, save the below as a reg file and then double-click on it:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Directory\shell\cmd]
@="Open Command Window Here"

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Directory\shell\cmd\command]
@="cmd.exe /k \"cd %L\""

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Drive\shell\cmd]
@="Open Command Window Here"

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Drive\shell\cmd\command]
@="cmd.exe /k \"cd %L\""

After you've modified the registry, open up Windows Explorer, right click on a directory, and then select the new option.  Notice how a cmd window opens in the requested directory.

Disclaimer: Modifying the registry can cause serious problems that may require you to reinstall your operating system.  Use the information provided at your own risk.

posted @ Friday, June 13, 2008 12:19 PM | Feedback (1)

Wednesday, June 11, 2008

Idera's SQL admin toolset

We are required to start auditing our SQL Servers for various data points.  We did not want to write a custom tool if at all possible, so we started looking for third-party products.  We found Idera's SQL admin toolset.  It doesn't collect all of the information that we want to audit, but it's a great start.

Excerpt from their site about the tool:

Are you a DBA or developer tired of spending countless hours on routine administrative and troubleshooting tasks, like figuring out why a user can't connect, or moving databases, or producing reports for your boss to show that the backups really did run, or finding rogue SQL Servers, or checking configurations or…. yeah, we know, the list is endless.

If the answer is yes then, you need our SQL admin toolset. Packed with 24 powerful tools that will make troubleshooting, administration and reporting such a breeze you'll be wondering what to do with all the spare time you have on your hands - really! Download now and see.


I do not work for Idera.  I am not getting paid to write this nor was I asked to write this.  I found this tool to be so useful that I wanted to bring it to other's attention, especially since it's free to use right now.

posted @ Wednesday, June 11, 2008 1:43 PM | Feedback (7)

Friday, June 06, 2008

New RSS Feed

Attention Loyal Readers,

I've updated my RSS feed to use FeedBurner.  If you haven't already done so, please update your link to this one:  http://feeds.feedburner.com/RamblingsOfADba

If you don't update your feed, you will be redirected to the new one.  However, I won't know how many loyal readers I have out there.  I could be persuaded to write more if I knew how big my audience is.

For those of you who read my blog through my main blog page or through the SQLTeam main blog page, I'd recommend getting a blog reader to make it easier to read blogs.  I use Google Reader.

If you don't know what I'm talking about, check this out.

posted @ Friday, June 06, 2008 11:53 AM | Feedback (5)

Thursday, June 05, 2008

SQL Injection Attacks

There's a lot of information out there on how to avoid SQL injection attacks, but I wanted to point you to this blog due to the recent increase in such attacks. 

Thank you, Buck Woody, for bringing this to our (SQL Server MVPs) attention. 

posted @ Thursday, June 05, 2008 2:33 PM | Feedback (2)

Friday, May 23, 2008

How to troubleshoot SQL Server connectivity problems

We often get asked in the SQLTeam forums why they can't connect to a SQL Server instance.  They provide the typical connectivity error. 

In SQL Server 2000, the error is:

SQL Server does not exist or access denied.

In SQL Server 2005, the error is:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Both errors are misleading as they are not usually resolved by providing access or enabling remote connections.  In fact, I've never seen the SQL Server 2000 error to be as a result of lack of access.  If someone doesn't have access, they will get the "Login failed" error.

Here is an example thread where I helped the original poster and someone who hijacked the thread get connected to their respective SQL Server instances.  Here is another example. 

Recently, I found this MSDN blog post.  It has a bunch of typographical errors, but it is very detailed and should help resolve most connection problems.  The most important typo is netstat is spelled as netstate.  If the user doesn't realize that the command is netstat, they will get an error which could lead them in the wrong direction when troubleshooting this issue.  Hopefully the blog post will get corrected at some point.

Rather than linking to the SQLTeam threads I mentioned above when I see new threads with these errors, I will now be pointing them to this blog post.  Between the 3 links, the person should be able to resolve their problem. 

posted @ Friday, May 23, 2008 10:53 AM | Feedback (8)

Wednesday, May 21, 2008

Backup databases stored procedure - new version

I have updated my backup stored procedure, isp_Backup, as the old version had two issues. It did not exclude database snapshots, which can't be backed up. It also was not checking for the number of CPUs in the registry properly. The CPU check only affects backups when SQL Litespeed is used.

In the next version, I will exclude those databases that are being log shipped by SQL Litespeed as we don't want to interfere with the LSNs. I am already checking for this condition for native backups. I will also add code to perform a full backup when it is performing a differential or transaction log backup and a full backup does not exist. This will be done to avoid backup errors.

----------------------------------------------------------------------------------------------------
-- OBJECT NAME            : isp_Backup
--
-- AUTHOR               : Tara Kizer
--
-- INPUTS                : @path - location of the backups, default backup directory used if @path is null
--                          @dbType - which database(s) to backup
--                            All, System, User, or dash followed by database name (ex. -Toolbox)
--                          @bkpType - type of backup to perform
--                            Full, TLog, Diff
--                          @retention - number of days to retain backups, -1 to retain all files
--                          @liteSpeed - perform backup using LiteSpeed (Imceda product)
--                            N, Y
--
-- OUTPUTS                : None
--
-- RETURN CODES            : 0-10 (see @error table variable at the end for the messages)
--
-- DEPENDENCIES            : None
--
-- DESCRIPTION            : Performs backups.
--
-- EXAMPLES (optional)  : EXEC isp_Backup @path = 'C:\MSSQL\Backup\', @dbType = 'All', @bkpType = 'Full', @retention = 5, @liteSpeed = 'N'
----------------------------------------------------------------------------------------------------
CREATE PROC [dbo].[isp_Backup]
(@path varchar(100), @dbType sysname = 'All', @bkpType char(4) = 'Full', @retention smallint = 2, @liteSpeed char(1) = 'N')
AS

SET NOCOUNT ON

DECLARE @now char(14)             -- current date in the form of yyyymmddhhmmss
DECLARE @dbName sysname         -- database name that is currently being processed
DECLARE @cmd nvarchar(4000)     -- dynamically created DOS command
DECLARE @result int             -- result of the dir DOS command
DECLARE @rowCnt int             -- @@ROWCOUNT
DECLARE @fileName varchar(200)    -- path and file name of the BAK file
DECLARE @edition int            -- edition of SQL Server (1 - Personal or Desktop Engine; 2 - Standard; 3 - Developer or Enterprise)
DECLARE @rc int                    -- return code
DECLARE @extension char(4)        -- extension for backup file
DECLARE @version char(1)        -- one digit version number, i.e. 8 (2000) or 9 (2005)

-- log shipping tables have been renamed in 2005
SET @version = CONVERT(char(1), SERVERPROPERTY('ProductVersion'))

IF @version NOT IN ('8', '9')
BEGIN
    SET @rc = 1
    GOTO EXIT_ROUTINE
END

-- Enterprise and Developer editions have msdb.dbo.log_shipping* tables, other editions do not
SET @edition = CONVERT(int, SERVERPROPERTY('EngineEdition'))

-- validate input parameters
IF @dbType IS NOT NULL AND @dbType NOT IN ('All', 'System', 'User') AND @dbType NOT LIKE '-%'
BEGIN
    SET @rc = 2
    GOTO EXIT_ROUTINE
END

IF @dbType LIKE '-%' AND @version = '8'
BEGIN
    IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
    BEGIN
        SET @rc = 3
        GOTO EXIT_ROUTINE
    END
END
ELSE IF @dbType LIKE '-%' AND @version = '9'
BEGIN
    IF NOT EXISTS (SELECT * FROM master.sys.databases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
    BEGIN
        SET @rc = 3
        GOTO EXIT_ROUTINE
    END
END

IF @bkpType IS NOT NULL AND @bkpType NOT IN ('Full', 'TLog', 'Diff')
BEGIN
    SET @rc = 4
    GOTO EXIT_ROUTINE
END

IF @dbType = 'System' AND @bkpType <> 'Full'
BEGIN
    SET @rc = 5
    GOTO EXIT_ROUTINE
END

IF @liteSpeed IS NOT NULL AND @liteSpeed NOT IN ('N', 'Y')
BEGIN
    SET @rc = 6
    GOTO EXIT_ROUTINE
END

-- use the default backup directory if @path is null
IF @path IS NULL
    EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @path output, 'no_output'

-- we need the backslash after the path, so add it if it wasn't provided in the input parameter
IF RIGHT(@path, 1) <> '\'
    SET @path = @path + '\'

CREATE TABLE #WhichDatabase(dbName SYSNAME NOT NULL)

-- put the databases to be backed up into temp table
IF @dbType LIKE '-%'
BEGIN
    IF @bkpType = 'TLog' AND DATABASEPROPERTYEX(SUBSTRING(@dbType, 2, DATALENGTH(@dbType)), 'RECOVERY') = 'SIMPLE'
    BEGIN
        SET @rc = 7
        GOTO EXIT_ROUTINE
    END
    
    IF @edition = 3
    BEGIN
        IF @version = '8'
        BEGIN
            IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_databases WHERE database_name = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
            BEGIN
                SET @rc = 8
                GOTO EXIT_ROUTINE
            END
        END
        ELSE IF @version = '9' 
        BEGIN
            IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_primary_databases WHERE primary_database = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
            BEGIN
                SET @rc = 8
                GOTO EXIT_ROUTINE
            END
        END
    END

    IF @version = '9'
    BEGIN
        IF EXISTS (SELECT * FROM master.sys.databases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)) AND source_database_id IS NOT NULL)
        BEGIN
            SET @rc = 11
            GOTO EXIT_ROUTINE
        END
    END

    INSERT INTO #WhichDatabase(dbName)
    VALUES(SUBSTRING(@dbType, 2, DATALENGTH(@dbType))) 
END
ELSE IF @dbType = 'All' 
BEGIN
    IF @edition = 3 AND @version = '8'
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.dbo.sysdatabases
        WHERE 
            [name] NOT IN ('tempdb', 'ReportServerTempDB') AND
            [name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases) AND
            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
        ORDER BY [name]
    ELSE IF @edition = 3 AND @version = '9'
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.sys.databases
        WHERE 
            [name] NOT IN ('tempdb', 'ReportServerTempDB') AND
            [name] NOT IN (SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases) AND
            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
        ORDER BY [name]
    ELSE IF @version = '8'
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.dbo.sysdatabases
        WHERE 
            [name] NOT IN ('tempdb', 'ReportServerTempDB') AND
            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
        ORDER BY [name]
    ELSE -- version is 9
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.sys.databases
        WHERE 
            [name] NOT IN ('tempdb', 'ReportServerTempDB') AND
            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
        ORDER BY [name]
END
ELSE IF @dbType = 'System'
BEGIN
    IF @version = 8
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.dbo.sysdatabases
        WHERE [name] IN ('master', 'model', 'msdb')
        ORDER BY [name]
    ELSE
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.sys.databases
        WHERE [name] IN ('master', 'model', 'msdb')
        ORDER BY [name]
END
ELSE IF @dbType = 'User'
BEGIN
    IF @edition = 3 AND @version = '8'
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.dbo.sysdatabases
        WHERE 
            [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
            [name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases) AND
            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
        ORDER BY [name]
    ELSE IF @edition = 3 AND @version = '9'
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.sys.databases
        WHERE 
            [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
            [name] NOT IN (SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases) AND
            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
        ORDER BY [name]
    ELSE IF @version = '8'
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.dbo.sysdatabases
        WHERE 
            [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
        ORDER BY [name]
    ELSE
        INSERT INTO #WhichDatabase (dbName)
        SELECT [name]
        FROM master.sys.databases
        WHERE 
            [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
            DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
            DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
        ORDER BY [name]
END
ELSE -- no databases to be backed up
BEGIN
    SET @rc = 9
    GOTO EXIT_ROUTINE
END

-- Remove snapshots
IF @version = '9'
    DELETE t
    FROM #WhichDatabase t 
    INNER JOIN master.sys.databases d
    ON t.dbName = d.[name]
    WHERE d.source_database_id IS NOT NULL

-- Get the database to be backed up
SELECT TOP 1 @dbName = dbName
FROM #WhichDatabase

SET @rowCnt = @@ROWCOUNT

-- Iterate throught the temp table until no more databases need to be backed up
WHILE @rowCnt <> 0
BEGIN 

    IF @bkpType = 'TLog' AND @dbType IN ('All', 'User') AND DATABASEPROPERTYEX(@dbName, 'RECOVERY') = 'SIMPLE'
        PRINT 'Skipping transaction log backup of ' + @dbName
    ELSE IF @bkpType = 'Diff' AND @dbName IN ('master', 'model', 'msdb')
        PRINT 'Skipping differential backup of ' + @dbName
    ELSE
    BEGIN
        -- Build the dir command that will check to see if the directory exists
        SET @cmd = 'dir ' + @path + @dbName
    
        -- Run the dir command, put output of xp_cmdshell into @result
        EXEC @result = master..xp_cmdshell @cmd, NO_OUTPUT
    
        -- If the directory does not exist, we must create it
        IF @result <> 0
        BEGIN
            -- Build the mkdir command        
            SET @cmd = 'mkdir ' + @path + @dbName
    
            -- Create the directory
            EXEC master..xp_cmdshell @cmd, NO_OUTPUT
    
            IF @@ERROR <> 0
            BEGIN
                SET @rc = 10
                GOTO EXIT_ROUTINE
            END
        END
        -- The directory exists, so let's delete files older than two days
        ELSE IF @retention <> -1
        BEGIN
            -- Stores the name of the file to be deleted
            DECLARE @whichFile VARCHAR(1000)
    
            CREATE TABLE #DeleteOldFiles(DirInfo VARCHAR(7000))
    
            -- Build the command that will list out all of the files in a directory
            SELECT @cmd = 'dir ' + @path + @dbName + ' /OD'
    
            -- Run the dir command and put the results into a temp table
            INSERT INTO #DeleteOldFiles
            EXEC master..xp_cmdshell @cmd
    
            -- Delete all rows from the temp table except the ones that correspond to the files to be deleted
            DELETE FROM #DeleteOldFiles
            WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%<DIR>%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - @retention
    
            -- Get the file name portion of the row that corresponds to the file to be deleted
            SELECT TOP 1 @whichFile = SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) 
            FROM #DeleteOldFiles        
    
            SET @rowCnt = @@ROWCOUNT
            
            -- Interate through the temp table until there are no more files to delete
            WHILE @rowCnt <> 0
            BEGIN
                -- Build the del command
                SELECT @cmd = 'del ' + @path + + @dbName + '\' + @whichFile + ' /Q /F'
                
                -- Delete the file
                EXEC master..xp_cmdshell @cmd, NO_OUTPUT
                
                -- To move to the next file, the current file name needs to be deleted from the temp table
                DELETE FROM #DeleteOldFiles
                WHERE SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))  = @whichFile
    
                -- Get the file name portion of the row that corresponds to the file to be deleted
                SELECT TOP 1 @whichFile = SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) 
                FROM #DeleteOldFiles
            
                SET @rowCnt = @@ROWCOUNT
            END
            DROP TABLE #DeleteOldFiles
        END
        -- Get the current date using style 120, remove all dashes, spaces, and colons
        SET @now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')
    
        SET @extension =
            CASE
                WHEN @bkpType = 'Full' THEN '.BAK'
                WHEN @bkpType = 'TLog' THEN '.TRN'
                ELSE '.DIF'
            END
    
        -- Build the backup path and file name, backup the database
        IF @liteSpeed = 'N'
        BEGIN
            SET @fileName = @path + @dbName + '\' + @dbName + '_' + @now + @extension
            IF @bkpType = 'FULL'
                BACKUP DATABASE @dbName
                TO DISK = @filename
                WITH INIT
            ELSE IF @bkpType = 'DIFF'
                BACKUP DATABASE @dbName
                TO DISK = @filename
                WITH INIT, DIFFERENTIAL
            ELSE
                BACKUP LOG @dbName
                TO DISK = @filename
                WITH INIT    
        END
        ELSE
        BEGIN
            SET @fileName = @path + @dbName + '\' + @dbName + '_LS_' + @now + @extension
    
            DECLARE @regOutput varchar(20) -- stores the output from the registry
            DECLARE @numProcs INT -- stores the number of processors that the server has registered

            -- Get the number of processors that the server has
            EXEC master..xp_regread 
                  @rootkey = 'HKEY_LOCAL_MACHINE', 
                  @key = 'SYSTEM\CurrentControlSet\Control\Session Manager\Environment\',
                  @value_name = 'NUMBER_OF_PROCESSORS',
                  @value = @regOutput OUTPUT
            
            --  We want n - 1 threads, where n is the number of processors
            SET @numProcs = CONVERT(int, @regOutput) - 1
    
            IF @bkpType = 'FULL'
                EXEC master.dbo.xp_backup_database
                    @database = @dbName,
                    @filename = @fileName,
                    @threads = @numProcs,
                    @init = 1
            ELSE IF @bkpType = 'DIFF'
                EXEC master.dbo.xp_backup_database
                    @database = @dbName,
                    @filename = @fileName,
                    @threads = @numProcs,
                    @init = 1,
                    @with = 'DIFFERENTIAL'
            ELSE
                EXEC master.dbo.xp_backup_log
                    @database = @dbName,
                    @filename = @fileName,
                    @threads = @numProcs,
                    @init = 1
        END
    END
        -- To move onto the next database, the current database name needs to be deleted from the temp table
        DELETE FROM #WhichDatabase
        WHERE dbName = @dbName
    
        -- Get the database to be backed up
        SELECT TOP 1 @dbName = dbName
        FROM #WhichDatabase
    
        SET @rowCnt = @@ROWCOUNT
        
        -- Let the system rest for 5 seconds before starting on the next backup
        WAITFOR DELAY '00:00:05'
END

SET @rc = 0

EXIT_ROUTINE:

IF @rc <> 0
BEGIN
    DECLARE @rm varchar(500)
    DECLARE @error table (returnCode int PRIMARY KEY CLUSTERED, returnMessage varchar(500))

    INSERT INTO @error(returnCode, returnMessage)
    SELECT  0, 'Success' UNION ALL
    SELECT  1, 'Version is not 2000 or 2005' UNION ALL
    SELECT  2, 'Invalid option passed to @dbType' UNION ALL
    SELECT  3, 'Database passed to @dbType does not exist' UNION ALL
    SELECT  4, 'Invalid option passed to @bkpType' UNION ALL
    SELECT  5, 'Only full backups are allowed on system databases' UNION ALL
    SELECT  6, 'Invalid option passed to @liteSpeed' UNION ALL
    SELECT  7, 'Can not backup tlog when using SIMPLE recovery model' UNION ALL
    SELECT  8, 'Will not backup the tlog on a log shipped database' UNION ALL
    SELECT  9, 'No databases to be backed up' UNION ALL
    SELECT 10, 'Unable to create directory' UNION ALL
    SELECT 11, 'Can not backup database snapshots'

    SELECT @rm = returnMessage 
    FROM @error 
    WHERE returnCode = @rc

    RAISERROR(@rm, 16, 1)
END

RETURN @rc

posted @ Wednesday, May 21, 2008 10:05 AM | Feedback (16)

Powered by: