Backup command and getting progress like Enterprise/Management Studio

Sorry if this is the wrong place for this, but I couldn't find a better place...

I have a .net application that calls a TSQL command to backup the database when the user clicks a button. I would like to give the user incremental feedback on what's going on.

So I hooked into the connection's InfoMessage event.

It gets called at the end of the command, not during. I read somewhere that you're supposed to be able to add "WITH NOWAIT" to a TSQL command to have it return immediately and then use the InfoMessage for updates, but the Backup command doesn't support it.

I also set a trace on the SQL Server and told SQL Server to backup using Enterprise manager hoping to catch what it was doing special to get these commands, and didn't see anything unique.

Does anyone have suggestions? I really need to show the progress of what's going on.

Thanks!

SqlClient unfortunately does not support retrieving of messages asynchronously during command execution. You can do this however if you use System.Data.Odbc instead. The steps are pretty much the same. This can also be done if you use C/C++ and use ODBC/OLEDB/SNAC.

Backup command and getting progress like Enterprise/Management Studio

Sorry if this is the wrong place for this, but I couldn't find a better place...

I have a .net application that calls a TSQL command to backup the database when the user clicks a button. I would like to give the user incremental feedback on what's going on.

So I hooked into the connection's InfoMessage event.

It gets called at the end of the command, not during. I read somewhere that you're supposed to be able to add "WITH NOWAIT" to a TSQL command to have it return immediately and then use the InfoMessage for updates, but the Backup command doesn't support it.

I also set a trace on the SQL Server and told SQL Server to backup using Enterprise manager hoping to catch what it was doing special to get these commands, and didn't see anything unique.

Does anyone have suggestions? I really need to show the progress of what's going on.

Thanks!

SqlClient unfortunately does not support retrieving of messages asynchronously during command execution. You can do this however if you use System.Data.Odbc instead. The steps are pretty much the same. This can also be done if you use C/C++ and use ODBC/OLEDB/SNAC.

backing up using sql-dmo

Hi,
I am trying to backkup my app's database using SQL - DMO in vb.net. Here is my code, the code complains about the backupdevice not present. I just want to back the db to my D drive with name as myBackup.bak, so the full path will be D:\myBackup.bak.
Public WithEvents dmoBackup As New SQLDMO.Backup
Dim svr As New SQLDMO.SQLServer
svr.LoginTimeout = 30
svr.Connect("(local)\MyInstanceName", "MyUser", "MyPassword")
dmoBackup.Action = SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database
dmoBackup.Database = "MyDatabase"
dmoBackup.Files = "D:\myBackup.bak"
dmoBackup.Devices = dmoBackup.Files
dmoBackup.SQLBackup(svr)
The code is able to create server object fine, but crashes at the last line. This is the entire code I have, I haven't created any backup device, what should I do to make this work.
Please note that my backup restore using the same database and user etc in T-SQL works fine. I just need DMO to show progress bar to the user while backing up the db.
Thanks
dev
hi dev,
"dev_kh" <devkh@.discussions.microsoft.com> ha scritto nel messaggio
news:F5DFA76A-9458-4E45-A772-9CA53D481D7A@.microsoft.com...
> Hi,
>...
> Public WithEvents dmoBackup As New SQLDMO.Backup
> Dim svr As New SQLDMO.SQLServer
> svr.LoginTimeout = 30
> svr.Connect("(local)\MyInstanceName", "MyUser", "MyPassword")
> dmoBackup.Action = SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database
> dmoBackup.Database = "MyDatabase"
> dmoBackup.Files = "D:\myBackup.bak"
remove this line
> dmoBackup.Devices = dmoBackup.Files <--

> dmoBackup.SQLBackup(svr)
>
and it shoul'd be fine...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea. This works but there is a problem. Here me out:
1> If I pass the path as "D:\myBackup.bak" then it backsup fine.
2> If I pass the path as "D:\OnWord\myBackup.bak" then it backsup fine.
3> But if I pass the path as "D:\Two Words\myBackup.bak" then it complains saying "cannot open backup device..".
So basically if any directory in the path as space in between chars, then the backup fails.. Is this a know issue. How do I resolve it. In actual terms, my path is something like:
"C:\Program Files\..."
Please help.
dev
"Andrea Montanari" wrote:

