Backup and Restore FileGroup

Step1. Create a db with two file groups

CREATE DATABASE [FGBackup]
 CONTAINMENT = NONE
 ON PRIMARY
( NAME = N'FGBackup', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
 FILEGROUP [Secondary]
( NAME = N'FGBackup_Sec', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Sec.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
 FILEGROUP [FGB]
( NAME = N'FGBackup_FGB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_FGB.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'FGBackup_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

Step2. Create two tables, one on each FG

USE [FGBackup]
GO
Create Table FGTesting_Primary
(ID int Identity(1,1),
TestingData Varchar(50),
CreatedDate DateTime ) ON [Primary]

Create Table FGTesting_Secondary
(ID int Identity(1,1),
TestingData Varchar(50),
CreatedDate DateTime ) ON [Secondary]

Create Table FGTesting_FGB
(ID int Identity(1,1),
TestingData Varchar(50),
CreatedDate DateTime ) ON [FGB]

Step3. Insert a row into each table

Insert into FGTesting_Primary values('TestingBefore1',GetDate())
Insert into FGTesting_Primary values('TestingBefore2',GetDate())
Insert into FGTesting_Primary values('TestingBefore3',GetDate())
Insert into FGTesting_Secondary values('TestingBefore1',GetDate())
Insert into FGTesting_Secondary values('TestingBefore2',GetDate())
Insert into FGTesting_Secondary values('TestingBefore3',GetDate())
Insert into FGTesting_FGB values('TestingBefore1',GetDate())
Insert into FGTesting_FGB values('TestingBefore2',GetDate())
Insert into FGTesting_FGB values('TestingBefore3',GetDate())

Select * From FGTesting_Primary
Select * From FGTesting_Secondary
Select * From FGTesting_FGB

Step4. Backup FG 1

Backup Database FGBackup
FileGroup = 'Primary'
To Disk = 'D:\FG\Priamry.bck'

Backup Database FGBackup
FileGroup = 'Primary',
FileGroup = 'Secondary'
To Disk = 'D:\FG\Secondary.bck'

Backup Database FGBackup
FileGroup = 'Primary',
FileGroup = 'FGB'
To Disk = 'D:\FG\FGB.bck'

Step5. Restore as a new DB

RESTORE DATABASE [FGBackup_Primary]
FILE = N'FGBackup'
FROM DISK = N'D:\FG\Priamry.bck'
WITH FILE = 1,
MOVE N'FGBackup' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Primary.mdf',
MOVE N'FGBackup_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Primary_1.ldf',
NOUNLOAD, STATS = 10
GO
RESTORE DATABASE [FGBackup_Secondary]
FILE = N'FGBackup',
FILE = N'FGBackup_Sec'
FROM DISK = N'D:\FG\Secondary.bck'
WITH FILE = 1,
MOVE N'FGBackup' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Primary1.mdf',
MOVE N'FGBackup_Sec' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Secondary.mdf',
MOVE N'FGBackup_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Secondary_1.ldf',
NOUNLOAD, STATS = 10
GO

RESTORE DATABASE [FGBackup_FGB]
FILE = N'FGBackup',
FILE = N'FGBackup_FGB'
FROM DISK = N'D:\FG\FGB.bck'
WITH FILE = 1,
MOVE N'FGBackup' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Primary2.mdf',
MOVE N'FGBackup_FGB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_FGB.mdf',
MOVE N'FGBackup_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Secondary_2.ldf',
NOUNLOAD, STATS = 10
GO

Step6. One table should be accessible. Other should be down

use [FGBackup_Primary]
select * from FGTesting_Primary
select * from FGTesting_Secondary Step Not available
select * from FGTesting_FGB Step Not available

use [FGBackup_Secondary]
select * from FGTesting_Primary
select * from FGTesting_Secondary
select * from FGTesting_FGB Step Not available
use [FGBackup_FGB]
select * from FGTesting_Primary
select * from FGTesting_Secondary Step Not available
select * from FGTesting_FGB

SSIS Package to Backup Jobs, Operators and Alerts Script using SMO

SSIS Package to Backup Jobs Script using SMO.

This package willl use some SMO reference and generates SQL Server Jobs, Operators and Alerts script backup.

I had written the code for SQL Server Jobs only and commented code for Operators backup and Alerts Backup.

You just need to replcace Operators or Alerts instead of Jobs.

I have created SSIS package that automatically creates backup folder for current date and save your all jobs with jobsname.sql separately as well as all jobs in one file with static name AllJobsInOneFile.sql.

Step1 : First of all you have to drag one script task and create variables..

–> ServerName – DataType String.

–> UserName – DataType String.

–> pwd- DataType String.

–> DateTime – DataType String.

–> FolderPath – DataType String.

BackupJobsScript 1

Step2 : Please proper assign ServerName, UserName Pwd and your local machine’s FolderPath.

For DateTime variable click on Expression button and paste below code.

Note : This will create folder with current date letter in script task.

(DT_STR,4,1252)YEAR(GETDATE()) + RIGHT(“0″ + (DT_STR,2,1252)MONTH(GETDATE()),2) + RIGHT(“0″ + (DT_STR,2,1252)DAY(GETDATE()),2)

Replication Script Backup 2

Step3 : Now Right Click on Script task and select all user variables to ReadOnlyvariables.

BackupJobsScript 2

Step4 : Click on Edit Script.

here you need to add refrences for SMO Objects.

To Add Reference

–> Click on project menu –> Select Add Refrence.

–> On the Add Refrence popup select .Net Tab.

–>Select Microsoft.SQLServer.ConnectionInfo

Microsoft.SQLServer.Management.Utility

Microsoft.SQLServer.SMO

Microsoft.SQLServer.ManagedDTS

Microsoft.SQLServer.ScriptTask.

Paste below code to your script task code.


#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.IO;
using System.Collections.Specialized;
#endregion

namespace ST_69d1b336488942988b45f8cdd86a3824
{
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {
  public void Main()
  {
            StringCollection sc = new StringCollection();
            ScriptingOptions so = new ScriptingOptions();
            so.IncludeDatabaseContext = true;

            string ServerName;
            string UserName;
            string Password;
            string FolderDate;
            string FolderPath;

            ServerName = (string)Dts.Variables["ServerName"].Value;
            UserName = (string)Dts.Variables["UserName"].Value;
            Password = (string)Dts.Variables["pwd"].Value;
            FolderDate = (string)Dts.Variables["DateTime"].Value;
            FolderPath = (string)Dts.Variables["FolderPath"].Value;

            ServerConnection conn = new ServerConnection();
            conn.LoginSecure = false;
            conn.Login = UserName;
            conn.Password = Password;
            conn.ServerInstance = ServerName;
            Server srv = new Server(conn);

            System.IO.Directory.CreateDirectory(FolderPath + "\\" + FolderDate.ToString());
            try
            {
                string script = "";

                string JobName;
                string AllJobsInOneFile = "";
                //Loop over all the jobs
                //To take a script backup of Operators.

                //foreach (Job J in srv.JobServer.Operators)
                //To take a script backup of Alerts.
                //foreach (Job J in srv.JobServer.Alerts)
                //To take a script backup of Jobs.
                foreach (Job J in srv.JobServer.Jobs)
                {

                    //Output name in the console
                    Console.WriteLine(J.Name.ToString());

                    JobName = J.Name.ToString();
                    sc = J.Script(so);

                    //Get all the text for the job
                    foreach (string s in sc)
                    {
                        script += s;
                    }

                    //Generate the file
                    TextWriter tw = new StreamWriter(FolderPath + "\\" + FolderDate.ToString() + "\\" + JobName.Replace(':', '_').ToString() + ".sql");
                    tw.Write(script);
                    tw.Close();
                    TextWriter WriteAllJobs = new StreamWriter(FolderPath + "\\" + FolderDate.ToString() + "\\" + "AllJobsInOneFile.sql", true);
                    WriteAllJobs.Write(script + "\nGO\n");
                    WriteAllJobs.Close();
                    script = "";
                }
            }
            catch (Exception eh)
            {
                //MessageBox.Show(eh.ToString());
            }
   Dts.TaskResult = (int)ScriptResults.Success;
  }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        ///
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}

Note: This script will support only in SQL Server 2012.

Create a job which calls this package and schedule it daily or weekly.

Regards,

Nirav Gajjar