How to change the owner of database in SQL Server

In this article, we will see how to change the owner of database in SQL Server with different methods.

We following three methods to change SQL Server database owner name:
1. Using SQL Server Management Studio.
2. Using ALTER AUTHORIZATION command.
3. Using system stored procedure.

Permissions required to change owner of the database:
Requires TAKE OWNERSHIP permission on the database.

Change the owner of database in SQL Server using SSMS:
Continue reading How to change the owner of database in SQL Server

How to set database in single user mode on SQL Server

In this article, we will see how to set database in single user mode on SQL Server by using GUI method and SQL Statement.

Why single user mode?
1. Database Single-user mode specifies that only one user at a time can access the database and is generally used for maintenance tasks.

2. If other users are connected to the database at the time that we want set the database to single-user mode, their connections to the database will be closed without warning.

Permissions Required to set single user for database in SQL Server:
Continue reading How to set database in single user mode on SQL Server

How to rename a database in SQL Server

In this article, we will see how to rename a database in SQL Server by using QL Server Management Studio or Transact-SQL.

1. Renaming system databases are not allowed.
2. It is best practice to set a database in single user mode to close any open connections.
3. User must be granted ALTER permission on the database which is going to be renamed.

Rename database in SQL Server using SQL Server Management Studio:
Continue reading How to rename a database in SQL Server

How to add Data or Log Files to a Database in SQL Server

In this article, we will see how to add Data or Log Files to a Database in SQL Server step by step.

Permissions required:

Requires ALTER permission on the database.

Limitations and Restrictions

1. We cannot add or remove a file while a BACKUP statement is running.
2. A maximum of 32,767 files and 32,767 filegroups can be specified for each database.

Method1: Using SQL Server Management Studio
1. Connect to SQL Server Instance and expand Databases, right-click the database from which to add the files, and then click Properties.
Continue reading How to add Data or Log Files to a Database in SQL Server

How to change default backup location in SQL Server

In this article, we will see how to change default backup location in SQL Server.

When you install a SQL Server instance you have the opportunity to identify the default location for your instance database backups. If you don’t change the default location for your installation your default backups location will be “C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQL2019\MSSQL\Backup” (default location for a SQL Server 2019 installation). Overtime you might decide you want to change the default location for database backups.

Two different ways to change default backup location in SQL Server:
Continue reading How to change default backup location in SQL Server

How to change default data file and transaction log files location in SQL Server

In this article, we will see how to change default data file and transaction log files location in SQL Server.

By default database files are stored in DATA folder of respective instance.
It is always best practice to to maintain data and transaction log files in different disks in order to get the following advantages:

1. Minimize data loss.
2. High Performance.
3. To avoid disk space regular issues.
4. To avoid page corruption issues.

Best Practices:
Continue reading How to change default data file and transaction log files location in SQL Server