SQLLawman

Family man and SQL Geek

Restoring Last Full Backup

By MichaelDeputy, 9 days ago

I ran into a small problem where I needed to automate a restore of my last full backup to a different database.  Since my full backup file names include a date/time stamp I needed to query msdb for the backup name.  Since I figured it out, I thought I'd post it so I would know where of find it the next time I need it, and maybe help someone else out in the process.

Keep reading →

DBCC Checkdb

By MichaelDeputy, 4 months and 15 days ago

Last weekend I attended SQL Saturday in Chicago.

Keep reading →

Find locked SQL accounts on SQL 2005

By MichaelDeputy, 1 year ago

We are in the process of upgrading a Microsoft product that just hasn't gotten around to integrating with AD and uses SQL accounts.  It also has a really bad habit of locking SQL accounts if your AD policy for failed login attempts is set low.  So I got tired of searching through SSMS to unlock accounts, so I wrote a quick script to list the locked accounts and the command to unlock it.  I didn't go as far as to auto-unlock the locked accounts but that would be a simple edit.  Here's the script.

Keep reading →

Find all Triggers

By MichaelDeputy, 1 year ago

I was asked to find all tables in a database that had a DML trigger so a developer could ensure that all the old functionality was available in the new database.  So once I built my query to give me the information, I thought I'd save it here for future use.

Keep reading →

SKU is Invalid when installing 2nd node in SQL 2008

By MichaelDeputy, 1 year and 1 month ago

I was installing SQL Server 2008 on a Windows 2008 cluster the other day and I received and error:  'The current SKU is Invalid'.  So I went looking for the resolution.  I came across  a blog post by Vittorio Pavesi that pointed me to the answer.  I did not take the course of copying the install files to the local server.  I was installing from a network share, so I simply moved the file  DefaultSetup.ini to a new location and started the 'Add node to a SQL Server failover cluster'  again and entered the key included on the DefaultSetup.ini manually.  The rest of the install went without issue.

Stored Procedure with Execute as

By MichaelDeputy, 1 year and 3 months ago

We have a database that holds information that is fairly personal, so much that the application team that supports it may only have read access to the database.  There came a need to occasionally lock all the administrators out of the system for updates.  These updates will occur every few months.  So I created a SQL user and gave that user select and update rights to the necessary table.  Then I created a couple of stored procs, one to disable the logins and one to enable the logins.

Keep reading →

Cleaning up the disk

By MichaelDeputy, 1 year and 5 months ago

When I became our companies MSSQL DBA I inherited an smooth running machine.  My predecessors were extremely talented and their scripts did everything we needed done.  But I can't just live off their hard work, I need to leave my mark on the environment.  So I am tweaking some of their scripts to get rid of what annoys me.  The current task is old backup files.  The scripts they wrote do this awesome thing that archives a backup if the archive bit has not been cleared.  This is so cool.  If NetBackup has a problem, the old backup file that hasn't gone to tape/disk is moved to an archive folder and then the new backup file is created.  But what happens when after the file is backed up and the archive bit is cleared.  It just sits on the disk and is backed up again with every full backup that occurs.  That makes the backups bigger and take longer.  So I added a job that deletes all *.bak files that have their archive bit cleared.  That was so much easier than I expected it to be.

Keep reading →

Developer can not create views

By MichaelDeputy, 1 year and 7 months ago

I received a e-mail a few days ago from one of our developers informing me that he is unable to create views on one of our SQL Server 2005 databases.  He also told me he had another developer try and she also could not create views.  Interesting, I thought, so out to my favorite co-worker - Google.  My buddy Google quickly pointed me to this MSDN post.  This explained to me that when a user gains rights by Active Directory group membership they are not given a default schema.  Say what? Yes, that's right, because multiple AD groups could have rights on a database, and a user could be a member of multiple AD groups it could get confusing.  So any statement that requires a schema will fail with some form of the following error message.

Keep reading →