Hello there
On beggining of any month i:
1. Backup the database,
2. Restore it as diffrent name
3. Run some actions that change the database
So far i have store procedure for the the changes only
Is there a way to build code/script that do the backup action and the
restore as diffrent name action?
..
roy@.atidsm.co.il
: 03-5611606
' 050-7709399Roy
> Is there a way to build code/script that do the backup action and the
> restore as diffrent name action?
If I understand you correctly , you need to write a stored procedure that
does backup of the database and the restore the database with a differnet
name. Am I right?
CREATE PROC myProc_Backup
AS
DECLARE @.FileName AS VARCHAR(255)
DECLARE @.Date AS VARCHAR(20)
SELECT @.Date =SELECT CONVERT(VARCHAR(50),GETDATE(),112)
SELECT @.FileName = 'N:\MyFolder\DatabaseName' + @.Date+'.bak'
BACKUP DATABASE DatabaseName
TO DISK = @.FileName
CREATE PROC myProc_Restore
AS
DECLARE @.FileName AS VARCHAR(255)
DECLARE @.Date AS VARCHAR(20)
SELECT @.Date =SELECT CONVERT(VARCHAR(50),GETDATE(),112)
SELECT @.FileName = 'N:\MyFolder\DatabaseName' + @.Date+'.bak'
RESTORE DATABASE DatabaseName_New FROM DISK= @.FileNameWITH RECOVERY,
MOVE 'DataBase_Data' TO 'N:\Program Files\Microsoft SQL
Server\MSSQL\Data\DataBase_data.mdf',
MOVE 'DataBase_Log' TO 'N:\Program Files\Microsoft SQL
Server\MSSQL\Data\DataBase__log.ldf'
Note: Create a job with two steps (backup/restore) and schedule it. Please,
make sure that you cannot restore operation while users perform some
activities at the database, it must be in SINGLE USER mode and before
RESTORE you need to drop an old database. Look , I don't need your business
requirements ,sothe logic might be changed.
If you want the SP to accept some parameters as Backup's Path or something
like this you can rewrite it
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:e$ZpbwUsFHA.2520@.TK2MSFTNGP10.phx.gbl...
> Hello there
> On beggining of any month i:
> 1. Backup the database,
> 2. Restore it as diffrent name
> 3. Run some actions that change the database
> So far i have store procedure for the the changes only
> Is there a way to build code/script that do the backup action and the
> restore as diffrent name action?
>
> --
>
> ..
> roy@.atidsm.co.il
> : 03-5611606
> ' 050-7709399
>
Showing posts with label code. Show all posts
Showing posts with label code. Show all posts
Monday, March 19, 2012
Sunday, March 11, 2012
BackUp database
Hi all
I use this code to bak up database
and its working fine but i want run this code via ASP
can anyone write it for me in ASP format?
USE master
EXEC sp_addumpdevice 'disk', 'test_2',
'C:\test_2.dat'
USE master
EXEC sp_addumpdevice 'disk', 'testlog',
'C:\testlog.dat'
BACKUP DATABASE test TO test_2
BACKUP LOG test TO testlog
thanksAll you need to do is put each TSQL command as relevant ASP command.
In your case, you need to execute 4 commands so you would need
SQLStmt, SQLstmt1,2,3 and 4 lots of RS = Connection.Execute(SQLStmt)
e.g. - example of one connection & execution :
Dim UID
Dim SQLStmt, SQLStmt1, SQLStmt2, SQLStmt3
Dim Connection,
Dim RS, RS1, RS2, RS3
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "PROVIDER=SQLOLEDB;DATA SOURCE=server1;UID=test;PWD=password;DATABASE=data base1"
SQLStmt = "SELECT * FROM TBL_Test where server_name = 'SERVER1' and job_run_time > (getdate() - 7)"
Set RS = Connection.Execute(SQLStmt)
I use this code to bak up database
and its working fine but i want run this code via ASP
can anyone write it for me in ASP format?
USE master
EXEC sp_addumpdevice 'disk', 'test_2',
'C:\test_2.dat'
USE master
EXEC sp_addumpdevice 'disk', 'testlog',
'C:\testlog.dat'
BACKUP DATABASE test TO test_2
BACKUP LOG test TO testlog
thanksAll you need to do is put each TSQL command as relevant ASP command.
In your case, you need to execute 4 commands so you would need
SQLStmt, SQLstmt1,2,3 and 4 lots of RS = Connection.Execute(SQLStmt)
e.g. - example of one connection & execution :
Dim UID
Dim SQLStmt, SQLStmt1, SQLStmt2, SQLStmt3
Dim Connection,
Dim RS, RS1, RS2, RS3
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "PROVIDER=SQLOLEDB;DATA SOURCE=server1;UID=test;PWD=password;DATABASE=data base1"
SQLStmt = "SELECT * FROM TBL_Test where server_name = 'SERVER1' and job_run_time > (getdate() - 7)"
Set RS = Connection.Execute(SQLStmt)
Sunday, February 12, 2012
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
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
>
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
>
Subscribe to:
Posts (Atom)