> hi dev,
> "dev_kh" <devkh@.discussions.microsoft.com> ha scritto nel messaggio
> news:F5DFA76A-9458-4E45-A772-9CA53D481D7A@.microsoft.com...
> remove this line
>
> and it shoul'd be fine...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi dev,
"dev_kh" <devkh@.discussions.microsoft.com> ha scritto nel messaggio
news:D4641BED-0740-480C-8294-C16B88A5FF09@.microsoft.com...
> Thanks Andrea. This works but there is a problem. Here me out:
> 1> If I pass the path as "D:\myBackup.bak" then it backsup fine.
> 2> If I pass the path as "D:\OnWord\myBackup.bak" then it backsup fine.
> 3> But if I pass the path as "D:\Two Words\myBackup.bak" then it complains
saying "cannot open backup device..".
> So basically if any directory in the path as space in between chars, then
the backup fails.. Is this a know issue. How do I resolve it. In actual
terms, my path is something like:
enclose it in square brackets like .Files = "[D:\Two Words\my Backup.bak]"
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

backing up using sql-dmo

Hi,
I am trying to backkup my app's database using SQL - DMO in vb.net. Here is my code, the code complains about the backupdevice not present. I just want to back the db to my D drive with name as myBackup.bak, so the full path will be D:\myBackup.bak.
Public WithEvents dmoBackup As New SQLDMO.Backup
Dim svr As New SQLDMO.SQLServer
svr.LoginTimeout = 30
svr.Connect("(local)\MyInstanceName", "MyUser", "MyPassword")
dmoBackup.Action = SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database
dmoBackup.Database = "MyDatabase"
dmoBackup.Files = "D:\myBackup.bak"
dmoBackup.Devices = dmoBackup.Files
dmoBackup.SQLBackup(svr)
The code is able to create server object fine, but crashes at the last line. This is the entire code I have, I haven't created any backup device, what should I do to make this work.
Please note that my backup restore using the same database and user etc in T-SQL works fine. I just need DMO to show progress bar to the user while backing up the db.
Thanks
dev
hi dev,
"dev_kh" <devkh@.discussions.microsoft.com> ha scritto nel messaggio
news:F5DFA76A-9458-4E45-A772-9CA53D481D7A@.microsoft.com...
> Hi,
>...
> Public WithEvents dmoBackup As New SQLDMO.Backup
> Dim svr As New SQLDMO.SQLServer
> svr.LoginTimeout = 30
> svr.Connect("(local)\MyInstanceName", "MyUser", "MyPassword")
> dmoBackup.Action = SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database
> dmoBackup.Database = "MyDatabase"
> dmoBackup.Files = "D:\myBackup.bak"
remove this line
> dmoBackup.Devices = dmoBackup.Files <--

> dmoBackup.SQLBackup(svr)
>
and it shoul'd be fine...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea. This works but there is a problem. Here me out:
1> If I pass the path as "D:\myBackup.bak" then it backsup fine.
2> If I pass the path as "D:\OnWord\myBackup.bak" then it backsup fine.
3> But if I pass the path as "D:\Two Words\myBackup.bak" then it complains saying "cannot open backup device..".
So basically if any directory in the path as space in between chars, then the backup fails.. Is this a know issue. How do I resolve it. In actual terms, my path is something like:
"C:\Program Files\..."
Please help.
dev
"Andrea Montanari" wrote:

> hi dev,
> "dev_kh" <devkh@.discussions.microsoft.com> ha scritto nel messaggio
> news:F5DFA76A-9458-4E45-A772-9CA53D481D7A@.microsoft.com...
> remove this line
>
> and it shoul'd be fine...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi dev,
"dev_kh" <devkh@.discussions.microsoft.com> ha scritto nel messaggio
news:D4641BED-0740-480C-8294-C16B88A5FF09@.microsoft.com...
> Thanks Andrea. This works but there is a problem. Here me out:
> 1> If I pass the path as "D:\myBackup.bak" then it backsup fine.
> 2> If I pass the path as "D:\OnWord\myBackup.bak" then it backsup fine.
> 3> But if I pass the path as "D:\Two Words\myBackup.bak" then it complains
saying "cannot open backup device..".
> So basically if any directory in the path as space in between chars, then
the backup fails.. Is this a know issue. How do I resolve it. In actual
terms, my path is something like:
enclose it in square brackets like .Files = "[D:\Two Words\my Backup.bak]"
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||IT WORKED!!!
Thanks Andrea. You are a genius.
dev
"Andrea Montanari" wrote:

