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