C# .NET - i want to create in application in c# backup and restore in sql

Asked By paras patel on 15-Nov-11 05:15 AM
hello sir and madam I want to help for creating
application in c#

backup and restore
so plz give me code with sql server 2005
Jitendra Faye replied to paras patel on 15-Nov-11 05:18 AM

You may backup and restore the SQL Server 2000 database with “SQL Distributed Management Objects (SQL-DMO)”.
SQL-DMO is a collection of COM objects that are designed for programming all aspects of managing Microsoft SQL Server.


Please refer to the following suggestions:


Since you are using the windows authentication, we need to add the login “YourMachineName\ASPNET” in the SQL Server.

In SQL Server Enterprise Manager, expand Microsoft SQL Servers->SQL Server Group->YourSQLServerInstance->Security, right-click Logins and choose New Login…, add the login “YourMachineName\ASPNET” and assign the appropriate “Server Roles” and “Database Access” to it.

Right-click the References in the Visual Studio, and choose Add Reference…, find the “Microsoft SQLDMO Object Library” in the COM tab and select it, press OK.


Try this code-


private void btnBackup_Click(object sender, System.EventArgs e)
{

  if (saveFileDialog1.ShowDialog() == DialogResult.OK)  
  {
    SQLDMO.Backup backup = new SQLDMO.BackupClass();
    SQLDMO.SQLServer server = new SQLDMO.SQLServerClass(); 
    server.LoginSecure = true;   
   server.Connect(".","","");   
   backup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
   backup.Database = "sql2000test"; 
   backup.Files = saveFileDialog1.FileName;  
   backup.Initialize = true; 
    backup.SQLBackup(server);
   server.DisConnect();  
     }  
     }

Try this code and let me know.

Suchit shah replied to paras patel on 15-Nov-11 05:21 AM
Just have a look on below nice developed backup and restore utility on c# check that links


http://www.codeproject.com/KB/database/SQLServer_Backup_SMO.aspx
http://www.codeproject.com/KB/database/SqlServer_Backup_Restore.aspx
http://www.codeproject.com/KB/database/SQL_Server_2005_Database.aspx

Hope it helps
Web Star replied to paras patel on 15-Nov-11 06:35 AM
Yes you can take backup and restoring of sql server database in c#.net code as follows
Backup database


public void BackupDatabase(String databaseName, String userName, 
            String password, String serverName, String destinationPath)
{
    Backup sqlBackup = new Backup();
    
    sqlBackup.Action = BackupActionType.Database;
    sqlBackup.BackupSetDescription = "ArchiveDataBase:" + 
                                     DateTime.Now.ToShortDateString();
    sqlBackup.BackupSetName = "Archive";


    sqlBackup.Database = databaseName;


    BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);
    ServerConnection connection = new ServerConnection(serverName, userName, password);
    Server sqlServer = new Server(connection);
    
    Database db = sqlServer.Databases[databaseName];
    
    sqlBackup.Initialize = true;
    sqlBackup.Checksum = true;
    sqlBackup.ContinueAfterError = true;
    
    sqlBackup.Devices.Add(deviceItem);
    sqlBackup.Incremental = false;


    sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
    sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;


    sqlBackup.FormatMedia = false;


    sqlBackup.SqlBackup(sqlServer);
}

Restore Database



public void RestoreDatabase(String databaseName, String filePath, 
       String serverName, String userName, String password, 
       String dataFilePath, String logFilePath)
{
    Restore sqlRestore = new Restore();
    
    BackupDeviceItem deviceItem = new BackupDeviceItem(filePath, DeviceType.File);
    sqlRestore.Devices.Add(deviceItem);
    sqlRestore.Database = databaseName;


    ServerConnection connection = new ServerConnection(serverName, userName, password);
    Server sqlServer = new Server(connection);


    Database db = sqlServer.Databases[databaseName];
    sqlRestore.Action = RestoreActionType.Database;
    String dataFileLocation = dataFilePath + databaseName + ".mdf";
    String logFileLocation = logFilePath + databaseName + "_Log.ldf";
    db = sqlServer.Databases[databaseName];
    RelocateFile rf = new RelocateFile(databaseName, dataFileLocation);
    
    sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFileLocation));
    sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName+"_log", logFileLocation));
    sqlRestore.ReplaceDatabase = true;
    sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
    sqlRestore.PercentCompleteNotification = 10;
    sqlRestore.PercentComplete += 
       new PercentCompleteEventHandler(sqlRestore_PercentComplete);
            
    sqlRestore.SqlRestore(sqlServer);
    db = sqlServer.Databases[databaseName];
    db.SetOnline();
    sqlServer.Refresh();
}

also check 
Check this article - http://www.dotnetfunda.com/articles/article489-backup-and-restore-database-in-aspnet.aspx
http://www.geekpedia.com/tutorial180_Backup-and-restore-SQL-databases.html
Kirtan Patel replied to paras patel on 15-Nov-11 12:51 PM
Database YourDB has full backup YourBaackUpFile.bak. It can be restored using following two steps.
 
Step 1: Retrive the Logical file name of the database from backup.
 
RESTORE FILELISTONLY
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
GO
 
Step 2: Use the values in the LogicalName Column in following Step.
 
----Make Database to single user Mode
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
 
----Restore Database
RESTORE DATABASE YourDB
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'
 
/*If there is no error in statement before database will be in multiuser mode.
If error occurs please execute following command it will convert database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER
GO