Hi,
SQL 2000 had an example for using DMO programing with VB to restore and
backup a database, which named BackRestEvents.
I want to extend this project to build a tool, which can restore from a
backup file to a new database (new name). But I don't know, how I can set
the database filenames to another name (such as test1_data.mdf and
test1_log..ldf), because the database test (with test_data.mdf and
test_log..ldf) still exists on the system.
How can I do?
The backup file serves only as a template to create a new database. Is there
another way?
I use MSDE 2000, I don't have any tool such as Enterprise manager or SQL
Analyzer.
Thanks for help
Martin
hi Martin,
Martin wrote:
> Hi,
> SQL 2000 had an example for using DMO programing with VB to restore
> and backup a database, which named BackRestEvents.
> I want to extend this project to build a tool, which can restore from
> a backup file to a new database (new name). But I don't know, how I
> can set the database filenames to another name (such as
> test1_data.mdf and test1_log..ldf), because the database test (with
> test_data.mdf and test_log..ldf) still exists on the system.
> How can I do?
assuming your relative T-SQL syntax is
RESTORE DATABASE [Pubs2] FROM DISK = N'C:\Pubs.bak' WITH FILE = 1,
NOUNLOAD ,
STATS = 10,
RECOVERY ,
MOVE N'pubs' TO N'C:\Programmi\Microsoft SQL Server\MSSQL\Data\pubs2.mdf',
MOVE N'pubs_log' TO N'C:\Programmi\Microsoft SQL
Server\MSSQL\Data\pubs_log2.ldf'
where you restore to Pubs2 database and move file to other location, you
have to specify the
oRestore.RelocateFiles = "[pubs],[C:\Programmi\Microsoft SQL
Server\MSSQL\Data\pubs2.mdf],[pubs_log],[C:\Programmi\Microsoft SQL
Server\MSSQL\Data\pubs_log2.ldf]"
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Showing posts with label dmo. Show all posts
Showing posts with label dmo. Show all posts
Friday, March 30, 2012
Tuesday, March 20, 2012
restore database
I trying to rrestore a database from BAK file and I get an error microsoft
sql -dmo (odbc sqlstate:4200).
device activation error the physical file *.ldf/*.mdf may be incorrect.
what I am doing wrong.
someone can help me.
Thanks.
Amos
It is probably because you have specified a wrong path to the log file.
I assume you do have enough permissions to the folder.
"amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
news:B2517B74-F209-466D-9124-A49E6CAB6EDA@.microsoft.com...
>I trying to rrestore a database from BAK file and I get an error microsoft
> sql -dmo (odbc sqlstate:4200).
> device activation error the physical file *.ldf/*.mdf may be incorrect.
> what I am doing wrong.
> someone can help me.
> Thanks.
>
|||Is the backup coming from anouher server ?
Try out "restore headeronly" on the .BAK-File and check the results.
Then use the "MOVE" - option on restore to put the data- and log- files
to valid places - as Uri said,
the pahtes must be existing before restore.
|||Uri thnks you for your help.
but I do not understand what I need to do according to your advice.
because the path create automaticly
in the "option" tab when I select device in the "general" tab.
c:\program files\microsoft sql server\mssql\data\anyfile.bak
"Uri Dimant" wrote:
> Amos
> It is probably because you have specified a wrong path to the log file.
> I assume you do have enough permissions to the folder.
>
>
> "amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
> news:B2517B74-F209-466D-9124-A49E6CAB6EDA@.microsoft.com...
>
>
|||Hi Amos,
I see - you are using Enterprise Manager (EM).
So you probably selected "from device" on the restore menu and
navigated to the .BAK file.
EM then reads the information in the header of the .BAK file and wants
to put the files in the same
place where they existed when the DB was being backed up.
You will need to go to "options" and click *into* the lines specifying
the files to restore ("move to physical file")
You can *change* this information.
Change them according to your system (e.g. c:\programs\... or
d:\programs or whatsoever).
|||Hi mattp,
I am a asp.net developer and I get a bak file to restore in my server in the
localhost for test.
I am don't have a DBA manager but I get with the file a text file that
explain what to do I doing step by step by the page and I get this error.
I don't thinks that I need to change the place of the files (ldf,mdf).
because in the default the wizard place them in C:\Program Files\Microsoft
SQL Server\MSSQL\Data when I select the bak file in the general tab,and in
the guide page write the same place.
maybe you want that I place the files in the other place.
"mattp" wrote:
> Hi Amos,
> I see - you are using Enterprise Manager (EM).
> So you probably selected "from device" on the restore menu and
> navigated to the .BAK file.
> EM then reads the information in the header of the .BAK file and wants
> to put the files in the same
> place where they existed when the DB was being backed up.
> You will need to go to "options" and click *into* the lines specifying
> the files to restore ("move to physical file")
> You can *change* this information.
> Change them according to your system (e.g. c:\programs\... or
> d:\programs or whatsoever).
>
|||Do these files already exist there ?
--> then also "checkmark" the option "restore over existing database".
Is there enough space on the drive ?
sql -dmo (odbc sqlstate:4200).
device activation error the physical file *.ldf/*.mdf may be incorrect.
what I am doing wrong.
someone can help me.
Thanks.
Amos
It is probably because you have specified a wrong path to the log file.
I assume you do have enough permissions to the folder.
"amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
news:B2517B74-F209-466D-9124-A49E6CAB6EDA@.microsoft.com...
>I trying to rrestore a database from BAK file and I get an error microsoft
> sql -dmo (odbc sqlstate:4200).
> device activation error the physical file *.ldf/*.mdf may be incorrect.
> what I am doing wrong.
> someone can help me.
> Thanks.
>
|||Is the backup coming from anouher server ?
Try out "restore headeronly" on the .BAK-File and check the results.
Then use the "MOVE" - option on restore to put the data- and log- files
to valid places - as Uri said,
the pahtes must be existing before restore.
|||Uri thnks you for your help.
but I do not understand what I need to do according to your advice.
because the path create automaticly
in the "option" tab when I select device in the "general" tab.
c:\program files\microsoft sql server\mssql\data\anyfile.bak
"Uri Dimant" wrote:
> Amos
> It is probably because you have specified a wrong path to the log file.
> I assume you do have enough permissions to the folder.
>
>
> "amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
> news:B2517B74-F209-466D-9124-A49E6CAB6EDA@.microsoft.com...
>
>
|||Hi Amos,
I see - you are using Enterprise Manager (EM).
So you probably selected "from device" on the restore menu and
navigated to the .BAK file.
EM then reads the information in the header of the .BAK file and wants
to put the files in the same
place where they existed when the DB was being backed up.
You will need to go to "options" and click *into* the lines specifying
the files to restore ("move to physical file")
You can *change* this information.
Change them according to your system (e.g. c:\programs\... or
d:\programs or whatsoever).
|||Hi mattp,
I am a asp.net developer and I get a bak file to restore in my server in the
localhost for test.
I am don't have a DBA manager but I get with the file a text file that
explain what to do I doing step by step by the page and I get this error.
I don't thinks that I need to change the place of the files (ldf,mdf).
because in the default the wizard place them in C:\Program Files\Microsoft
SQL Server\MSSQL\Data when I select the bak file in the general tab,and in
the guide page write the same place.
maybe you want that I place the files in the other place.
"mattp" wrote:
> Hi Amos,
> I see - you are using Enterprise Manager (EM).
> So you probably selected "from device" on the restore menu and
> navigated to the .BAK file.
> EM then reads the information in the header of the .BAK file and wants
> to put the files in the same
> place where they existed when the DB was being backed up.
> You will need to go to "options" and click *into* the lines specifying
> the files to restore ("move to physical file")
> You can *change* this information.
> Change them according to your system (e.g. c:\programs\... or
> d:\programs or whatsoever).
>
|||Do these files already exist there ?
--> then also "checkmark" the option "restore over existing database".
Is there enough space on the drive ?
restore database
I trying to rrestore a database from BAK file and I get an error microsoft
sql -dmo (odbc sqlstate:4200).
device activation error the physical file *.ldf/*.mdf may be incorrect.
what I am doing wrong.
someone can help me.
Thanks.Amos
It is probably because you have specified a wrong path to the log file.
I assume you do have enough permissions to the folder.
"amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
news:B2517B74-F209-466D-9124-A49E6CAB6EDA@.microsoft.com...
>I trying to rrestore a database from BAK file and I get an error microsoft
> sql -dmo (odbc sqlstate:4200).
> device activation error the physical file *.ldf/*.mdf may be incorrect.
> what I am doing wrong.
> someone can help me.
> Thanks.
>|||Is the backup coming from anouher server ?
Try out "restore headeronly" on the .BAK-File and check the results.
Then use the "MOVE" - option on restore to put the data- and log- files
to valid places - as Uri said,
the pahtes must be existing before restore.|||Uri thnks you for your help.
but I do not understand what I need to do according to your advice.
because the path create automaticly
in the "option" tab when I select device in the "general" tab.
c:\program files\microsoft sql server\mssql\data\anyfile.bak
"Uri Dimant" wrote:
> Amos
> It is probably because you have specified a wrong path to the log file.
> I assume you do have enough permissions to the folder.
>
>
> "amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
> news:B2517B74-F209-466D-9124-A49E6CAB6EDA@.microsoft.com...
> >I trying to rrestore a database from BAK file and I get an error microsoft
> > sql -dmo (odbc sqlstate:4200).
> > device activation error the physical file *.ldf/*.mdf may be incorrect.
> > what I am doing wrong.
> > someone can help me.
> > Thanks.
> >
>
>|||Hi Amos,
I see - you are using Enterprise Manager (EM).
So you probably selected "from device" on the restore menu and
navigated to the .BAK file.
EM then reads the information in the header of the .BAK file and wants
to put the files in the same
place where they existed when the DB was being backed up.
You will need to go to "options" and click *into* the lines specifying
the files to restore ("move to physical file")
You can *change* this information.
Change them according to your system (e.g. c:\programs\... or
d:\programs or whatsoever).|||Hi mattp,
I am a asp.net developer and I get a bak file to restore in my server in the
localhost for test.
I am don't have a DBA manager but I get with the file a text file that
explain what to do I doing step by step by the page and I get this error.
I don't thinks that I need to change the place of the files (ldf,mdf).
because in the default the wizard place them in C:\Program Files\Microsoft
SQL Server\MSSQL\Data when I select the bak file in the general tab,and in
the guide page write the same place.
maybe you want that I place the files in the other place.
"mattp" wrote:
> Hi Amos,
> I see - you are using Enterprise Manager (EM).
> So you probably selected "from device" on the restore menu and
> navigated to the .BAK file.
> EM then reads the information in the header of the .BAK file and wants
> to put the files in the same
> place where they existed when the DB was being backed up.
> You will need to go to "options" and click *into* the lines specifying
> the files to restore ("move to physical file")
> You can *change* this information.
> Change them according to your system (e.g. c:\programs\... or
> d:\programs or whatsoever).
>|||Do these files already exist there ?
--> then also "checkmark" the option "restore over existing database".
Is there enough space on the drive ?
sql -dmo (odbc sqlstate:4200).
device activation error the physical file *.ldf/*.mdf may be incorrect.
what I am doing wrong.
someone can help me.
Thanks.Amos
It is probably because you have specified a wrong path to the log file.
I assume you do have enough permissions to the folder.
"amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
news:B2517B74-F209-466D-9124-A49E6CAB6EDA@.microsoft.com...
>I trying to rrestore a database from BAK file and I get an error microsoft
> sql -dmo (odbc sqlstate:4200).
> device activation error the physical file *.ldf/*.mdf may be incorrect.
> what I am doing wrong.
> someone can help me.
> Thanks.
>|||Is the backup coming from anouher server ?
Try out "restore headeronly" on the .BAK-File and check the results.
Then use the "MOVE" - option on restore to put the data- and log- files
to valid places - as Uri said,
the pahtes must be existing before restore.|||Uri thnks you for your help.
but I do not understand what I need to do according to your advice.
because the path create automaticly
in the "option" tab when I select device in the "general" tab.
c:\program files\microsoft sql server\mssql\data\anyfile.bak
"Uri Dimant" wrote:
> Amos
> It is probably because you have specified a wrong path to the log file.
> I assume you do have enough permissions to the folder.
>
>
> "amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
> news:B2517B74-F209-466D-9124-A49E6CAB6EDA@.microsoft.com...
> >I trying to rrestore a database from BAK file and I get an error microsoft
> > sql -dmo (odbc sqlstate:4200).
> > device activation error the physical file *.ldf/*.mdf may be incorrect.
> > what I am doing wrong.
> > someone can help me.
> > Thanks.
> >
>
>|||Hi Amos,
I see - you are using Enterprise Manager (EM).
So you probably selected "from device" on the restore menu and
navigated to the .BAK file.
EM then reads the information in the header of the .BAK file and wants
to put the files in the same
place where they existed when the DB was being backed up.
You will need to go to "options" and click *into* the lines specifying
the files to restore ("move to physical file")
You can *change* this information.
Change them according to your system (e.g. c:\programs\... or
d:\programs or whatsoever).|||Hi mattp,
I am a asp.net developer and I get a bak file to restore in my server in the
localhost for test.
I am don't have a DBA manager but I get with the file a text file that
explain what to do I doing step by step by the page and I get this error.
I don't thinks that I need to change the place of the files (ldf,mdf).
because in the default the wizard place them in C:\Program Files\Microsoft
SQL Server\MSSQL\Data when I select the bak file in the general tab,and in
the guide page write the same place.
maybe you want that I place the files in the other place.
"mattp" wrote:
> Hi Amos,
> I see - you are using Enterprise Manager (EM).
> So you probably selected "from device" on the restore menu and
> navigated to the .BAK file.
> EM then reads the information in the header of the .BAK file and wants
> to put the files in the same
> place where they existed when the DB was being backed up.
> You will need to go to "options" and click *into* the lines specifying
> the files to restore ("move to physical file")
> You can *change* this information.
> Change them according to your system (e.g. c:\programs\... or
> d:\programs or whatsoever).
>|||Do these files already exist there ?
--> then also "checkmark" the option "restore over existing database".
Is there enough space on the drive ?
Monday, March 12, 2012
restore database
I trying to rrestore a database from BAK file and I get an error microsoft
sql -dmo (odbc sqlstate:4200).
device activation error the physical file *.ldf/*.mdf may be incorrect.
what I am doing wrong.
someone can help me.
Thanks.Amos
It is probably because you have specified a wrong path to the log file.
I assume you do have enough permissions to the folder.
"amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
news:B2517B74-F209-466D-9124-A49E6CAB6EDA@.microsoft.com...
>I trying to rrestore a database from BAK file and I get an error microsoft
> sql -dmo (odbc sqlstate:4200).
> device activation error the physical file *.ldf/*.mdf may be incorrect.
> what I am doing wrong.
> someone can help me.
> Thanks.
>|||Is the backup coming from anouher server ?
Try out "restore headeronly" on the .BAK-File and check the results.
Then use the "MOVE" - option on restore to put the data- and log- files
to valid places - as Uri said,
the pahtes must be existing before restore.|||Uri thnks you for your help.
but I do not understand what I need to do according to your advice.
because the path create automaticly
in the "option" tab when I select device in the "general" tab.
c:\program files\microsoft sql server\mssql\data\anyfile.bak
"Uri Dimant" wrote:
> Amos
> It is probably because you have specified a wrong path to the log file.
> I assume you do have enough permissions to the folder.
>
>
> "amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
> news:B2517B74-F209-466D-9124-A49E6CAB6EDA@.microsoft.com...
>
>|||Hi Amos,
I see - you are using Enterprise Manager (EM).
So you probably selected "from device" on the restore menu and
navigated to the .BAK file.
EM then reads the information in the header of the .BAK file and wants
to put the files in the same
place where they existed when the DB was being backed up.
You will need to go to "options" and click *into* the lines specifying
the files to restore ("move to physical file")
You can *change* this information.
Change them according to your system (e.g. c:\programs\... or
d:\programs or whatsoever).|||Hi mattp,
I am a asp.net developer and I get a bak file to restore in my server in the
localhost for test.
I am don't have a DBA manager but I get with the file a text file that
explain what to do I doing step by step by the page and I get this error.
I don't thinks that I need to change the place of the files (ldf,mdf).
because in the default the wizard place them in C:\Program Files\Microsoft
SQL Server\MSSQL\Data when I select the bak file in the general tab,and in
the guide page write the same place.
maybe you want that I place the files in the other place.
"mattp" wrote:
> Hi Amos,
> I see - you are using Enterprise Manager (EM).
> So you probably selected "from device" on the restore menu and
> navigated to the .BAK file.
> EM then reads the information in the header of the .BAK file and wants
> to put the files in the same
> place where they existed when the DB was being backed up.
> You will need to go to "options" and click *into* the lines specifying
> the files to restore ("move to physical file")
> You can *change* this information.
> Change them according to your system (e.g. c:\programs\... or
> d:\programs or whatsoever).
>|||Do these files already exist there ?
--> then also "checkmark" the option "restore over existing database".
Is there enough space on the drive ?
sql -dmo (odbc sqlstate:4200).
device activation error the physical file *.ldf/*.mdf may be incorrect.
what I am doing wrong.
someone can help me.
Thanks.Amos
It is probably because you have specified a wrong path to the log file.
I assume you do have enough permissions to the folder.
"amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
news:B2517B74-F209-466D-9124-A49E6CAB6EDA@.microsoft.com...
>I trying to rrestore a database from BAK file and I get an error microsoft
> sql -dmo (odbc sqlstate:4200).
> device activation error the physical file *.ldf/*.mdf may be incorrect.
> what I am doing wrong.
> someone can help me.
> Thanks.
>|||Is the backup coming from anouher server ?
Try out "restore headeronly" on the .BAK-File and check the results.
Then use the "MOVE" - option on restore to put the data- and log- files
to valid places - as Uri said,
the pahtes must be existing before restore.|||Uri thnks you for your help.
but I do not understand what I need to do according to your advice.
because the path create automaticly
in the "option" tab when I select device in the "general" tab.
c:\program files\microsoft sql server\mssql\data\anyfile.bak
"Uri Dimant" wrote:
> Amos
> It is probably because you have specified a wrong path to the log file.
> I assume you do have enough permissions to the folder.
>
>
> "amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
> news:B2517B74-F209-466D-9124-A49E6CAB6EDA@.microsoft.com...
>
>|||Hi Amos,
I see - you are using Enterprise Manager (EM).
So you probably selected "from device" on the restore menu and
navigated to the .BAK file.
EM then reads the information in the header of the .BAK file and wants
to put the files in the same
place where they existed when the DB was being backed up.
You will need to go to "options" and click *into* the lines specifying
the files to restore ("move to physical file")
You can *change* this information.
Change them according to your system (e.g. c:\programs\... or
d:\programs or whatsoever).|||Hi mattp,
I am a asp.net developer and I get a bak file to restore in my server in the
localhost for test.
I am don't have a DBA manager but I get with the file a text file that
explain what to do I doing step by step by the page and I get this error.
I don't thinks that I need to change the place of the files (ldf,mdf).
because in the default the wizard place them in C:\Program Files\Microsoft
SQL Server\MSSQL\Data when I select the bak file in the general tab,and in
the guide page write the same place.
maybe you want that I place the files in the other place.
"mattp" wrote:
> Hi Amos,
> I see - you are using Enterprise Manager (EM).
> So you probably selected "from device" on the restore menu and
> navigated to the .BAK file.
> EM then reads the information in the header of the .BAK file and wants
> to put the files in the same
> place where they existed when the DB was being backed up.
> You will need to go to "options" and click *into* the lines specifying
> the files to restore ("move to physical file")
> You can *change* this information.
> Change them according to your system (e.g. c:\programs\... or
> d:\programs or whatsoever).
>|||Do these files already exist there ?
--> then also "checkmark" the option "restore over existing database".
Is there enough space on the drive ?
Friday, March 9, 2012
Restore all data (system must not exit during restore) using SQL DMO
I am using the SQL DMO to do my backup and restore. The restore works fine for one thing. I there is current connections on my database I cannot perform the restore. I need to be logged on to the system to be able to do the restore (restore must be built into the system), but if I log in I cannot do the restore of the database.
Need help ASAP!!
Thanks!!!Is the database you're attempting to restore your default database on the server? If so, change it. You can't restore a database if any connections exist to that database.|||I'll explain to you what exactly needs to be done then you maybe can help me better.
I need to do a full backup and restore of the database. The user with the correct login details will have the ability to perform the backup and restore.
And it is right there where the problem comes in. A user is logged into the system so a connection to the database exist.The "master" database in SQL Server is the default db.
Another option that I am considering is to remove the requirement that a user must be logged in to do a backup. Let me explain how this will be possible. The application is written to function in a server enviroment and I am writen a standalone app to be installed on the server to perform certain tasks of which one is the backup/restore functionality. Seeing that only one or two people will have access to the server, user controll is already managed.
If I do this, it means that no current connections exist on my database and the backup can be performed!
Now you know the exact problem and the two options I have got. I'm not keen on the second option of removing the login, but will do so if I have no other choice.
Any suggestions?
MS|||If the default database of the user is "master" then you shouldn't have this problem. I'd take a look at the server when this user is connected to see what other connections exist. You can be connected to the server and restore a database other than the one you're connected to.|||
I
|||I am taking back up of database ABC (ABC.bak )from Server A. My application need is such that i should be able to restore ABC.bak on server A as well as Server B. My ASP Application is connected to server A. so while restoring back up, db already exists ,I am overwriting the current but different application users are connected to server A. therefore restore fails. What could be the possible solution for this situation?Restore all data (system must not exit during restore) using SQL DMO
I am using the SQL DMO to do my backup and restore. The restore works fine for one thing. I there is current connections on my database I cannot perform the restore. I need to be logged on to the system to be able to do the restore (restore must be built into the system), but if I log in I cannot do the restore of the database.
Need help ASAP!!
Thanks!!!Is the database you're attempting to restore your default database on the server? If so, change it. You can't restore a database if any connections exist to that database.|||I'll explain to you what exactly needs to be done then you maybe can help me better.
I need to do a full backup and restore of the database. The user with the correct login details will have the ability to perform the backup and restore.
And it is right there where the problem comes in. A user is logged into the system so a connection to the database exist.The "master" database in SQL Server is the default db.
Another option that I am considering is to remove the requirement that a user must be logged in to do a backup. Let me explain how this will be possible. The application is written to function in a server enviroment and I am writen a standalone app to be installed on the server to perform certain tasks of which one is the backup/restore functionality. Seeing that only one or two people will have access to the server, user controll is already managed.
If I do this, it means that no current connections exist on my database and the backup can be performed!
Now you know the exact problem and the two options I have got. I'm not keen on the second option of removing the login, but will do so if I have no other choice.
Any suggestions?
MS|||If the default database of the user is "master" then you shouldn't have this problem. I'd take a look at the server when this user is connected to see what other connections exist. You can be connected to the server and restore a database other than the one you're connected to.|||
I
|||I am taking back up of database ABC (ABC.bak )from Server A. My application need is such that i should be able to restore ABC.bak on server A as well as Server B. My ASP Application is connected to server A. so while restoring back up, db already exists ,I am overwriting the current but different application users are connected to server A. therefore restore fails. What could be the possible solution for this situation?
Subscribe to:
Posts (Atom)