> hi dev,
> "dev_kh" <devkh@.discussions.microsoft.com> ha scritto nel messaggio
> news:D4641BED-0740-480C-8294-C16B88A5FF09@.microsoft.com...
> saying "cannot open backup device..".
> the backup fails.. Is this a know issue. How do I resolve it. In actual
> terms, my path is something like:
> enclose it in square brackets like .Files = "[D:\Two Words\my Backup.bak]"
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Hi Andrea and others,
Earlier I was using T-SQL for backing up and rstoring the database. Now I need the progress bar so I am using the DMO. Is there anything I am loosing in terms of quality, functionality etc by using DMO in this case.
Thanks
dev
"dev_kh" wrote:
[vbcol=seagreen]
> IT WORKED!!!
> Thanks Andrea. You are a genius.
> dev
> "Andrea Montanari" wrote:
|||hi dev,
"dev_kh" <devkh@.discussions.microsoft.com> ha scritto nel messaggio
news:19BC601B-575B-4151-A86D-192D558B0194@.microsoft.com...
> But I am using my own MSDE instance and thus I believe the dmo's dll will
stay within it and
>wouldn't cause any versioning havocs.. right.
on the server where MSDE has been installed it will be available "for free",
but you have to distribute it to ALL your clients across the LAN...
and possibily updating them when a new service pack of MSDE will be
available (later this year)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea I am not sure what you implied by saying "available for free".. Please elaborate. Also how does this address the versioning issue you mentioned in your previous post.
Thanks
dev
"Andrea Montanari" wrote:

> hi dev,
> "dev_kh" <devkh@.discussions.microsoft.com> ha scritto nel messaggio
> news:19BC601B-575B-4151-A86D-192D558B0194@.microsoft.com...
> stay within it and
> on the server where MSDE has been installed it will be available "for free",
> but you have to distribute it to ALL your clients across the LAN...
> and possibily updating them when a new service pack of MSDE will be
> available (later this year)
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi dev,
"dev_kh" <devkh@.discussions.microsoft.com> ha scritto nel messaggio
news:926B2832-40A6-47B7-A664-C4BFE54D8C80@.microsoft.com...
> Andrea I am not sure what you implied by saying "available for free"..
Please elaborate.
"for free" was intended as sql-dmo will be installed by MSDE setup, and
upgraded to next service pack as well, with no need from you to do it... but
you'll be asked to install, along with your app(s), sqldmo related
dependencies on all other clients... as long as providing a way to install
service packs as well...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||1> You mean the clients which use my app.. right.
2> Which are the dependent dlls' of dmo
3> This gives me an opportunity to post about one other issue, imagine a small office setup with 4 users who want the database on 5th machine. Will I need to install MSDE on all five machines or just on 4 user machines (and just attach on them the databa
se which resides on the 5th machine).
Thanks
dev
"Andrea Montanari" wrote:

> hi dev,
> "dev_kh" <devkh@.discussions.microsoft.com> ha scritto nel messaggio
> news:926B2832-40A6-47B7-A664-C4BFE54D8C80@.microsoft.com...
> Please elaborate.
> "for free" was intended as sql-dmo will be installed by MSDE setup, and
> upgraded to next service pack as well, with no need from you to do it... but
> you'll be asked to install, along with your app(s), sqldmo related
> dependencies on all other clients... as long as providing a way to install
> service packs as well...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Backing up the sql database(*.bak) and restoring the Database in VB.net

Hi There,

I want to know how to backup and restore database from VB.net. ie I am trying to put two buttons on my form, Backup and Restore. When I click on Backup a Backup of the SQL Database with extension of *.bak is created. Similarly when I click on restore and select a database with *.bak extension it should restore that database. I saw a software where it created a backup of Sql database with extension of .zip containing the bak file which is password protected. when u restore the database it automatically gets unzipped and the database is restored. Can anyone help me.

Thanks in advance.

Regards,

Amit

You can create stored procedure to perform backup/restore, then call the stored procedure from your code. For example:

use master
go

CREATE PROC sp_backup @.path sysname,@.dbname sysname,
@.dataDevice varchar(100),@.logDevice varchar(100)
as
DECLARE @.fullFileName sysname
SET @.fullFileName=@.path+@.dataDevice
--Create the data backup device.
EXEC sp_addumpdevice 'disk', @.dataDevice,@.fullFileName

--Create the log backup device.
SET @.fullFileName=@.path+@.logDevice
EXEC sp_addumpdevice 'disk', @.logDevice,@.fullFileName

-- Back up the full MyNwind database.
EXEC('BACKUP DATABASE'+@.dbname+' TO'+@.dataDevice)

-- Update activity has occurred since the full database backup.

-- Back up the log of the MyNwind database.
EXEC('BACKUP LOG'+@.dbname+' TO'+@.logDevice)
go

sp_backup 'c:\','Northwind','test_Data','test_Log'

Then call the sp:

string connectionString = @."Data Source=Confute\SQL2000;Initial Catalog=master;Integrated Security=SSPI;";

using (SqlConnection connection =
new SqlConnection(connectionString))
{

SqlCommand command = new SqlCommand("EXEC sp_backup @.path ,@.dbname,
@.dataDevice,@.logDevice", connection);
command.Connection.Open();

command.Parameters.Add("@.path", textBox1.Text)

//add all parameters here
command.ExecuteNonQuery();

}