How to Backup SQL Server Database

We can backup the SQL Serer database either with the T-SQL statements or we can use the SSMS wizard process to take full, differential, or transactional backup of the database.

Use one of the below options to backup the SQL Server database:

1. Backup Database using T-SQL

Launch SQL Server Management Studio and connect to the database server. Now open a query window and execute the following statement to back up the database in your local drive.

BACKUP DATABSAE [Test_db] TO DISK = 'D:\backup\Test_db.bak'
Go

Here Test_db is the database name and D:\backup\Test_db.bak is the backup file name with full path.

The above screenshot shows a successful database backup in the SQL server using a T-SQL statement. The next option will help you to backup the SQL Server database with a graphical interface.

2. Backup Database using SSMS

SQL Server Management Studio provides an option to backup SQL Server databases manually. To back up a SQL server database follow the below steps.

1. Launch Management Studio and Connect to SQL Server
2. Under databases, Select your database to backup
3. Right-click on database and navigate to >> Tasks >> Backup

   

4. Under the Source option, Correct databse is selected in dropdownlist. The backup type is set to Full.
5. Under the Destination section, Remove any entry that already exists by clicking the remove button available on the right side. Then Click Add button to provide the correct backup path with filename.
6. See the below screenshot, taking a full database backup of “Test_db” at path D:\backup\Test_db.bak.

   

7. Once the backup completed successfully, you will see a message like below.

   

Conclusion

In this tutorial, you have learned two methods to backup a SQL Server database. Next, you may like our tutorial to restore SQL Server database with T-SQL and SQL Server Management Studio

The post How to Backup SQL Server Database appeared first on TecAdmin.

How to Rename a SQL Server Database

In this guide, we will discuss three ways to rename a database in the SQL server. The first option will use SQL query to rename a database, the second will use a stored procedure and the third option will use SQL Server Management Studio (SSMS) to rename a database.

Use one of the below options’s to rename a SQL server database:

1. Rename SQL Database with Query

Use ALTER DATABASE query to rename a database in SQL Server. The renaming process will fail, in case there are any active connections established by applications or other databases.

Use the below queries to rename the database with SQL query. The first query will change the database to single-user mode to prevent any external connection. After that rename, the database and again set the database to multi-user mode.

ALTER DATABASE Test_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE [Test_db] MODIFY NAME = [Test_db2]
GO 

ALTER DATABASE Test_db_new SET MULTI_USER;
GO

That’s it. You will see that database is renamed.

2. Rename SQL Database with Stored Procedure

The SQL server will also provide a stored procedure to rename a database. You can follow the same way as above to switch the database to single-user mode and rename the database.

The database rename queries will be like:

ALTER DATABASE Test_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

EXEC sp_renamedb 'Test_db', 'Test_db2'
GO

ALTER DATABASE Test_db_new SET MULTI_USER;
GO

3. Rename SQL Database with Management Studio

You can also rename the SQL server database similar to renaming a folder in Windows. Open SQL Server Management Studio (SSMS) and connect to the database server. Follow the below steps to change SQL server to single user mode and rename a database.

First, we will set the database connection to single-user mode to close all active connections to the database before renaming. So follow the below steps to rename the SQL database with SSMS.

The steps are:

1. Open Object Explorer in SSMS.
2. Right Click on the database and click on properties.
3. In the Properties, select the Options page
4. From the Restrict Access option, select Single.
5. Again, right-click the Database and click on Rename.
6. Enter a new name for database and hit enter.
7. Again, open Options page in properties and change Restrict Access to MULTI_USER

Here is the screenshots for the above steps:

Open the SQL Server database properties:

Restrict database access to SINGLE_USER in SQL Server.

Select rename option in SQL server database properties.

Finally, rename the SQL Server database.

Conclusion

This tutorial helped you with 3 methods to rename a database in the SQL Server on the Windows system.

The post How to Rename a SQL Server Database appeared first on TecAdmin.