Hello,
I found some code below that works fine when I run it in my vb.net code in Design mode. When I publish the application, run the app and then execute the sub MyDbBackup() I get the following error: Cannot open backup device 'C:\dbBackup'. Operating system error 5(Access is denied). BACKUP DATABASE is terminating abnormally.
Here is the code that runs:
Public Sub MyDbBackup()
Dim sqlconn As New SqlClient.SqlConnection(MyConnectionStringdb)
sqlconn.Open()
Dim Backupcommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("BACKUP DATABASE [" & sqlconn.Database.ToString & "] TO DISK = 'c:\myDataBaseBackup.bak'")
Backupcommand.CommandType = CommandType.Text
Backupcommand.Connection = sqlconn
Backupcommand.ExecuteNonQuery()
sqlconn.Close()
End Sub
2 Questions: Why does it backup fine in design mode, but not after it is published? Also, in design mode, my database is called TAP_Master.mdf but when I look under C:\Program Files\Microsoft\ SQL Server\MSSQL.1\MSSQL\Data, I do not see that database name... all I see is master.mdf, model, msdbdata etc... Is that database stored anywhere else?
I have set the MyConnectionStringdb variable to = "Data Source =.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TAP_Master.mdf;Integrated Security=True;User Instance=True"
Thanks in advance.
You don't see your database in the Data directory because you embedded it in your application. The database is installed to the same location where you application is installed, which is an obfuscated path that is generated during ClickOnce installation. ClickOnce applications use a special type of instances of SQL Server called a User Instance to allow the database to be attached at runtime.
I'm not sure about the backup failure, I'll have to do a little more research. Is your published application on a different computer than the Dev box? I'm thinking it has something to do with a backup device that was created on the Dev box, but does not exist on the test box.
Mike
|||Mike Wachal - MSFT wrote:
You don't see your database in the Data directory because you embedded it in your application. The database is installed to the same location where you application is installed, which is an obfuscated path that is generated during ClickOnce installation. ClickOnce applications use a special type of instances of SQL Server called a User Instance to allow the database to be attached at runtime.
I'm not sure about the backup failure, I'll have to do a little more research. Is your published application on a different computer than the Dev box? I'm thinking it has something to do with a backup device that was created on the Dev box, but does not exist on the test box.
Mike
Thanks for your reply. You are correct... My published application is on a different computer than my Dev box. If you can find out any additional info as to why I'm getting that error that would be great.
Also, I'll ask this question as well, Once the bak up file is created, what would be the best method to restore the .bak file to the machine that runs the published application.? I thought I could use the following code, but I receive the message, "Restore cannot process the database C"\Visual Studio\Projects\Prog\BIN\DEBUG\TAP_Master.MDF beause it is in use by this session. It is recommended that the master database be used when performing this operation":
Public Sub MyDbBackup()
Dim sqlconn As New SqlClient.SqlConnection(MyConnectionStringdb)
sqlconn.Open()
Dim Backupcommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("RESTORE DATABASE [" & sqlconn.Database.ToString & "] FROM DISK = 'c:\myDataBaseBackup.bak'")
Backupcommand.CommandType = CommandType.Text
Backupcommand.Connection = sqlconn
Backupcommand.ExecuteNonQuery()
sqlconn.Close()
End Sub
Thanks in advance.
|||
Hi,
I guess that the user instance feature is not made for Backup and restore (with the emphasize on restore) because a restore will do a server attaching after the restoring which is not the sense of a user attached instance. Why don′t you take the MDF as a backup only ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Hi,... Why don′t you take the MDF as a backup only ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Hello, can you elaborate on that quote? How can I take only the MDF as a backup? Basically I want to be able to back up any saved data, and then be able to restore it in case of a system failure or a corrupted operating system.
Thanks for your replies all.
|||Just do a normal file copy when the database connection is closed, then you can easily attach it to a user instance again when the data is corrupt.HTH, Jens K. Suessmeyer.
htttp://www.sqlserver2005.de
|||Ok. This is what I have done. I opened my published app on my laptop, and then entered in some data. I closed my app, and then attempted to copy the master.mdf file from the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory. At first I couldn't copy because SQL server express was still running. So I killed the services and copied the data.
I reinstalled my app, copied back the master.mdf into the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory, and then launched the app. Now I do not see the data that I previously entered.
Am I understanding the File Copy / Attach to user instance concept correctly?
Thanks again for all your help.
|||No actually not, the database which has to be backup is your own database not the SQL Server internal ones. YOu will have to backup the MDF file of the database with the reference TAP_master.mdf you mentioned above.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
The reason you're seeing the backup error is that the user context running your app on the deployed system doesn't have Windows permissions to create a file in the root of the local C: drive. That's why it's different from your dev environment: Different machine, different permissions.
So, either point the backup to a known directory that you're guaranteed to have permission to write to on machines where you're deployed, or find a way to make sure that you have appropriate permissions to the root of C:.
On the restore, it's simply a matter that you can't overwrite a database while you have an active session attached to it. Your connection is that session. By setting focus to Master (use master) you're taking focus away from the database you're trying to restore, and it should work fine.
|||On the restore, it's simply a matter that you can't overwrite a database while you have an active session attached to it. Your connection is that session. By setting focus to Master (use master) you're taking focus away from the database you're trying to restore, and it should work fine.
Ok thanks...First issue resolved... Now with regards to the second issue, Where am I setting focus to the master from? Is it within Visual Studio design mode, or under SQL Server 2005 Surface Area Configuration?
Thanks.
|||Kevin Farlee wrote: On the restore, it's simply a matter that you can't overwrite a database while you have an active session attached to it. Your connection is that session. By setting focus to Master (use master) you're taking focus away from the database you're trying to restore, and it should work fine.
Ok thanks...First issue resolved... Now with regards to the second issue, Where am I setting focus to the master from? Is it within Visual Studio design mode, or under SQL Server 2005 Surface Area Configuration?
Thanks.
The context information is session based, you can change the context from within your script (if you are using one, I am not sure from your explnation using the USE <DatabaseName> command. If you are in any GUI of SQL Management Studio, make sure that you are not residing on a database node in the treeview that you want to restore.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
No comments:
Post a Comment