Dataclub U.S.

IT for Your Success

DBA Basics Part 1

Shrink Database
DBCC SHRINKDATABASE (UserDB, 10); (shrink database to 10% of free space)

Rename a database
EXEC sp_renamedb N'Accounting', N'Financial'; (old and new name)

Check file group associated with a database
Sp_helpfilegroup

Change the database state
Check the state (user_access_desc, state_desc)
select * from sys.databases where name='dataclub'

alter database dataclub set emergency

alter database dataclub set single_user
alter database dataclub set multi_user

To bring it back from emergency to normal
alter databse dataclub set online

Force database offline
ALTER DATABASE dataclub
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

sp_who2

sp_who ā€˜mjiā€™

kill 71

Rename a database
ALTER DATABASE dbName MODIFY NAME = dbNewName

Or sp_renamedb ā€˜old nameā€™, ā€˜new nameā€™

Backup & Restore

Error: the database is in use
Get database in Single Mode
ALTER DATABASE [SBO_DataclubUS] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
OR
ALTER DATABASE [SBO_DataclubUS] SET SINGLE_USER WITH ROLLBACK AFTER 30
OR
ALTER DATABASE [SBO_DataclubUS] SET SINGLE_USER WITH NO_WAIT

Restore
USE [master]
BACKUP LOG [SBO_Dataclub] TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\SBO_Dataclub_LogBackup_2017-10-05_14-34-27.bak' WITH NOFORMAT, NOINIT, NAME = N'SBO_Dataclub_LogBackup_2017-10-05_14-34-27', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5

RESTORE DATABASE [SBO_DataclubUS] FROM DISK = N'D:\SAPB1\backup\SBO_Dataclub_171005.bak' WITH RESTRICTED_USER, FILE = 1, MOVE N'SBO_Dataclub' TO N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SBO_DataclubUS.mdf', MOVE N'SBO_Dataclub_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SBO_DataclubUS_log.ldf', NOUNLOAD, REPLACE, STATS = 5

GO

ALTER DATABASE [SBO_DataclubUS] SET MULTI_USER

Restore DATABASE [SBO_DataclubUS] with recovery

DBA Basics Part 1