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.
Last weekend I attended SQL Saturday in Chicago.
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.
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.
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.
1 comment
No trackbacksWe 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.
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.
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.
Write a commentNo trackbacks
Delicious
Digg
Furl
Magnolia
Reddit
Slashdot
Stumbleupon
Technorati Favorites
Twitter
.
.
.