Wednesday, August 18, 2010

Automating SQL 2005/2008 Express Backup in 5 Easy Steps

I was recently asked to backup all system and user databases on a SQL 2005 Express installation. Unfortunately, SQL 2005 Express native backup capabilities are not on par with SQL 2005 Standard/Enterprise. SQL Express includes functionality for backing up a single DB using SQL Server Management Studio Express, but this is only ideal for backing up a single database and it cannot be scheduled. So here are the steps for automating this process.  If you’d like more detailed information, check out this much more thorough article at http://www.sqldbatips.com/showarticle.asp?ID=27.

Step 1:

Save the lines here in a file called expressmaint.sql to C:\SQLScripts.

Step 2:

Open an administrator command prompt to “c:\SQLScripts\” and run “sqlcmd -S .\SQLExpress -i c:\sqlscripts\expressmaint.sql”.

image

Step 3:

Save the lines below in a file called sqlbackup.sql and save it in C:\SQLScripts.  Be sure to change the variable contents for @reportfldr to whatever is appropriate for your environment.

exec expressmaint
   @database      = '$(DB)',
   @optype        = 'DB',
   @backupfldr    = '$(BACKUPFOLDER)',
   @reportfldr    = 'c:\SQLScripts',
   @verify        = 1,
   @dbretainunit  = '$(DBRETAINUNIT)',
   @dbretainval   = '$(DBRETAINVAL)',
   @rptretainunit = 'copies',
   @rptretainval  = 2,
   @report        = 1

Step 4:

Save the lines below in a file called sqlbackup.cmd and save it in C:\SQLScripts.  Be sure to change “.\SQLExpress” to the name of your SQL Express server and instance” and don’t forget to change the BACKUPFOLDER location to whatever is relevant in your environment.  NOTE: Each “sqlcmd” begins a new line.

sqlcmd -S .\SQLExpress -i"c:\SQLScripts\sqlbackup.sql" -v DB="ALL_USER" -v BACKUPFOLDER="e:\SQLBackups" -v DBRETAINUNIT="days" -v DBRETAINVAL="1"

sqlcmd -S .\SQLExpress -i"c:\SQLScripts\sqlbackup.sql" -v DB="ALL_SYSTEM" -v BACKUPFOLDER="e:\SQLBackups" -v DBRETAINUNIT="days" -v DBRETAINVAL="1"

If you were to run the 2nd sqlcmd above your output would be similar to this:

image

Step 5:

Create a scheduled task to run “C:\SQLScripts\sqlbackup.cmd” at your desired backup frequency.

Friday, August 06, 2010

Windows System State Analyzer

“The basic functionality of the System State Analyzer tool is to allow you to compare two snapshots taken at different points in time. This allows you to compare the state of a machine both before and after an application install for instance.”  Read more here.

Killing a hung service

Today I came across a system that was failing to heartbeat .  Opening services.msc on the box revealed that the System Center Management service status was “Starting”.   Stopping the service from services.msc didn’t work, so I had to resort to a more forceful option.

Here’s how I did it.

  1. Open a command prompt
  2. Query for the state and PID by entering “sc queryex healthservice”
    image
  3. Now that we have the PID, we can use the Taskkill command by running “Taskkill /PID /1168 /F.  The /F switch will forcefully terminate our process.  
    image

Now you can start the HealthService.

Monday, November 23, 2009

Adding SCOM R2 Report Models

SCOM R2 includes 2 new report models for Events and Performance.  For direction on how to add these models have a look at http://blogs.technet.com/momteam/attachment/3222196.ashx

Technorati Tags:

Sunday, November 15, 2009

New Tool: MDT 2010 Build-out/Hydration Evaluation

If you’re interested in checking out MDT 2010 you should take a look at the  New Tool: MDT 2010 Build-out/Hydration Evaluation from Xtreme Consulting Group.  This awesome tool was builds you out a complete test environment in Hyper-V with almost no effort at all.

Wednesday, April 04, 2007

Ask the Performance Team : Memory Management - Understanding Pool Resources

Memory Management - Understanding Pool Resources is a great blog post for understanding Pool Resources and Pool Resource Depletion in Windows 2000 and 2003 32-bit Operating Systems.

