By default, SQL Server system objects are listed in Object Explorer in Management Studio. These system objects include system database, system tables/views/procedures and so on.
SQL Server Management Studio provides an option to hide these objects from Object Explorer to prevent *accidental* use.
To enable this option follow below steps.
Step1: Go to Tools > Options
Step2: Navigate to Environment > Startup
Step3: Check Hide system objects in Object Explorer and click OK:
Step4: An confirmation message will be displayed saying that changes will take effect after Management Studio is restarted, click on OK to continue:
Once you restart SQL Server Management Studio, you will notice that system objects are no longer listed in Object Explorer:
As you can see from above screenshot that System databases node is no longer available in Object Explorer.
What is hidden by this setting:
1. System databases – This is not hidden for SQL Server 2012
2. System Stored Procedures
3. System Tables
4. System Views
Hope This Helps!
By default SQL Server ERRORLOG is stored in "C:\Program Files\Microsoft SQL Server\InstanceFolder\MSSQL\Log" folder. The ERRORLOG location is configured as a startup parameter for SQL Server Service.
To change the location of ERRORLOG you need to modify the startup parameter -e.
For example, if you need to move logs to C:\Logs\ then replace the startup parameter as below:
Replace "-eC:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Log\ERRORLOG"
Restart SQL Server Service to apply changes. The ERRORLOG will be created to "C:\Logs" after SQL Server is restarted.
To change location for SQL Server Agent Logs:
Location for SQL Server Agent Log can be changed using T-SQL or SQL Server Management Studio.
Method 1: To change SQL Server Agent Log location right click on "Error Logs" node and select "Configure"
Provide new location for Log file in "Error Log File" path:
Click on "OK" and restart SQL Server Agent to apply changes.
Method 2: To change log location using T-SQL
To change log location using T-SQL you can use below code:
@errorlog_file = N'C:\Logs\SQLAGENT.OUT'
Execute this code and restart SQL Server agent to apply changes.
Hope This Helps!
If you have lost the password for sa account or does not have any administrative account and you are locked out of SQL Server you can still login to SQL Server as an Administrator using Local administrator account.
You can use any account which is part of local Administrators group. To be able to login using local admin account you need to stop and start SQL Server in single user mode.
Using below method you can restore access to your server by resetting forgotten administrator password or by creating a new Administrative account in SQL Server.
We need to perform below steps to create new administrator account in SQL Server
Step 1. Stop SQL Server from Services Console or SQL Server Configuration Manager
Step 2. Start SQL Server in single user mode using -m startup parameter
You can add -m switch as a startup parameter using SQL Server Configuration Manager or You can start SQL Server in single user mode from command prompt using below command:
C:\> sc start MSSQLSERVER -m
Replace MSSQLSERVER with service name on your system
Before you start SQL Server in single user mode make sure SQL Server Agent is disabled
Step 3. Connect to SQL Server using SQLCMD or SQL Server Management Studio
To connect to SQL Server when running in single user mode using SQL Server Management Studio use "Database Engine Query" from File Menu > New option *
* You cannot connect using Object Explorer while SQL Server is running in single user mode as Object Explorer requires 2 separate connections to server
To connect to SQL Server using SQLCMD, run below on command prompt:
C:\>SQLCMD -S (local)
Step 4. Create New Administrator account or reset password for an existing account
When connected to SQL Server using SQLCMD or SQL Server Management Studio execute below script to create a new administrator account:
1> CREATE LOGIN [NewAccount] WITH PASSWORD = N'Welcome',
2> DEFAULT_DATABASE = [master],
3> CHECK_EXPIRATION = OFF,
4> CHECK_POLICY = OFF
2> ALTER SERVER ROLE [sysadmin]
3> ADD MEMBER [NewAccount]
To Reset password of an existing account use below code:
1> ALTER LOGIN [ExistingAccount] WITH PASSWORD = N'Welcome'
Replace [ExistingAccount] with login name for which you want to reset the password, and replace 'Welcome' with new password, make sure new password meets the security policies
Step 5. Stop SQL Server Service using Services Console or SQL Server Configuration Manager
Step 6. Start SQL Server Service normally using Services Console or SQL Server Configuration Manager
Now you can login to SQL Server using the newly created Administrator account.
Hope This Helps!
To move a database from one location to other you fist need to detach the database from server. In this article we will learn different ways to detach a database from server.
There are two different methods available to detach a database from a server.
Method 1. Detach a Database using "Detach" Task in SQL Server Management Studio:
To detach a database using SQL Server Management Studio:
1. Right Click on Database you want to detach and Select "Tasks" > "Detach" Option.
2. Check the "Drop Connections" checkbox and click "OK" to detach the database
Now the database is detached from server and you can move .mdf and .ldf files related to database.
Method 2: Detach database using T-SQL:
To detach a database from a server using T-SQL you can use below code:
ALTER DATABASE [SqlAndMe] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC master.dbo.sp_detach_db @dbname = N'SqlAndMe'
Command(s) completed successfully.
This detaches database from server.
Hope This Helps!