System Status
Buy Now
  • Email & Apps
  • Office 365
Login
  • MyRackspace Portal
  • Cloud Control Panel
  • Rackspace Webmail Login
  • Cloud Office Control Panel
  • Support Home
  • How-To
  • Developer Documentation
  •  Blogs 
    • Expert Insights Tech Blog
    • Rackspace Blog
    • Solve: Thought Leadership

Support Network

End-to-End Multicloud Solutions.   Solving Together.™   Learn more at Rackspace.com

How–To Home

Cloud Servers

  • Introduction
  • FAQ
  • All Articles

Configure MSSQL database backups

Last updated on:  2020-08-19

Authored by:  Karoline Mills


This article describes how to perform SQL Server® database backups by using SQL Server Management Studio (SSMS) and PowerShell®.

Backup limitations

Backups have the following limitations:

  • You cannot restore backups that were created with a newer version of SQL Server to older versions of SQL Server.
  • You must take a full database backup before you can perform differential and transaction log backups.
  • You need Backup database or Backup log permissions to perform backup operations. By default, the system grants these permissions to the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

Use SQL Server Management Studio to create a database backup

  1. Connect to your database instance and expand the databases section on the left-hand side.
  2. Right-click on the database you want to back up and select Back Up….
  3. Select the desired backup type (full, differential, or log) from the drop-down list.
  4. Choose the desired backup destination and select OK to start the backup process.

Alternatively, you can use the following steps to initiate a backup operation:

  1. When logged into the correct database instance, select New Query.

  2. Use the following query when backing up to a disk. Replace the location and database name with the respective names on your server:

    USE SQLTestDatabase;
    GO
    BACKUP DATABASE SQLTestDatabase
    TO DISK = 'd:\backups\SQLTestDatabase.bak'
     WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of SQLTestDatabase';
    GO
    

Use PowerShell to create a database backup

You can also create database backups by using PowerShell.

First, open PowerShell with administrator permissions and type Install-Module -Name SqlServer. This commmand installs the SQL Server module, which you need to perform backup operations in PowerShell.

Next, use the following example to perform a full database backup to the default backup location, replacing the location and database name with your server’s respective names:

$credential = Get-Credential
Backup-SqlDatabase -ServerInstance Computer[\Instance] -Database <SQLTestDatabase> -BackupAction Database -Credential $credential

Finally, to learn more about syntax and examples for different backup operations, review the official Microsoft documentation.

Share this information:

©2020 Rackspace US, Inc.

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License

See license specifics and DISCLAIMER

About Rackspace
  • About
  • Customer Stories
  • Events
  • Programs
Blogs
  • The Rackspace Blog
  • Expert Insights Tech Blog
  • Solve: Thought Leadership
  • News
  • Contact Information
  • Legal
  • Careers
Site Information
  • Style Guide for Technical Content
  • Trademarks
  • Privacy Statement
  • Website Terms
Support Network
  • Support Network Home
  • Rackspace How-To
  • API Documentation
  • Developer Center
  • ©2020 Rackspace US, Inc.