|
#
|
Offline
|
Detach
|
Emergency
|
|
1
|
Taking the database offline will make the database unavailable. But
the details of the database and its files are still intact in master database
|
Detaching the database will remove the database details completely
from the SQL server leaving the data, log and other files independent.
|
When the database state is set to emergency, the database is moved to
read_only single-user mode.
|
|
2
|
Once offline, You can see the reference of the database in SSMS object
explorer. In object explorer, the offline database name is prefixed by a red
down arrow icon.
|
You won’t see any reference of the detached database anywhere in the
SSMS including object explorer
|
Database in emergency mode is represented by the red database Icon
before the database name in SSMS object explorer
|
|
3
|
Details of the offline database are intact in master..sysdatabases.
|
Once detached the details of the database are removed from
master..sysdatabases.
|
Details of the emergency mode database are intact in
master..sysdatabases.
|
|
4
|
Users and application cannot access the database.
|
Users and application cannot access the database.
|
Only member of sysadmin can access the database in read-only mode.
|
|
5
|
After taken offline, you cannot delete the mdf and sql files as it is
still linked to the SQL server.
|
Detached databases files like mdf, ldf, etc… can be deleted as they
are detached from SQL Server.
|
After setting the database to emergency mode, you cannot delete the
mdf and sql files as it is still linked to the SQL server.
|
|
6
|
Offline database can be easily brought back online. You don’t need to
tell the file locations to bring back it online.
|
To attach a database again, you need to specify the location of the
files in the attach statement.
|
Emergency state database can be easily brought back online without
specifying the file location.
|
|
7
|
Usage:
Taking the database offline is a quick way to make it unavailable to
any one or any application. For example, if you want to prevent all the users
from connecting the database for a short time then taking it offline will be
the easiest option.
|
Usage:
Detaching a database is normally useful for moving a database
permanently from one SQL Server or instance to another server or instance.
|
Usage:
Moving the database to emergency mode is primarily for troubleshooting
purpose. It helps to avoid the regular users and applications from accessing
the database. But the sysadmin can access it in read-only mode for troubleshooting
and repairing.
|
|
8
|
T-SQL Syntax For Taking Database
Offline:
ALTER DATABASE [Database-Name] SET OFFLINE
GO |
T-SQL Syntax For Detaching
Database:
Exec dbo.sp_detach_db ‘Database-Name’, ‘true’;
GO |
T-SQL Syntax For Moving Database To
Emergency Mode:
ALTER DATABASE [Database-Name] SET EMERGENCY
GO |
Q: What is SSRS? Ø SSRS or SQL Server Reporting Service is a server-based report generation software systems from Microsoft and is part of Microsoft BI. Ø It is used for preparing and delivering interactive and variety of reports. Ø It is administered through an web based interface. Ø Reporting services utilizes a web service interface for supporting and developing of customized reporting applications. Ø SSRS lets you create very rich reports (Tabular/Graphical/Interactive) from various datasources with rich data visualization (Charts, Maps, sparklines) Ø SSRS allows are reports to be exported in various formats (Excel, PDF, word etc) Q: Explain SSRS Architecture? Reporting services architecture comprises of integrated components. It is a multi-tiered, included with application, server and data layers. This architecture is scalable and modular. A single installation can be used across multiple computers. It includes the fo...
Thank you for your valuable information,Keep doing your best.
ReplyDeleteMSBI Online Training India
Thank you for your valuable feedback
DeleteI really liked your blog post.Much thanks again. Awesome.
ReplyDeleteMsbi Course
Msbi Training