Find the last time all databases were used

select stat.database_id as database_id ,db.name as database_name ,max(stat.last_user_scan) as last_user_scan from sys.dm_db_index_usage_stats as stat join sys.databases as db on db.database_id = stat.database_id group by stat.database_id,db.name order by db.name asc

Can I upgrade my SQL Server 2008 R2 Trial to a full version?

If you are a GUI person just launch the SQL Server setup and go to the “Maintenance Tab”, you will see the “Edition Upgrade” option there. Follow a couple of steps in the wizard, provide the key and you are all set.

Developers have no access to linked servers after creating one

 So, you just created a linked server back to DB2 on MS SQL Server 2008R2. However, the developer calls you and says I getting this error;
 The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (.Net SqlClient Data Provider)

This script assumes the user is basis_admin
use master
go
create user basis_admin for login basis_admin
go
grant exec on sys.xp_prop_OLEDB_provider to basis_admin

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

I just want to add MS 2008 R2 steps: Tools > Options > Designers > Table and Database Designers then uncheck Prevent saving changes that require table re-creation

How to create an SSIS package to import data

Open BIDS click new project >click Control Flow> Data flow > add source object and then add destination object. Next you can run the project or save it.

Msdb cannot be restored

Ever get stuck restoring a master database from a higher version of SQL server than the current one you just built? Well the post will help you.

Problem:
"Restore failed -The backup of the system database on device
msdb cannot be restored because it
was created by a different version of the server (134218407) than this server
(134218262)."

Solution :
bring the system(sql server) up to date with the hotfixes and service packs...

Suspect databases are not cool

Dba days are always fun. I came in Monday morning to a list of suspect databases. I think this was a SAn issue. But, I still can not prove it. This is what i did to repair them.

Step 1:

Use Master
Go
Sp_resetstatus 'Database Name'
--The above command will reset the status flag of the suspect database.

Step 2:-- Once the command is executed you need to restart the SQL services and check
the status of the database.

Step 3: Execute the below query to check the integrity of the database

Dbcc Checkdb('Database Name')
Step 4: None of these worked, so I ended up restoring the database from a previous day.

Rename a Computer that Hosts a Stand-Alone Instance of SQL Server

Have you ever renamed a windows server after installing SQL server on it? Well, sql still holds the old name if you run select @@servername.
So, there are a few steps to follow after the rename. First, allow master (system database ) to see the new default instance name. Second, restart the database engine.

Open new query windows or sqlcmd:

use master
go

sp_dropserver
GO
sp_addserver , local
GO

Cannot delete job | The DELETE statement conflicted with the Foreign

I got a call from user today who was trying to delete a job from sql agent. The problem is that the maintenance plan still exist. So, you should delete the parent( Maintenance plan) before attempting to delete the child (JOB). However, Microsoft doesn't give you a clear error.

Cannot delete job The DELETE statement conflicted with the Foreign Key-- Read more: http://www.sqldbadiaries.com/2010/11/11/cannot-delete-job-the-delete-statement-conflicted-with-the-foreign-key/#ixzz1qzPlu15d

MS SQL 2008 Standard does not support database compression

I thought is was a normal morning at the office . So, I check job history on all my sql instances and noticed that FULL backups were failing from the night before. Why you ask? Well , I added the compression as default to my sql server boxes backup plans the night before.

Microsoft doesnt even give you an error when adding compression to maintenance plans.

Moral of the story, dont trust that backups are working. Check them every day. You might say , just add a notification to task for failure. However, what if your SMTP is having issues also.

Check for errors in your jobs.

Change the database name

alter database tmsng1
set single_user
go
USE master;
GO
ALTER DATABASE tmsng1
Modify Name = tmsng
GO
use master
go
alter database tmsng
set multi_user

Create a timestamp updatable column

SQL DBA's --have you ever create a column that holds modified time. Yes. Well it probably held the inserted time. It didnt work for the updated time. This little script will work for you very well.

create TRIGGER [dbo].[Account_UTrig] ON [dbo].[Account]
FOR INSERT, UPDATE
AS

UPDATE Account
SET Account.ts = getdate()
FROM Account INNER JOIN INSERTED ON Account.[AccountID] = INSERTED.[AccountID]