MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter

















Latest SQL Server Tips










FILE Backup in SQL Server Step by Step Tutorial with Examples

Read Comments   |   Related Tutorials: SQL Server Database Backup Options > Next Topic: FILEGROUP Backups in SQL Server

Page 13 / 16


Click here to read this tutorial from the beginning...

Introduction

Using FILE backup one can backup SQL Server Data File individually. While backing up the database you can specify whole of FILEGROUP instead of specifying each database file individually within the FILEGROUP. In case if there is any file within the FILEGROUP OFFLINE may be because of file being restored then the whole FILEGROUP will be OFFLINE and cannot be backup up. A FILE backup can also serve as the differential base for differential file backups. For more information, see Differential Backups in SQL Server.

TSQL Script to Create Database with Multiple Data Files in Primary and Read Only File in Secondary File Group

USE [master]
GO

CREATE DATABASE [MyTechMantra]
CONTAINMENT = PARTIAL
ON PRIMARY
(
NAME = N'MyTechMantra',
FILENAME = N'F:\MSSQL\DATA\MyTechMantra.mdf',
SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB
),
(
NAME = N'MyTechMantra_Data2',
FILENAME = N'F:\MSSQL\DATA\MyTechMantra_Data2.ndf',
SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB
),
FILEGROUP [ReadOnly]
(
NAME = N'MyTechMantra_ReadOnly',
FILENAME = N'F:\MSSQL\DATA\MyTechMantra_ReadOnly.ndf',
SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'MyTechMantra_log',
FILENAME = N'F:\MSSQL\DATA\MyTechMantra_log.ldf',
SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%
)
GO

Create a FULL FILE backup Using TSQL command

In this example we will backup Secondary Data File which is available in Primary File Group.

BACKUP DATABASE [MyTechMantra]
FILE = N'MyTechMantra_Data2'
TO DISK = N'C:\DBBackups\MyTechMantra_MyTechMantra_Data2.bck'
WITH NOFORMAT, NOINIT, COMPRESSION, STATS = 25
GO

Create a FULL FILE Database Backup Using SQL Server Management Studio

  • Open SQL Server Management Studio, connect to the appropriate SQL Server Database Instance, in Object Explorer, click the SERVER NAME to expand the server tree.
  • Expand Databases node and then right click the database which you want to take a FULL FILE Database backup.
  • Select Tasks, and then click Back Up… option as shown to open up Back Up Database dialog box
  • Select Backup Type as FULL, Backup Component as Files and FileGroups and click on "..." and Select Files and Filegroups and Click OK

Select File and FileGroups in SQL Server


  • Click on Add button to add a backup file and specify the backup file name and click OK to save the changes.
  • Finally to take FULL FILE Backup click OK.

Create a FULL FILE Database Backup Using SQL Server Management Studio


Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.

Previous Page.. Next Page..


Learn More...




Last Updated On: Feb 12, 2014



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: