Backup SQL Database files with vb.net 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Backup SQL Database files with vb.net 2005

Author

Topic

TimKellSSi
Starting Member

4 Posts

Posted - 2008-03-17 : 14:06:34

I am trying to backup the sql database files from a local computer to a server location through vb.net 2005. Is there a simple way to do this? Every time I try to detach the database, I cannot because the database is in use. Is there something I am missing with this?

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-17 : 23:51:27

Why don't do db backup? It's online operation.

TimKellSSi
Starting Member

4 Posts

Posted - 2008-03-20 : 09:27:29

I'm able to create a backup file using the following code
Dim a As SQLDMO.Backup
Dim svr As SQLDMO._SQLServer
Try
a = New SQLDMO.BackupClass()
svr = New SQLDMO.SQLServerClass()

svr.LoginSecure = False
svr.Connect(ServerName, ADMIN_UID, ADMIN_PWD)

a.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database
a.Database = dbName
a.Files = path & BACKUP_FILE
a.BackupSetName = dbName
a.BackupSetDescription = "Database backup"
a.Initialize = True
a.SQLBackup(svr)

svr.DisConnect()
svr = Nothing
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

The above code runs with no problem and creates the backup file. However, when I try to run the restore code:

Dim restore As SQLDMO.Restore
Dim svr As SQLDMO._SQLServer

Try
restore = New SQLDMO.RestoreClass()
svr = New SQLDMO.SQLServerClass()

svr.LoginSecure = False
svr.Connect(ServerName, ADMIN_UID, ADMIN_PWD)

restore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database
restore.Database = dbName
restore.Files = path & BACKUP_FILE
restore.BackupSetName = dbName
restore.FileNumber = 1
restore.SQLRestore(svr)

svr.DisConnect()
svr = Nothing
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

I get an error stating that the BackupSetName is not supported by the Restore object.
What am I missing with this?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-20 : 09:36:30

Not sure what's going on without seeing the specific error and the specific code that calls the code you are showing us.

Seems to work just fine, as in this example here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2553505&SiteID=1

- Jeff
http://weblogs.sqlteam.com/JeffS

TimKellSSi
Starting Member

4 Posts

Posted - 2008-03-20 : 11:08:50

Thanks for the input so far. In the example you've shown, there is a line: sqlserver.Connect("192.168.19.25","sa","sa"). What is this IP address? Is it the local computer when SQL Server is, or is it the server where the backup is? I've tried using the local server by name and here is the error I get:

System.Runtime.InteropServices.COMException (0x80045068): [SQL-DMO]BackupSetName property is not supported by the Restore object.
at SQLDMO.RestoreClass.set_BackupSetName(String pRetVal)
at Main.Restore(String dbName, String path) in C:\dev\VB2005.NET\Solution\Project\Main.vb:line 255

When I use the IP address listed above, I just time out.

Thanks

TimKellSSi
Starting Member

4 Posts

Posted - 2008-03-20 : 11:37:47

I would like to those that helped me with this problem.

I simply commented out the following line:
restore.BackupSetName = dbName

and the restore worked fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources