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

Answer 1

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

Answer 2

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

 

Answer 3

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

 

Answer 4

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

 

Answer 5

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

 

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

what is your windows system? turn off your user account control (UAC)

 

Answer 10

after turn off your user account control (UAC) nothing happened

thank you for help

 

Answer 11

Nothing happened could you help me i face the same problem

 

Answer 12

        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