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.

Syntax:

The syntax of the sp_rename stored procedure is:

sp_rename 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'

Rename a Column in SQL Server

For example, I have a table named Accounts in the MSSQL server database. This table contains a column named “cust_id”. For some reason, we need to rename this column to “customer_id”. See below screenshot:

Open query windows and execute the following query. Here Test_dbis the database name.

USE Test_db
GO

sp_rename 'Accounts.cust_id', 'customer_id' , 'COLUMN'
GO 

This may show you a warning message with the result, But this will rename the column name successfully. Checked the table structure again, and I found that the column is updated as required.

Conclusion

This tutorial helped you to rename a table column name in the SQL Server database.

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

Use WITH REPLACE option with the RESTORE command on MSSQL query window. Change database name and backup file location as per your setup.

USE MASTER
GO

RESTORE DATABASE  MyDB
FROM DISK = 'C:\Backups\MyDB_15082016.bak'
WITH REPLACE
GO

The post FIXED MSSQL Error 3154 – The backup set holds a backup of a database other than the existing database appeared first on TecAdmin.

Prerequsities

You must have installed the .net framework on your system to complete this task.

Create ASPState Database

After installing the .net framework on your system, open the windows command prompt and navigate to the framework installation directory as per your installed version of the .net framework and system architecture.

Syntax:

aspnet_regsql.exe -ssadd -sstype p -S {SQL Instance} -U {Username} -P {Password}

Implementation:

Navigate to the .net framework directory as followings:

c:\> cd "%windir%\Microsoft.NET\Framework64\v4.0.30319"

Directory path may be changed as per your installation like below.

.NET Framework 2.0/3.0/3.5 (32-bit): %windir%\Microsoft.NET\Framework\v2.0.50727
.NET Framework 2.0/3.0/3.5 (64-bit): %windir%\Microsoft.NET\Framework64\v2.0.50727
.NET Framework version 4.x (32-bit): %windir%\Microsoft.NET\Framework\v4.0.30319
.NET Framework version 4.x (64-bit): %windir%\Microsoft.NET\Framework64\v4.0.30319

Now execute following command to finally create database named ASPState in SQL server defined instance. As per below command DB1SQLEXPRESS is my SQL instance.

aspnet_regsql.exe -ssadd -sstype p -S DB1SQLEXPRESS -U sa -P myPassword

On successful execution of the above command, you will get output like below.

Microsoft (R) ASP.NET SQL Registration Tool version 4.0.30319.33440
Administrative utility to install and uninstall ASP.NET features on a SQL server.
Copyright (C) Microsoft Corporation. All rights reserved.

Start adding session state.
.........................
Finished.

The post How To Create ASPState Database in SQL Server appeared first on TecAdmin.

This article contains a set of commands to Shrink Log Files of SQL Server database.

Shrink SQL Server Transaction Log Files

First of all, check the actual name of log files stored in SQL server records using the following SQL query. Execute these queries in using SQL Server management studio query browser.

USE DATABASE_NAME;
GO

SELECT FILE_NAME(2);
GO

Now, use the DBCC SHRINKFILE SQL command to shrink log files. The database must be running in SIMPLE recovery mode. Open the query windows in SQL Server management studio and run the following command. You

Make sure to change DATABASE_NAME with actual database name and LOG_FILE_NAME with log file name found in the above step. The below example query will shrink the log file to 1000MB.

USE DATABASE_NAME;
GO

ALTER DATABASE DATABASE_NAME
SET RECOVERY SIMPLE;
GO  

DBCC SHRINKFILE (LOG_FILE_NAME, 1000);
GO  

ALTER DATABASE DATABASE_NAME
SET RECOVERY FULL;
GO  

All done. Now check the size of the log files, I hope you will get the expected results.

The post How to Shrink SQL Server Transaction Log Files using DBCC SHRINKFILE appeared first on TecAdmin.