MSSQL - Deattach and attach manually by commands

• sp_helpdb smrtest

• --Set the database to a single user mode from multiuser mode

USE [master]

GO

ALTER DATABASE [smrtest] 

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

• 
  

--Detach the database using the sprocs

• 
  

USE [master]
GO
CREATE DATABASE [smrtest1] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\smrtest.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\smrtest_log.ldf' )
 FOR ATTACH --<<use for attach to attach the sales database
GO

MSSQL - Detach and Attach database using GUI

1



2



3



4



5



6





############ ATTACH #######################

1



2



3



4

MSSQL - Change tempdb mdf and ldf file location

sp_helpdb tempdb




USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\Tempdb.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\tempdb_mssql_8.ndf');
GO
USE master;
GO

###########################ALSO  ##########################################

We cant change recovery mode from simple to full in temp db





USE [master]
GO
ALTER DATABASE [tempdb] SET RECOVERY FULL
GO

--Sizing the data and log files and autogrowth property

USE [master]

GO

ALTER DATABASE [tempdb]

MODIFY FILE ( NAME = N'tempdev', SIZE = 102400KB , FILEGROWTH = 20480KB )--<< pre sizing data, auto growth

GO

ALTER DATABASE [tempdb]

MODIFY FILE ( NAME = N'templog', SIZE = 20480KB , FILEGROWTH = 20480KB )--<< pre sizing log, auto growth

GO