none
sql server database back up in c#

    Question

  •  

    Dear all

    I create a database in Visual studio/server explorer, and then I use linq to access the table, it works fine with my code. right now I want to do backup, I found following code from web, my database name is RegServer.mdf, only one table inside, let's say tablename

    here I got 2 problem, one is it seems I can not find my database from sqlServer 

    Database db = sqlServer.Databases["RegServer"]; return null,

    second is I got   "Backup failed for Server 'ENG-J6X8ZD1\\SQLEXPRESS'. exception at 

    sqlBackup.SqlBackup(sqlServer);

    any help?   thank you very much



    Backup sqlBackup = new Backup();

    sqlBackup.Action = BackupActionType.Database;

    sqlBackup.BackupSetDescription = "ArchiveDataBase:" + DateTime.Now.ToShortDateString();

    sqlBackup.BackupSetName = "Archive";

    sqlBackup.Database = "RegServer";

    BackupDeviceItem deviceItem = new BackupDeviceItem(filename, DeviceType.File);

    ServerConnection connection = new ServerConnection(@".\SQLEXPRESS");

    Server sqlServer = new Server(connection);

    Database db = sqlServer.Databases["RegServer"];

    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);

    Wednesday, September 10, 2008 6:27 PM

Answers

  • Hello,

     

    I'm fearing that you have jumped into the classical trap of the creation of a database thru Visual Studio.

    I hope that you have Sql Server Management Studio Express Edition ( SSMSEE ) which is the only one tool which should be used to create or modify a database. VS is not done for that.

    If you have SSMSEE , use it to test if you have a database RegServer in your instance.

    You should have created a user instance instead of a real database.

     

    Even, if in many books for beginners, it is said that you can create a database with VS, you will have always problems if your instance is an Express Edition.

    If your instance is a Workgroup,Standard,Developper or Entreprise, the notion of user instance is unknown, so VS ( full edition not express ) is able to create a real database.

     

    I have always considered Express Edition as a "normal" edition with limitations ( it is free, you can't have all the features of costly editions ) and i have rejected user instances ( user instance = false ) and i have no problem ( except for Sql Agent, but you can now create it , see CodePlex )

     

    Your code is correct. I have found no error, your database is not attached really to your instance , that's all

     

    Have a nice day

    Wednesday, September 10, 2008 9:11 PM
  • Hello,

     

    your error message is not enough precise to be helpful.

    But after a search on this forum, your problem may be caused by a problem of permissions on the files of your database.

     

    Please, could you try this code ?

     

    try

    {

    sqlBackup.SqlBackup(sqlserver);

    }

    catch ( Exception ex )

    {

    String str = "Error for backup " + ex.Message + Environment.NewLine;

    str += "StackTrace : " + ex.StackTrace + Environment.NewLine;

    if ( ex.InnerException != null )

    {

    str += "Inner1 : " + ex.InnerException.Message + Environment.NewLine;

    if ( ex.InnerException.InnerException != null )

    {

    str += "Inner2 : " + ex.InnerException.InnerException.Message + Environment.Newline;

    }

    }

    MessageBox.Show(str);

    //or

    Console.WriteLine(str);

    }

     

    Paste the content of str and i think it will be easier to find the cause of your problem

    Post the content of str

     

    Have a nice day

     

    Wednesday, September 10, 2008 9:41 PM

All replies

  • Hello,

     

    I'm fearing that you have jumped into the classical trap of the creation of a database thru Visual Studio.

    I hope that you have Sql Server Management Studio Express Edition ( SSMSEE ) which is the only one tool which should be used to create or modify a database. VS is not done for that.

    If you have SSMSEE , use it to test if you have a database RegServer in your instance.

    You should have created a user instance instead of a real database.

     

    Even, if in many books for beginners, it is said that you can create a database with VS, you will have always problems if your instance is an Express Edition.

    If your instance is a Workgroup,Standard,Developper or Entreprise, the notion of user instance is unknown, so VS ( full edition not express ) is able to create a real database.

     

    I have always considered Express Edition as a "normal" edition with limitations ( it is free, you can't have all the features of costly editions ) and i have rejected user instances ( user instance = false ) and i have no problem ( except for Sql Agent, but you can now create it , see CodePlex )

     

    Your code is correct. I have found no error, your database is not attached really to your instance , that's all

     

    Have a nice day

    Wednesday, September 10, 2008 9:11 PM
  • Many thanks, I have just figured out I have to attach my database to SSMSS too, and I have attached my database to it, right now I can view the database in server.

     

    but I still failed in this statement

     

    sqlBackup.SqlBackup(sqlServer);

     

    the exception is

    "Backup failed for Server 'ENG-J6X8ZD1\\SQLEXPRESS'.

     

     I google this msg, some info said I need set my table to be full text index, but I can not find this setting from SSMSS following the instruction, any ideas?

     

     

    Cheers

     

    Wednesday, September 10, 2008 9:22 PM
  • Hello,

     

    your error message is not enough precise to be helpful.

    But after a search on this forum, your problem may be caused by a problem of permissions on the files of your database.

     

    Please, could you try this code ?

     

    try

    {

    sqlBackup.SqlBackup(sqlserver);

    }

    catch ( Exception ex )

    {

    String str = "Error for backup " + ex.Message + Environment.NewLine;

    str += "StackTrace : " + ex.StackTrace + Environment.NewLine;

    if ( ex.InnerException != null )

    {

    str += "Inner1 : " + ex.InnerException.Message + Environment.NewLine;

    if ( ex.InnerException.InnerException != null )

    {

    str += "Inner2 : " + ex.InnerException.InnerException.Message + Environment.Newline;

    }

    }

    MessageBox.Show(str);

    //or

    Console.WriteLine(str);

    }

     

    Paste the content of str and i think it will be easier to find the cause of your problem

    Post the content of str

     

    Have a nice day

     

    Wednesday, September 10, 2008 9:41 PM
  • thanks, you are right, the real reason is system can not open the back file, if I wanna backup to user/document, it failed , if backup in working directory, it works. but I am administrator, how come I can not open a file to backup in my own document?

     

    anyway, it solved, thank you guys' help a lot, you did a big favor to me :-)

     

    Thursday, September 11, 2008 2:23 PM
  • Hi

     

    another problem, in my app, I have linq to connect my database and list the records, meanwhile I wanna backup my database with the above code, but this code works with other database file, for the current using database file, I failed, get exception:

     

     database "regserver" cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details

     

    I think probably this database is used, so I can not backup or retore, any way to solve this?

     

    Thanks

     

    Thursday, September 11, 2008 8:18 PM
  • Hello,

     

     

    malcolmxu wrote :

     

    Code Snippet

    database "regserver" cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details

     

     

    Please, could you have a look on the ErrorLog of your instance and post the error you should find ?

    The ErrorLog for the 1st instance installed is in

    c:\Program Files\Microsft Sql Server\MSSQL.1\MSSQL\Log\

    In this directory, you find the files :

    ErrorLog

    ErrorLog.1

    ErrorLog.2

    up to

    ErrorLog.5

     

    the current ErrorLog is ErrorLog

    As soon as the Sql Server Service is (re)started

    ErrorLog.5 deleted

    ErrorLog.4 renamed into ErrorLog.5

    ErrorLog.3 renamed into ErrorLog.4

    ...

    ErrorLog  renamed into ErrorLog.1

    ErrorLog is created and is the current file ( you have to guess the file which was the current ErrorLog when your problem appeared )

     

    I think it's again a problem of user instance

    I believe that on  http://blogs.msdn.com/sqlexpress/, you even find the way of doing a backup of an user instance

     

    Have a nice day

    Thursday, September 11, 2008 9:10 PM
  • BACKUP failed to complete the command BACKUP DATABASE C:\DEV\NETWORKSERVER\REGSERVER\REGSERVER\BIN\DEBUG\REGSERVER.MDF. Check the backup application log for detailed messages.

    here is log, that is not helpful, I did many tests, if this database is not used by some app, I can back up without problem, as long as it is using, it failed, is there any way I can release database before backup?

     

    thanks

    Thursday, September 11, 2008 9:22 PM

  • Hi Papy,

    This is the error I got

    Error for backup Backup failed for Server 'AKASH-LAPTOP\SQLEXPRESS'.
    StackTrace :    at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
       at SqlBackUp.Form1.btnCreate_Click(Object sender, EventArgs e) in C:\Documents and Settings\Akash\Desktop\SQL Server 2005 Database Backup\SqlBackUp\SqlBackUp\Form1.cs:line 106
    Inner1 : An exception occurred while executing a Transact-SQL statement or batch.
    Inner2 : Cannot open backup device 'C:\Documents and Settings\Akash\My Documents\Backup.bak'. Operating system error 5(Access is denied.).
    BACKUP DATABASE is terminating abnormally.

    Thanks
    Akash

    Wednesday, November 12, 2008 12:22 PM
  • what is your windows system? turn off your user account control (UAC)

     

    Wednesday, November 12, 2008 2:49 PM
  • after turn off your user account control (UAC) nothing happened

    thank you for help

     

    Friday, August 05, 2011 8:21 PM
  • Nothing happened could you help me i face the same problem

     


    Friday, August 05, 2011 8:44 PM
  •         private void CreateBackup()
            {
                SqlCommand CreateTableCommand = new SqlCommand();


                CreateTableCommand.Connection = SourceConnection;


                CreateTableCommand.CommandType = CommandType.Text;




                CreateTableCommand.CommandText = "BACKUP DATABASE Northwind "+ 
                                               "TO  DISK = \'C:\\backup\\t1.bak'"+ 
                                                    "WITH "+ 
                                               "NOFORMAT,"+ 
                                               "COMPRESSION,"+
                                               "NOINIT,"+
                                               "NAME = N'northwind-Full Database Backup',"+ 
                                               "SKIP,"+
                                               "STATS = 10";


                CreateTableCommand.Connection.Open();
                CreateTableCommand.ExecuteNonQuery(); 
                CreateTableCommand.Connection.Close();
            }

    Simple way to create backup no need to add big dll in app..i want to ask pro here is that alrigh or i should use that  SMO pls quide me 

    Tuesday, March 06, 2012 11:10 AM