Full Database Backup:

1. Connect to the Windows MSSQL server in question via RDP

2. Launch SQL Server Management Studio (SSMS) and connect to your SQL Server instance.

3. Expand the Databases node in Object Explorer and find out the database in question.

4. Right-click the database, hover over Tasks, and select Back up….

 5. On the General page in the Destination section, Select Remove until all existing backup files have been removed

6. Select Add and the Select Backup Destination dialog box will open. 

7. Enter a valid path and file name in the File name text box and use .bak as the extension to simplify the classification of this file.

8. Click OK and then click OK again to initiate the backup.

Alternatively, you can run the following Transact-SQL command to back up your database:

BACKUP DATABASE [DB Name]
TO DISK = N’G:\3plweb\3plweb_backup-2020_09_23.bak’
WITH NOFORMAT, NOINIT,
NAME = N’SQLTestDB-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

*Database Restoration: 

1. Launch SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
2. Right-click the Databases node in Object Explorer and select Restore Database….

3. Select Device:, and then select the ellipses (…) to locate your backup file.

4. Select Add and navigate to where your .bak file is located. Select the .bak file and then select OK.

5. Click on Options, then check Overwrite the existing database(WITH REPLACE) and Close exiting connection to destination database.

6. Go back to General option to review the backup file again, then Select OK to restore the backup of your database.

7. Done.

Alternatively, you can run the following Transact-SQL script to restore your database:

USE [master] RESTORE DATABASE [DB Name]
FROM DISK = N’G:\3plweb\3plweb_backup-2020_09_23.bak’WITH FILE = 1,
NOUNLOAD, STATS = 5
GO

USE [master];
DECLARE @kill varchar(8000) = ”;
SELECT @kill = @kill + ‘kill ‘ + CONVERT(varchar(5), req_spid) + ‘;’
FROM master.dbo.syslockinfo
WHERE rsc_type = 2
AND rsc_dbid = db_id(‘MyDB’)
EXEC(@kill);

Back up and shrink the Microsoft SQL transaction log and release the unused space

How to back up the transaction log file:

1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
2. Expand Databases and then right-click the database that you want to back up.
3. Point to Tasks, point to Back Up, and then in General page click Backup type.
4. In Options page ensure Truncate the transaction log option is selected

How to shrink a log file:

1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
2. Expand Databases and then right-click the database that you want to shrink.
3. Point to Tasks, point to Shrink, and then click Files.
4. Select the file type and file name.
5. Optionally, select the Release unused space check box.

Selecting this option causes any unused space in the file to be released to the operating system and shrinks the file to the last allocated extent. This reduces the file size without moving any data.