Wednesday, March 28, 2007

Error message after you install the Systems Management Server 2003 site database on Microsoft SQL Server 2005: "Message ID : 619"

As some of your may recall, there was a discussion on myitforum in August of last year regarding a Message ID 619 for SMS 2003 installs running on SQL Server 2005. I posted about this at the time and the fix that PSS offered, but forgot to update my one or two readers when the KB was updated in November. So here it is... KB923116 updated.

TechNet Magazine Active Directory Component Jigsaw Poster

This cool poster was featured in March-April 2006 edition of TechNet Magazine. Check the TechNet Magazine Active Directory Component Jigsaw Poster out for yourself.

Thursday, August 31, 2006

ACL Permission Error from IIS when installing SUS 1.0 on Windows 2003 SP1 server

After installing Software Update Services (SUS) 1.0 w/SP1 on my Windows 2003 Standard Server w/SP1 I was unable to view the SUSadmin webpage. The error message

"You are not authorized to view this pageYou do not have permission to view this directory or page due to the access control list (ACL) that is configured for this resource on the Web server. "

To fix this, navigate to Default Web Site-->SUSAdmin via IIS Manager and change the authentication methods for the SUSAdmin site to use Integrated Windows authentication. Refesh the SUSadmin page and you should now see the admin site.

Tuesday, August 29, 2006

Error message after you install the Systems Management Server 2003 site database on Microsoft SQL Server 2005: "Message ID : 619"

For those of you that have been experiencing a backlog of .sql files in Inboxes\Statmgr.box\Retry and seeing the error below, take a look at KB923116. You will need to call Microsoft Support to get the necessary changes (or take a look at this thread). Microsoft has told me that a hotfix should follow shortly.

*** [42000][102][Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'd'. : sp_InsStatusMessageAttribute SMS_STATUS_MANAGER date time 4144 (0x1030) ERROR: Could not add a SQL command to the transaction. Most likely, there is a problem with the SQL server or our connection to it. The transaction will be rolled back. SMS_STATUS_MANAGER date time 4144 (0x1030)

Changes to the WSUSScan.cab file

For better or worse the WSUSScan.cab is changing. In an attempt to reduce size, all superseded patches will now be removed because the maximum cab file size has been reached. The reduction in size should come as a welcomed relief to all those that have been seeing CPU levels holding steady at 100% during SMS ITMU or Windows/Microsoft Update scanning.

The question right now becomes how customers that require the superseded patches (because the current version breaks LOB apps) will be able to scan for the superseded patches.

For more info, http://support.microsoft.com/kb/924513.

Wednesday, August 23, 2006

TechNet Magazine for FREE

Technet magazine is a great resource for MOM, SMS, and Windows System Administrators and best of all it's free.

Subscribe to TechNet Magazine

Customize SMS 2003 with Local Policies

There was a recent thread on Myitforum.com regarding the use of local policies. For more information, take a look at the SMS 2003 SDK or have a look at this recent article in Techet Magazine, Customize SMS Using Local Policies -- TechNet Magazine, September 2006.

Microsoft Virtual Server support policy

If you are wondering whether or not yoru Microsoft Server app is supported on MS Virtual Server, take a look at the Microsoft Virtual Server support policy.

Wednesday, August 02, 2006

ITMU Updates Fail when Scan Tool fails to Run

Brian Tucker : ITMU Updates Fail when Scan Tool fails to Run

Take a look at Brian's blog if you are having issues with the SMS 2003 ITMU scan tool failing on clients.

How MS does patch management

Taken from Rod Trent's Blog...

Thanks to Paul Thomsen at Microsoft for posting these to the SMS email discussion list!
Main presentation – you have to sign in but that then leads to the live presentation, or the PowerPoint slides (or it should – it’s busted now), or the offline viewing download
WMA – voice-over only (podcast, I suppose you could say)
MP3 – same thing but for devices that don’t support WMA
PowerPoint slidedeck direct download - you don’t have to sign in

Webcast: ADMP for MOM2005

TechNet Webcast: Active Directory Management Pack for Microsoft Operations Manager 2005 (Level 200)

If you have questions on the configuration of the AD Management Pack for MOM 2005, this webcast is worth viewing.