Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

How to back up SQL Server database

Database backup is routine activity you can take backup using SSMS and using C# code.

Back up MSSQL database using c# code:

You can use following c# code example to take backup of your SQL server database,

 C# Code:

   //references
    Microsoft.SqlServer.ConnectionInfo
    Microsoft.SqlServer.Management.Sdk.Sfc
    Microsoft.SqlServer.Smo
    Microsoft.SqlServer.SmoExtended
    Microsoft.SqlServer.SqlEnum
 
//Method,pass your databaseName, username, password, server name and destination path to save backup file
    public void BackupDatabase(string databaseName, string userName, string password, string serverName, string destinationPath)
    {            //Define a Backup object variable. 
      Backup sqlBackup = new Backup();
    //Specify the type of backup, the description, the name, and the database to be backed up.
 
        sqlBackup.Action = BackupActionType.Database;
 
        sqlBackup.BackupSetDescription = "BackUp of:" + databaseName + "on" + DateTime.Now.ToShortDateString();
 
        sqlBackup.BackupSetName = "FullBackUp";
 
        sqlBackup.Database = databaseName;
        //Declare a BackupDeviceItem
 
        BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath + "FullBackUp.bak", DeviceType.File);
 
        //Define Server connection
 
        ServerConnection connection = new ServerConnection(serverName, userName, password);            //To Avoid TimeOut Exception
 
        Server sqlServer = new Server(connection);
        sqlServer.ConnectionContext.StatementTimeout = 60 * 60;
        Database db = sqlServer.Databases[databaseName]; (Reference Database As microsoft.sqlserver.management.smo.database,not as System.entity.database )
 
sqlBackup.Initialize = true;
 
        sqlBackup.Checksum = true;
 
        sqlBackup.ContinueAfterError = true;
        //Add the device to the Backup object.
 
        sqlBackup.Devices.Add(deviceItem);
 
        //Set the Incremental property to False to specify that this is a full database backup.
        sqlBackup.Incremental = false;
        sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
 
        //Specify that the log must be truncated after the backup is complete.       
        sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
        sqlBackup.FormatMedia = false;
 
        //Run SqlBackup to perform the full database backup on the instance of SQL Server.
        sqlBackup.SqlBackup(sqlServer);
 
        //Remove the backup device from the Backup object.          
        sqlBackup.Devices.Remove(deviceItem);
    }
    }


This code will take backup of your all databases in SQL server & As you can see in the above method created using C#, you need to pass all the details of your database like Server Name, User Name, Password, Destination path etc.

There are more options to take back-up of sql server database and is the original source of the above code.

SQL server 2016 Transfer Maintenance Plan from One Server to Another

 Hi this is Vaibhav Shringi, This is my 3rd article on SQL server 2016, in this article, will explain in detail how to transfer maintenance plan from one server to another.

In the following example, I will use two servers NMK135 and VS12345 for explanation and sample users Vadmin and nmk12345.

Transfer Maintenance Plan from One Server to Another

1. Connect to Source Integration server

Connect to Integration Services in SQL Server Management Studio (Where Maintenance plan already present Say SERVER-1)


2. Export Maintenance plan package from SERVER-1.

Connect with appropriate permissions and export the “DBMaintenancePlan” package.


3. Select Package Location as File System and browse for Package path to save “DBMaintenancePlan.dtsx” file.

4. Save and OK to complete the export process in SERVER-1.

5. Transfer the newly exported file to new server file system (say SERVER-2).

6. Transfer any required Rebuild Indexes and UpdateStatistics SPs from SERVER-1 to SERVER-2(if any).

7. Connect to Integration Services of SERVER-2. (use step 1). Import the previously exported package.


8. Connect to Database Engine of SERVER-2


9. Verify imported Maintenance Plan on SERVER-2.


10. Modify Plan to change some config entries.


11. Follow below steps ONLY IF WANT TO change Job Schedule Timing.

Click on Calendar button present right side of “Schedule” tab. Change appropriately and save the changes.


Below Step 12 to 14 are for “Nightly_DB_maintenance” Subplan.

Click on Nightly_DB_maintenance Subplan to see below screen.


12. Verify Rebuild and update statistics tasks.

13. Verify Database Backup Location and database names.

      => Double click on “Full Backup Database task”.
      => Verify Folder location.


14. Verify Backup File Cleanup Period.

     => Double click on “CleanUp BackUp Files”.
     => Verify File Age. (Prefer to have 2 weeks) 


Below Step 15 is for “Every2Hrs_DB_Tran_Bkp” Subplan.

Click on Nightly_DB_maintenance Subplan to see below screen


15. Verify Transactional backup location and databases names.

      =>Double click on “Back Up Database Task”
      =>Verify Folder location. 



16. Save the Maintenance plan and close it. 

How to VERIFY Maintenance Plan Jobs?

VERIFY Maintenance Plan Jobs:

1. Open property window of both the jobs (right click on Job name)

  a. DBMaintenancePlan.Every2Hrs_DB_Tran_Bkp
  b. DBMaintenancePlan.Nightly_DB_Maintenance


2. Job owner:

    => If face any access related issue with Vadmin then change Job owner to “SA”.
    => We are facing this issue in VS12345 because of SQL agent service default configuration. 

3. Review Schedule Time etc.


धन्यवाद मित्रो !! 

Thanks Friends


SQL Server 2016 User Logins Management

This is my 2nd article on SQL server 2016, in this article I will explain in detail how to maintain user permissions or security.

SQL Server Security Management

A. Developers Access Control

It is very crucial to manage each user’s access rights as it can become hectic when need to manage 10-20 users with multiple permissions on their windows authentication.

To manage permissions among users, SQL has provided access management using windows groups.

Below are the steps to create any local windows group

1.  Search “Computer Management” and open it.
2.  Expand “Local Users and Groups” and then open Groups.

3.  Add new local windows user group and map windows users under it. Eg. “SQLUserGP_Read”, “SQLUserGP_Write” and “SQLUserGP_DBA”

Windows group can be added in same fashion like windows user.
It is always advisable to provide execute rights to all developers so execute below query for same.
GRANT EXECUTE TO [VSPC0129\SQLUserGP_Read]
GRANT EXECUTE TO [VSPC0129\SQLUserGP_Write]


B.  SQL/Web User Access Control

To restrict hackers from manipulate our database, we should always provide minimal access to users.
It can be done at object level and database level.

Object Level

Advisable to provide access to only required objects so that other objects can’t be accessed in case of any hacking attempts.

Database Level 

Keep all the interactions through stored procedure and user should only be allowed to execute SPs (will not have even select rights).

धन्यवाद मित्रो !! 

Thanks Friends