Backing up all DBs using sp_msforeachdb

This will be the first in a series of post on a couple of my new favorite undocumented commands. I’m talking about sp_msforeachdb and sp_msforeachtable. These guys are awesome.

Recently I needed to backup all the databases on a SQL 2000 server. I could have created a maintenance plan and just used it as a one-off but I figured I’d get a little creative. I used sp_msforeachdb to back each DB up.

Check it out:

1
2
3
4
5
6
7
8
9
10
11
12
13
EXEC Sp_msforeachdb
' IF DB_ID(''?'') > 4
 Begin
BACKUP DATABASE [?] TO  DISK = N''\\server\Backups\?.BAK'' WITH NOFORMAT,
 INIT,  NAME = N''?-Full Database Backup'',SKIP, NOREWIND, NOUNLOAD
declare @backupSetId as int
 select @backupSetId = position from msdb..backupset where database_name=N''?''
and backup_set_id=(select max(backup_set_id)
from msdb..backupset  where database_name=N''?'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''?'''' not found.'', 16, 1)
end
RESTORE VERIFYONLY FROM  DISK = N''\\server\Backups\?.BAK''
WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND End '
Share

Managing your SQL Server logs

“I am getting an error. Something’s broke. My application works fine. It’s the database.”

We’ve heard that guy more than once. So what do you do? I usually open up my box of SQLs and check out the logs.

This is usually a pretty simple process. Open the management folder, open the SQL Server Logs folder, double click your log. It’s a nice place to start.

Now what happens when you have 500,000 records in there? It takes FOREVER!

Luckily SQL 2011 (Denali) has a feature in there to stop the whole log from loading. If you aren’t on SQL 2011 (Denali) then you have to sit there and wait…and wait…and wait…

I recently found this beauty – EXEC MASTER.sys.Sp_cycle_errorlog

This boots your current log to second place and creates a new one. Now you have a nice clean fresh log to fill up.

So what happens to the logs? Well the SQL default is to keeps 6. Almost every time you open up that folder, you see Current + 6 old ones. After 6, they are recycled.

You are able to configure the amount of logs you keep too, between 6 and 99. Just right click on the ‘SQL Server Logs’ –> Configure.

SQL creates a new log every time the server is restarted. For most people, this is way too far apart. On my servers, I set up a weekly job that run this command. That way I know I have 1 week worth of data in each of my logs.

Share

Fixing Orphaned Users

One my favorite commands when I was started my career was Sp_changeuserslogin. I had to move a lot of databases around and I orphaned a lot of users. This command really helped me find what users were orphaned and reconnect them with the appropriate database.

When should you use this command?

This command is very useful when you have a user in your database and a login on your server that should be connected (or mapped) but are not.

To find out if you have an oprhaned users run:

EXEC Sp_change_users_login ‘report’

This will give you a list of orphaned users in your DB.

If you find you have some orphaned users you can run this command to fix them:

EXEC Sp_change_users_login
‘auto_fix’,
‘YourUser’

It’s a handy tool to use but BOL warns avoiding it because it will be going away soon. They suggest using ALTER USER instead.

ALTER USER YourUser WITH LOGIN = ‘YourUser’

Share

Query to find Duplicate records in a column

We are going to start SQL sandwiches with a tasty little snack. I have had this asked in an interview before. “What is an easy way to find duplicate records in one table?”

The answer is quite easy yet it can stump anyone who hasn’t done it before.

It’s a simple select statement that you can use for multiple columns.

1
2
3
4
5
SELECT column1,
COUNT(column1)
FROM table1
GROUP BY column1
HAVING COUNT(column1) > 1

You can add other columns in there if you are looking for a record that has multiple columns that are the same.

 

 

Share

Welcome to SQL Sandwiches!

Hi,

My name is Adam and I am a DBA. Welcome to my kitchen of delicious SQL tips, tricks, and whatever else I can offer you. This blog is for me – to document my experiences with SQL, for you – maybe you can learn my mistakes or view something from a different angle, for the community – the more useful information out there, the better (sometimes), and for Ween – because they are awesome.

Like most DBAs, I didn’t always dream of working in the world of data. One day I was handed a terribly overdue project and was working nights. The next thing I know I am restoring backups, then looking at stored procedures, then the next – I am a certified DBA. Maybe not that fast but you get the idea.

So feel free to leave comments if you find something helpful, if you find mistakes in my work, or just to say Ween is awesome.

Enjoy!

 

Share