All Collections
Databases
Common ways to backup your Microsoft SQL databases
Common ways to backup your Microsoft SQL databases
Updated over a week ago

Whether you are looking to migrate your database to another server, or are simply wanting to store a backup of your database for safe keeping it is important to know exactly how this task can be accomplished.

In the event, you do not have full access to the server we’ll cover several different methods so that hopefully one of the available methods mentioned will assist you in your backup endeavors.

Backup Database via SSMS Backup Tool Set (recommended)

The easiest and fastest way to back up your database is via the built-in Microsoft SQL Task > Backup toolset. To take advantage of this feature you must be logged into the server that the database resides in via RDP. The full steps are provided below:

  1.  Login to the server via RDP (VPS servers only - RDP not available for shared servers).

2.  Open SQL Server Management Studio (SSMS) on the server and connect to your database instance.

3.  Expand the Databases list via SSMS, then right click on your database.

4.  Hover over Task, then click on the option for Backup….

5.  By default (recommended) the backup type will be Full, which will perform a full level backup of the database in question. Now we want to tell the backup toolset where to store the backup (once completed). To do so, make sure that the ‘Backup To’ field is set to Disk. Then click on Add… to add a new file location.

6.  It is important that you add the correct path, as well as the exact file name (including the .bak), so that this is a working backup file.

Screenshot below as an example of a legitimate file name:

7.  Once you enter a valid path, then click on OK. Then click on the OK button once more. The backup should now be running. Depending on the overall size of your database, as well as the performance of your SQL server (such as the resources available on your server) the time this takes may vary.

8.  Once the SSMS Backup toolset UI confirms the backup was successful, then you can use the backup file provided at the folder path you earlier specified in the steps.

Backup Database via SSMS Script Generation

Using SQL Server Management Studio (SSMS) we can utilize the script generation tools to back up a database or specific tables within a database. To do so, please follow the steps below.

  1.  Login to the server via RDP (VPS servers only - RDP not available for shared servers).

2.  Open SQL Server Management Studio (SSMS) on the server and connect to your database instance.

3.  Expand the Databases list via SSMS, then right click on your database.

4.  Then click on Generate Scripts…

5.  It may show you an introduction page, if so, you can click Next.

The Chose Objects screen will then appear, which you can then choose whether you want to only backup specific tables in the database or if you want the entire database (all tables). We recommend choosing the option for ‘Script entire database and all database objects’ if unsure.

Click Next once ready to move onto next step.

6.  For the Scripting Options pane we recommend setting this to ‘Save scripts to a specific location’ , then choosing ‘Save to File > Single File’. You will also want to be sure to enter the correct folder/file path that the backup file will reside in once completed.

Note: We are not yet done, so before clicking next, see next step.

7.  Before proceeding it is important to click on the Advanced button, as by default this script will only export the table schema (not any of the actual data).

8.  Within this Advanced Scripting Options window scroll down until you see the variable named ‘Types of data to script’. This will default to Schema Only, so change this to Schema and data.


9.  Click the OK button to save the changes to the advanced scripting options. Double check the settings discussed above, then click Next, Next, and Finish.

10.  The backup file (provided at the location you chose in earlier steps) should now be ready and contain the information related to your database and/or tables.

Backup Database via myLittleBackup

If running on our shared server environment, then a tool within your WCP control panel will become available that allows you to manage your Microsoft SQL databases. This tool is called myLittleBackup.

If your database is on its own dedicated server, then this myLittleBackup tool would not be available unless running a licensed version on your server.

To back up your database with this tool, please follow our guide on backing up your Microsoft SQL database via myLittleBackup.

Backup Single Database via T-SQL

T-SQL stands for Transact - Structured Query Language. Normally, this is what you run your SELECT, UPDATE, DELETE, etc. queries with from your website.

In this case, we want to make use of the BACKUP statement within T-SQL. We can do so by following the steps below:

  1.  Login to the server via RDP (VPS servers only - RDP not available for shared servers).

2.  Open SQL Server Management Studio (SSMS) on the server and connect to your database instance.

3.  Open a new query window via SSMS and insert the following:

Note: Be sure to replace [DB_Name] with the actual name of the database (both instances). Also, please update the path the backup will restore to if needed.

USE [DB_Name];  
GO  
BACKUP DATABASE [DB_NAME]  
TO DISK = 'C:\MSSQL\Backups\databasebackup.Bak' 
GO

4.  You should now have a full database level backup (.bak file) provided at the location you chose within this script (specified with the ‘TO DISK =’ portion of the above query code).

Backup All Databases via T-SQL

T-SQL can be used to backup all databases on the server, as well as you can exclude specific databases that you do not want to backup. Follow the steps below for guidance on performing this method:

  1. Login to the server via RDP (VPS servers only - RDP not available for shared servers).

  2. Open SQL Server Management Studio (SSMS) on the server and connect to your database instance.

  3. Open a new query window via SSMS and insert the following:

Important: Change the @path variable value in the script to the desired backup folder location.

By Default, the backup folder will be set to C:\MSSQL\Backup\ unless changed.
​

DECLARE @name VARCHAR(50) -- database name
      DECLARE @path VARCHAR(256) -- path for backup files
      DECLARE @fileName VARCHAR(256) -- filename for backup
      DECLARE @fileDate VARCHAR(20) -- used for file name

      -- specify database backup directory
      SET @path = 'C:\MSSQL\Backup\'

      -- specify filename format
      SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

      DECLARE db_cursor CURSOR READ_ONLY FOR
      SELECT name
      FROM master.dbo.sysdatabases
      WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

      OPEN db_cursor
      FETCH NEXT FROM db_cursor INTO @name

      WHILE @@FETCH_STATUS = 0
      BEGIN
         SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
         BACKUP DATABASE @name TO DISK = @fileName

         FETCH NEXT FROM db_cursor INTO @name
      END


      CLOSE db_cursor
      DEALLOCATE db_cursor


​

Did this answer your question?