Can you setup backup/restore functionality in SQL Express 2005 from a C# app?

Can you setup backup/restore functionality in SQL Express 2005 from a C# app?

• Simranjeev

  OK! I wasn't aware that you had to make sure the LogTruncation property was set to that. Thanks a bunch! I'll test that out right away.

  Umm.. yeah... the filenames mixup is a problem with copying pasting. In the actual code they're both "TailBU.trn"

  Thanks!

  KingyNL

  
  

• Krzysztof256

  Sorry, I've miss some in u'r code... :-(

  The first, while backing up tail of the log, you should set LogTruncation property (which controls the backup process), not NoRecovery (which controls restoring). 

  tail.LogTruncation = BackupTruncateLogType.NoTruncate; // this string specifies tail backup, default value of 'Truncate' - usual log backup

  The second, filenames to and from you're doing backup/restore of tail of the log are differs. Please fix it and look what happens.

  Good luck,
  Evergray
  --
  Words mean nothing...

  
  

• PHILIP LOW

  I think I do... SQL Server may put several backups in one device (and doing this by default - check your scripts for "NOFORMAT, NOINIT"). Looks like your device for tail of the log contains several backup sets, and by default RESTORE statement uses the first (which was created sooner and is invalid for current restore). You may check this by execute "restore headeronly from disk='your_device.bak'" in sqlcmd. If several backups in one device is not desired (as for you tail), you should set the 'FormatMedia' and 'Initialize' properties of Backup object to true to overwrite device content. If you want to use specific set from device, use 'FileNumber' property of Restore object (it corresponds the Position column from query results). This may, for example, let you store all needful backups in one file.

  Good luck,
  Evergray
  --
  Words mean nothing...

  
  

• peterMarshall106

  I finally got the database to restore itself. I had to specify tailLog.ContinueAfterError = true to get the tail log to restore without throwing that exception I mentioned earlier where the log backup is too recent for the database. It must be continuously getting that error because I don't get anywhere near the amount of data I should using the tail-log.

  Do you know where the "log backup is too recent to use" exception comes from

  
  

• barneyk

  I have to say that you're the first person that ever mentioned that to me. The documentation I've read never mentioned that for a restore. Thanks a bunch!

  Cheers,

  KingyNL

  
  

• Brad Bass

  It's possible. Take a look on Microsoft.SqlServer.Management.Smo namespace.
  
  Fortunately, there is no automatical restoring of corrupted databases.
  
  
  Good luck,
  Evergray
  --
  
  Words mean nothing...
  
  

• Joe78

  Because 'WITH RECOVERY' is SQL Server default, 'RESTORE LOG ...' and 'RESTORE LOG ... WITH RECOVERY' are similar (yeah, this is not as clear as it should be IMO). You shouldn't worry about 'WITH RECOVERY' absense in script . Your restore sequence is correct. Is this code works as expected

  Good luck,
  Evergray
  --
  Words mean nothing...
  
  

  
  

• GoingDotNet

  Hi,

  Yeah I've been looking into the Smo namespace, and I have the creation of backups working. My problem lies in the Restore class methods and properties.

  I'm using the full-backup/recovery model, so when I recover the database I make a backup of the tail-log, then restore the full database and log backup, then restore the tail-log. I've been using the property NoRecovery=true to denote that I don't want to recover the database yet, and when I look at the T-SQL statement returned with the Script() method it says 'WITH NORECOVERY' in the string. But, when I leave the NoRecovery property as default or explicitly set it to false I would expect that 'WITH RECOVERY' would be placed in the script. It's not though... there's no keyword in the script dealing with the recovery of the database, so I'm not quite understanding why this is happening.

  The following is the code I'm using (it's in C#). Tell me if I'm doing something wrong.

  Server server = new Server(@".\SQLExpress");

  // backup transaction log 1st
  Backup tail = new Backup();
  tail.Database = m_DATABASE_NAME;
  tail.Action = BackupActionType.Log;
  tail.NoRecovery = true;
  tail.Devices.Add(new BackupDeviceItem("TailBackup.trn", DeviceType.File));
  System.Collections.Specialized.StringCollection scriptB = tail.Script(server);
  tail.SqlBackup(server);

  
  //restore backed up db then it's log then the tail log
  Restore db = new Restore();
  db.Database = m_DATABASE_NAME;
  db.Action = RestoreActionType.Database;
  db.NoRecovery = true;
  db.Devices.Add(new BackupDeviceItem("Backup.bak", DeviceType.File));
  System.Collections.Specialized.StringCollection scriptDbR = db.Script(server);

  Restore log = new Restore();
  log.Database = m_DATABASE_NAME;
  log.Action = RestoreActionType.Log;
  log.NoRecovery = true;
  log.Devices.Add(new BackupDeviceItem("BackupLog.trn", DeviceType.File));
  System.Collections.Specialized.StringCollection scriptDbLR = log.Script(server);

  Restore tailLog = new Restore();
  tailLog.Database = m_DATABASE_NAME;
  tailLog.Action = RestoreActionType.Log;
  tailLog.NoRecovery = false;
  tailLog.Devices.Add(new BackupDeviceItem("TailBU.trn", DeviceType.File));
  System.Collections.Specialized.StringCollection scriptDbTLR = tailLog.Script(server);

  db.SqlRestore(server);
  log.SqlRestore(server);
  tailLog.SqlRestore(server);

  string strError;
  bool restored = log.SqlVerify(server, true, out strError);
  if (!restored)
  {
       System.Console.WriteLine("Database not restored properly: " + strError);
  }
  else
  {
       System.Console.WriteLine("Database restore successful!");
  }

  Thanks!

  
  

• Zou Yu

  Unfortunately no. I can restore the database and the log that goes with the database backup, but when I go to restore the tail-log I get this error:

  {"The log in this backup set begins at LSN 22000000017400001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000015400001 can be restored.\r\nRESTORE LOG is terminating abnormally."}

  I basically took an empty database that had tables waiting and backed it up. Then I filled it with some data and tried to perform a restore to see if I could do it. And that's what I get. I thought when you restore the tail-log it would roll-forward all the commited transactions and roll-back the uncommited. They say in all the documentation and forums I've read that this should work.

  Thanks!

  KingyNL

  
  

• magruder2

  It seems I am also sailing on the same ship as Kingy. I have tried setting .FormatMedia = true;.Initialize = true; but after setting these values, the backup fails....I don't now why....:(( and when I comment these lines, the backup works fine but creating BACKUP SETS.

  I also would like to know, is there any property which could be set while restoring so that always the latest backup is restored. I think somehow we ll have to set the last index of the backup set for the FileNumber property of the restore object but don't know how

  can you please help

  -vinay

  
  
  
• Can you setup backup/restore functionality in SQL Express 2005 from a C# app?