SQL Server Tip of the Day

DBAs often need to refresh database instances for developers.  In this case, you need to restore some database from a backup file.

Here are few simple commands to help with this task:

Determining what processes are using the database (Note the single quote around <database name>)

USE master

SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('<database name>')


Set database to SINGLE_USER (Note the double quotes around <database name>)

use master;

GO

ALTER DATABASE "<database name>" SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Detach database (Note the single quotes around <database name>)

EXEC sp_detach_db 'AdventureWorks2012', 'true';

Delete database (Note the doube quotes around <database name>)

use master;

GO

DROP DATABASE "<database name>";

GO

Once the database is deleted, you can use the restore GUI to restore the database using the original name.

See database file space usage 

USE <database name>
GO 

SELECT DB_NAME() AS DbName, 
name AS FileName, 
size/128.0 AS CurrentSizeMB,  
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files;

List recovery modes for all databases

SELECT name AS [Database Name],
recovery_model_desc AS [Recovery Model]
FROM sys.databases
GO

Change dbo for a database

EXEC sp_changedbowner '<databasename>'