Restoring SQLServer 7 backup to MSDE

I want to develop an app on my computer running SQLServer 7, then
backup the data and resrote it at the client's running MSDE. When
restoring the backup to MSDE I get this message from SQL-DMO:
Location:upgraddb.cpp:214
Instruction:tableIndex < ARRAY_LEN(upgradeMap)
I've tried it with two backups, one of a 40+ table database and one
with 3 tables, and get the same message both times.
Do you know what this means and how to work around it? Thanks.
hi Larry,
"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
news:095do0da3qccc93i4c59mmc396akdd9tbh@.4ax.com
> I want to develop an app on my computer running SQLServer 7, then
> backup the data and resrote it at the client's running MSDE. When
> restoring the backup to MSDE I get this message from SQL-DMO:
> Location: upgraddb.cpp:214
> Instruction: tableIndex < ARRAY_LEN(upgradeMap)
> I've tried it with two backups, one of a 40+ table database and one
> with 3 tables, and get the same message both times.
> Do you know what this means and how to work around it? Thanks.
you should get that exception when restoring SQL Server/MSDE 2000 databases
on SQL Server 7.0 servers..
you can restore on version 2000 from version 7.0, but not the contrary
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||You indicate I can restore a SQLServer 7.0 backup to an MSDE 2000
database, but that is where I'm getting the error. Am I reading your
reply wrong?
The bigger problem seems to be developing on my computer with
SQLServer 7.0 while my clients are using MSDE. I need to be able to
move the data back and forth easily but it sounds like that's not
really possible. Since I can't (I dont' think) run both SQLServer 7.0
and MSDE on the same machine, it looks like that development concept
won't work. Is that correct, or am I missing something?
On Mon, 1 Nov 2004 21:55:44 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

>hi Larry,
>"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
>news:095do0da3qccc93i4c59mmc396akdd9tbh@.4ax.com
>you should get that exception when restoring SQL Server/MSDE 2000 databases
>on SQL Server 7.0 servers..
>you can restore on version 2000 from version 7.0, but not the contrary
|||hi Larry,
"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
news:nabfo0l29tk7i9bhaa0ndsbktgki3nf9s0@.4ax.com
> ...
> The bigger problem seems to be developing on my computer with
> SQLServer 7.0 while my clients are using MSDE. I need to be able to
> move the data back and forth easily but it sounds like that's not
> really possible. Since I can't (I dont' think) run both SQLServer 7.0
> and MSDE on the same machine, it looks like that development concept
> won't work. Is that correct, or am I missing something?
actually you can... you can have a "default" SQL Server 7.0 instance and a
SQL Server 2000 named instance on the same pc..
keep in mind that, if you only install MSDE 2000 named instance side by side
a SQL Server 7.0 instance, you won't be able to use the SQL Server 7.0
client tools, as MSDE only installs "part of" them, that's to say the type
lybs, dlls, COM dependencies and so on, but not the tools (Enterprise
Manager, Profiler, Query Analyzer).. so you'll be stuck with no tool but
oSql.exe...
if you install SQL Server 2000, the newer tools (and realated dependencies)
will be able to manage SQL Server 7.0 ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I have to apologize...I am running SQLServer 2000. Too much going on.
What difference does that make?
On Tue, 2 Nov 2004 17:55:36 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

>hi Larry,
>"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
>news:nabfo0l29tk7i9bhaa0ndsbktgki3nf9s0@.4ax.com
>actually you can... you can have a "default" SQL Server 7.0 instance and a
>SQL Server 2000 named instance on the same pc..
>keep in mind that, if you only install MSDE 2000 named instance side by side
>a SQL Server 7.0 instance, you won't be able to use the SQL Server 7.0
>client tools, as MSDE only installs "part of" them, that's to say the type
>lybs, dlls, COM dependencies and so on, but not the tools (Enterprise
>Manager, Profiler, Query Analyzer).. so you'll be stuck with no tool but
>oSql.exe...
>if you install SQL Server 2000, the newer tools (and realated dependencies)
>will be able to manage SQL Server 7.0 ...
|||hi Larry,
"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
news:7phgo0dtkn0rppaa0ee4nhjrv329n47500@.4ax.com
> I have to apologize...I am running SQLServer 2000. Too much going on.
> What difference does that make?
>
:D
ok... this sometimes occur when restoring database across servers that do
not share the same service pack level too..
I'd suggest to upgrade both (or all) servers instances to service pack 3..
please have a look at http://www.microsoft.com/sql/downloads/2000/sp3.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks very much. I'll be able to work on it Thursday and will let you
know the results.
On Wed, 3 Nov 2004 12:24:08 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

>hi Larry,
>"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
>news:7phgo0dtkn0rppaa0ee4nhjrv329n47500@.4ax.com
>:D
>ok... this sometimes occur when restoring database across servers that do
>not share the same service pack level too..
>I'd suggest to upgrade both (or all) servers instances to service pack 3..
>please have a look at http://www.microsoft.com/sql/downloads/2000/sp3.asp
|||Once I got the same versions installed the restore worked perfectly.
Thanks for the assistance.

restoring sql database without having SQL SERVER Enterprise editio

I have a backup of a database and trn files I want to restore them back
without having SQL SERVER just MSDE. Can I do it somehow? Please help me
someone I'm not so good with SQL SERVER's
VB.NET developer
"Csuszo Otto" schrieb:

> I have a backup of a database and trn files I want to restore them back
> without having SQL SERVER just MSDE. Can I do it somehow? Please help me
> someone I'm not so good with SQL SERVER's
> --
> VB.NET developer
Hello,
yes, you can restore the database by using for example the osql.exe- Tool.
This command-line-based tool was installed in the \BINN\- Directory of your
installation. You only have to look by unsing "osql /?" what the correct
parameters are.
Hope that was helpfully,
Jan

Restoring MSDE databases to SQL Server 2000

Hi,
We have a MSDE 2000 installed on a blackberry server that contains
Blackberry configuration data. For testing the Net backups we need to restor
e
it to a SQL Server 2000 server. The SQL Server 2000 is already installed and
has couple of test databases that I will drop before doing the restore for
the system and the user databases from the Net backups of the MSDE 2000
databases.
I am planning on restoring master first, then msdb, then model and then the
user databases. For restoring master I'll just restore the master from the
Net backup so that it replaces the current master and then I will just repea
t
the process with other system databases. Is this the right way to proceed or
am I missing something? Any advise will be greatly appreciated. Thanks.If you don't have the same path, then you will get into some problems. Also
make sure you have the
same build number of the SQL Server engines. And, finally, read KB 224071
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5DDC3553-2145-4212-9EBE-69EAF201633A@.microsoft.com...
> Hi,
> We have a MSDE 2000 installed on a blackberry server that contains
> blackberry configuration data. For testing the Net backups we need to rest
ore
> it to a SQL Server 2000 server. The SQL Server 2000 is already installed a
nd
> has couple of test databases that I will drop before doing the restore for
> the system and the user databases from the Net backups of the MSDE 2000
> databases.
> I am planning on restoring master first, then msdb, then model and then th
e
> user databases. For restoring master I'll just restore the master from the
> Net backup so that it replaces the current master and then I will just rep
eat
> the process with other system databases. Is this the right way to proceed
or
> am I missing something? Any advise will be greatly appreciated. Thanks.|||Thanks a lot. I did check the paths and found that the paths were different.
So I created the same path on the target server. I found this on the SQL
Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
are referring to as the build numbers?
The article that you have mentioned KB 224071 diccusses moving databases by
detaching and attaching, while I want to restore using the files from the
VERITAS Net Backup to test it. Any insight would be appreciated.
"Tibor Karaszi" wrote:

> If you don't have the same path, then you will get into some problems. Als
o make sure you have the
> same build number of the SQL Server engines. And, finally, read KB 224071
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5DDC3553-2145-4212-9EBE-69EAF201633A@.microsoft.com...
>
>|||> I found this on the SQL
> Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
> are referring to as the build numbers?
Yes.

> while I want to restore using the files from the
> VERITAS Net Backup to test it. Any insight would be appreciated.
Which, I believe, lead you in to unsupported territory. Anyhow, check out
http://vyaskn.tripod.com/moving_sql_server.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:20CAF583-3917-4396-89E3-ED54C7BD3087@.microsoft.com...[vbcol=seagreen]
> Thanks a lot. I did check the paths and found that the paths were differen
t.
> So I created the same path on the target server. I found this on the SQL
> Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
> are referring to as the build numbers?
> The article that you have mentioned KB 224071 diccusses moving databases b
y
> detaching and attaching, while I want to restore using the files from the
> VERITAS Net Backup to test it. Any insight would be appreciated.
> "Tibor Karaszi" wrote:
>

Restoring MSDE databases to SQL Server 2000

Hi,
We have a MSDE 2000 installed on a Blackberry server that contains
Blackberry configuration data. For testing the Net backups we need to restore
it to a SQL Server 2000 server. The SQL Server 2000 is already installed and
has couple of test databases that I will drop before doing the restore for
the system and the user databases from the Net backups of the MSDE 2000
databases.
I am planning on restoring master first, then msdb, then model and then the
user databases. For restoring master I'll just restore the master from the
Net backup so that it replaces the current master and then I will just repeat
the process with other system databases. Is this the right way to proceed or
am I missing something? Any advise will be greatly appreciated. Thanks.
If you don't have the same path, then you will get into some problems. Also make sure you have the
same build number of the SQL Server engines. And, finally, read KB 224071
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5DDC3553-2145-4212-9EBE-69EAF201633A@.microsoft.com...
> Hi,
> We have a MSDE 2000 installed on a Blackberry server that contains
> Blackberry configuration data. For testing the Net backups we need to restore
> it to a SQL Server 2000 server. The SQL Server 2000 is already installed and
> has couple of test databases that I will drop before doing the restore for
> the system and the user databases from the Net backups of the MSDE 2000
> databases.
> I am planning on restoring master first, then msdb, then model and then the
> user databases. For restoring master I'll just restore the master from the
> Net backup so that it replaces the current master and then I will just repeat
> the process with other system databases. Is this the right way to proceed or
> am I missing something? Any advise will be greatly appreciated. Thanks.
|||Thanks a lot. I did check the paths and found that the paths were different.
So I created the same path on the target server. I found this on the SQL
Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
are referring to as the build numbers?
The article that you have mentioned KB 224071 diccusses moving databases by
detaching and attaching, while I want to restore using the files from the
VERITAS Net Backup to test it. Any insight would be appreciated.
"Tibor Karaszi" wrote:

> If you don't have the same path, then you will get into some problems. Also make sure you have the
> same build number of the SQL Server engines. And, finally, read KB 224071
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5DDC3553-2145-4212-9EBE-69EAF201633A@.microsoft.com...
>
>
|||> I found this on the SQL
> Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
> are referring to as the build numbers?
Yes.

> while I want to restore using the files from the
> VERITAS Net Backup to test it. Any insight would be appreciated.
Which, I believe, lead you in to unsupported territory. Anyhow, check out
http://vyaskn.tripod.com/moving_sql_server.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:20CAF583-3917-4396-89E3-ED54C7BD3087@.microsoft.com...[vbcol=seagreen]
> Thanks a lot. I did check the paths and found that the paths were different.
> So I created the same path on the target server. I found this on the SQL
> Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
> are referring to as the build numbers?
> The article that you have mentioned KB 224071 diccusses moving databases by
> detaching and attaching, while I want to restore using the files from the
> VERITAS Net Backup to test it. Any insight would be appreciated.
> "Tibor Karaszi" wrote:

Restoring MSDE databases to SQL Server 2000

Hi,
We have a MSDE 2000 installed on a Blackberry server that contains
Blackberry configuration data. For testing the Net backups we need to restore
it to a SQL Server 2000 server. The SQL Server 2000 is already installed and
has couple of test databases that I will drop before doing the restore for
the system and the user databases from the Net backups of the MSDE 2000
databases.
I am planning on restoring master first, then msdb, then model and then the
user databases. For restoring master I'll just restore the master from the
Net backup so that it replaces the current master and then I will just repeat
the process with other system databases. Is this the right way to proceed or
am I missing something? Any advise will be greatly appreciated. Thanks.If you don't have the same path, then you will get into some problems. Also make sure you have the
same build number of the SQL Server engines. And, finally, read KB 224071
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5DDC3553-2145-4212-9EBE-69EAF201633A@.microsoft.com...
> Hi,
> We have a MSDE 2000 installed on a Blackberry server that contains
> Blackberry configuration data. For testing the Net backups we need to restore
> it to a SQL Server 2000 server. The SQL Server 2000 is already installed and
> has couple of test databases that I will drop before doing the restore for
> the system and the user databases from the Net backups of the MSDE 2000
> databases.
> I am planning on restoring master first, then msdb, then model and then the
> user databases. For restoring master I'll just restore the master from the
> Net backup so that it replaces the current master and then I will just repeat
> the process with other system databases. Is this the right way to proceed or
> am I missing something? Any advise will be greatly appreciated. Thanks.|||Thanks a lot. I did check the paths and found that the paths were different.
So I created the same path on the target server. I found this on the SQL
Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
are referring to as the build numbers?
The article that you have mentioned KB 224071 diccusses moving databases by
detaching and attaching, while I want to restore using the files from the
VERITAS Net Backup to test it. Any insight would be appreciated.
"Tibor Karaszi" wrote:
> If you don't have the same path, then you will get into some problems. Also make sure you have the
> same build number of the SQL Server engines. And, finally, read KB 224071
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5DDC3553-2145-4212-9EBE-69EAF201633A@.microsoft.com...
> > Hi,
> >
> > We have a MSDE 2000 installed on a Blackberry server that contains
> > Blackberry configuration data. For testing the Net backups we need to restore
> > it to a SQL Server 2000 server. The SQL Server 2000 is already installed and
> > has couple of test databases that I will drop before doing the restore for
> > the system and the user databases from the Net backups of the MSDE 2000
> > databases.
> >
> > I am planning on restoring master first, then msdb, then model and then the
> > user databases. For restoring master I'll just restore the master from the
> > Net backup so that it replaces the current master and then I will just repeat
> > the process with other system databases. Is this the right way to proceed or
> > am I missing something? Any advise will be greatly appreciated. Thanks.
>
>|||> I found this on the SQL
> Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
> are referring to as the build numbers?
Yes.
> while I want to restore using the files from the
> VERITAS Net Backup to test it. Any insight would be appreciated.
Which, I believe, lead you in to unsupported territory. Anyhow, check out
http://vyaskn.tripod.com/moving_sql_server.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:20CAF583-3917-4396-89E3-ED54C7BD3087@.microsoft.com...
> Thanks a lot. I did check the paths and found that the paths were different.
> So I created the same path on the target server. I found this on the SQL
> Server properties 8.00.760 (SP3) on both the SQL Servers. Is this what you
> are referring to as the build numbers?
> The article that you have mentioned KB 224071 diccusses moving databases by
> detaching and attaching, while I want to restore using the files from the
> VERITAS Net Backup to test it. Any insight would be appreciated.
> "Tibor Karaszi" wrote:
>> If you don't have the same path, then you will get into some problems. Also make sure you have
>> the
>> same build number of the SQL Server engines. And, finally, read KB 224071
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:5DDC3553-2145-4212-9EBE-69EAF201633A@.microsoft.com...
>> > Hi,
>> >
>> > We have a MSDE 2000 installed on a Blackberry server that contains
>> > Blackberry configuration data. For testing the Net backups we need to restore
>> > it to a SQL Server 2000 server. The SQL Server 2000 is already installed and
>> > has couple of test databases that I will drop before doing the restore for
>> > the system and the user databases from the Net backups of the MSDE 2000
>> > databases.
>> >
>> > I am planning on restoring master first, then msdb, then model and then the
>> > user databases. For restoring master I'll just restore the master from the
>> > Net backup so that it replaces the current master and then I will just repeat
>> > the process with other system databases. Is this the right way to proceed or
>> > am I missing something? Any advise will be greatly appreciated. Thanks.
>>

Restoring MSDE Databases

I have uninstalled and re-installed an application that uses MSDE on one of
our workstations. Before uninstalling the application I copied the .mdf and
..ldf files to a new location. I now need to restore the databases that are
associated with this application using the .mdf and .ldf files I saved. I
also need to verify the initial configuration (data and log files
names/paths)
I initially tried to detach the database, copy the backup files to the
MSSQL\Data folder and then reattach the database. This worked except that
the database came back in a "read-only" mode. I then opened the properties
of the database and tried to uncheck the "Read-only" option in the
properties. When I tried to save the new settings I received a message that
stated "Device activation error".
Can anyone tell me how I can:
1) Verify the configuration of the database that was installed by the
application I installed.
2) Rebuild this database using the .mdf and .ldf files?
Thanks for any input.
Nancy
Hi Nancy,
Sounds like you're basically on the right track. Is there any chance the
files were copied in from a CD or something and actually are read-only?
If you attach read-only mdf & ldf files, the database comes up in a
read-only mode.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Nancy Kafer" <nkafer@.homesteaderslife.com> wrote in message
news:OBnN12BgEHA.3428@.TK2MSFTNGP11.phx.gbl...
> I have uninstalled and re-installed an application that uses MSDE on one
of
> our workstations. Before uninstalling the application I copied the .mdf
and
> .ldf files to a new location. I now need to restore the databases that are
> associated with this application using the .mdf and .ldf files I saved. I
> also need to verify the initial configuration (data and log files
> names/paths)
> I initially tried to detach the database, copy the backup files to the
> MSSQL\Data folder and then reattach the database. This worked except that
> the database came back in a "read-only" mode. I then opened the properties
> of the database and tried to uncheck the "Read-only" option in the
> properties. When I tried to save the new settings I received a message
that
> stated "Device activation error".
> Can anyone tell me how I can:
> 1) Verify the configuration of the database that was installed by the
> application I installed.
> 2) Rebuild this database using the .mdf and .ldf files?
> Thanks for any input.
> Nancy
>
|||Hi Greg,
Thanks for the solution. That worked perfectly!!!!
Nancy
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:OHp7LaFgEHA.384@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi Nancy,
> Sounds like you're basically on the right track. Is there any chance the
> files were copied in from a CD or something and actually are read-only?
> If you attach read-only mdf & ldf files, the database comes up in a
> read-only mode.
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
> "Nancy Kafer" <nkafer@.homesteaderslife.com> wrote in message
> news:OBnN12BgEHA.3428@.TK2MSFTNGP11.phx.gbl...
> of
> and
are[vbcol=seagreen]
I[vbcol=seagreen]
that[vbcol=seagreen]
properties
> that
>

restoring msde backup to SS2000

If I backup a msde 7 database, will I be able to restore it to Sql Server 2000?
Thanks,
Dan D.
did you hear =?Utf-8?B?RGFuIEQu?= <DanD@.discussions.microsoft.com> say
in news:0F86B7E5-1E58-42AC-880A-CFF38C42AB51@.microsoft.com:

> If I backup a msde 7 database, will I be able to restore it to Sql
> Server 2000?
> Thanks,
AFAIK you can
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs

restoring msde backup to SS2000

If I backup a msde 7 database, will I be able to restore it to Sql Server 2000?
Thanks,
--
Dan D.did you hear =?Utf-8?B?RGFuIEQu?= <DanD@.discussions.microsoft.com> say
in news:0F86B7E5-1E58-42AC-880A-CFF38C42AB51@.microsoft.com:
> If I backup a msde 7 database, will I be able to restore it to Sql
> Server 2000?
> Thanks,
AFAIK you can
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs

restoring msde backup to SS2000

If I backup a msde 7 database, will I be able to restore it to Sql Server 20
00?
Thanks,
--
Dan D.did you hear examnotes <DanD@.discussions.microsoft.com> say
in news:0F86B7E5-1E58-42AC-880A-CFF38C42AB51@.microsoft.com:

> If I backup a msde 7 database, will I be able to restore it to Sql
> Server 2000?
> Thanks,
AFAIK you can
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs

Restoring msdb

Hi,
My system went down and I had to reinstall the O/S and of
course SQL Server and MSDE. Anyhow after that saga, I
finally have SQL Server running again with another
Instance called <computername>\VSDOTNET
I have backups of all dbs from prior to the crash, but I
am having trouble restoring them, especially the msdb
because it contains my DTS packages which I need
desperately!
Problem is when I attempt to restore the msdb it fails
and tells me it can't do it b/c the backup was created
with a different version of the server.
I've tried to Force restore over existing database with
no luck. Any suggestions please?
Thank you.
JasonMake sure that the service pack level for your reinstalled system is the same as the one you had
when the prior install crashed. SQL Server cannot go between even service packs for system
databases.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason" <shogun1972@.hotmail.com> wrote in message news:051401c3b7e2$01590910$a501280a@.phx.gbl...
> Hi,
> My system went down and I had to reinstall the O/S and of
> course SQL Server and MSDE. Anyhow after that saga, I
> finally have SQL Server running again with another
> Instance called <computername>\VSDOTNET
> I have backups of all dbs from prior to the crash, but I
> am having trouble restoring them, especially the msdb
> because it contains my DTS packages which I need
> desperately!
> Problem is when I attempt to restore the msdb it fails
> and tells me it can't do it b/c the backup was created
> with a different version of the server.
> I've tried to Force restore over existing database with
> no luck. Any suggestions please?
> Thank you.
> Jason

Restoring database with different COLLATION

I make backup from SQL Server 2000 and distribute this backup to our
customers.
They install MSDE 2000, and I can't garantee what COLLATION they will use.
How I can modify the COLLATION of the database after restoring it?
Thanks in advance.
hi Jorge,
"Jorge Brizuela" <jorge_brizuela@.spymac.com> ha scritto nel messaggio
news:OckM6w$pEHA.2948@.TK2MSFTNGP11.phx.gbl
> I make backup from SQL Server 2000 and distribute this backup to our
> customers.
> They install MSDE 2000, and I can't garantee what COLLATION they will
> use.
> How I can modify the COLLATION of the database after restoring it?
> Thanks in advance.
as SQL Server 2000 supports different collations and sort orders for each
database, and even different collations at column level granularity, you
should not require altering that setting for your distributed database...
you can however use the
ALTER DATABASE db_name
COLLATE new_collation...
ALTER TABLE tbname
ALTER COLUMN colname type
COLLATE new_collation
so you have to alter the database setting, and then modify all varchar(n),
char(n) and text datatype columns accordingly...
that's to say
SET NOCOUNT ON
SELECT DATABASEPROPERTYEX( 'master' , 'Collation') AS [master an sys
databases collation]
GO
CREATE DATABASE TEST
GO
USE TEST
GO
CREATE TABLE dbo.TestTB (
ID INT NOT NULL ,
name VARCHAR(10) NOT NULL
)
INSERT INTO dbo.TestTB VALUES ( 1 , 'Andrea')
CREATE TABLE dbo.TestTB2 (
ID INT NOT NULL ,
name VARCHAR(10) NOT NULL
)
GO
SELECT DATABASEPROPERTYEX( 'TEST' , 'Collation') AS [TEST database collation
original]
GO
ALTER DATABASE TEST
COLLATE Latin1_General_CS_AI_KS_WS
GO
SELECT DATABASEPROPERTYEX( 'TEST' , 'Collation') AS [TEST database collation
after changing]
GO
PRINT 'alter each varchar(n), char(n) to the defined collation by HAND'
ALTER TABLE dbo.TestTB2
ALTER COLUMN [name] VARCHAR(10)
COLLATE Latin1_General_CS_AI_KS_WS
ALTER TABLE dbo.TestTB
ALTER COLUMN [name] VARCHAR(10)
COLLATE Latin1_General_CS_AI_KS_WS
GO
PRINT ''
PRINT '--'
PRINT 'alter each varchar(n), char(n) to the defined collation using a
cursor'
PRINT 'an ALTER TABLE ALTER COLUMN script will be written (not executed)'
DECLARE @.sql VARCHAR(2000)
DECLARE @.table_schema VARCHAR(255),
@.table_name VARCHAR(255),
@.column_name VARCHAR(255),
@.is_nullable VARCHAR(255),
@.data_type VARCHAR(255),
@.character_maximum_length VARCHAR(255)
DECLARE myCur CURSOR FOR
SELECT TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'TEST'
AND DATA_TYPE IN ('varchar', 'char', 'nvarchar', 'nchar')
AND TABLE_NAME NOT LIKE 'sys%'
OPEN myCur
FETCH NEXT FROM myCur INTO @.table_schema,
@.table_name,
@.column_name,
@.is_nullable,
@.data_type,
@.character_maximum_length
WHILE @.@.FETCH_STATUS <> -1
BEGIN
SET @.sql = 'ALTER TABLE ' + @.table_schema + '.' + @.table_name
+ ' ALTER COLUMN ' + @.column_name + ' '
+ @.data_type + '(' + @.character_maximum_length + ') '
+ 'COLLATE Latin1_General_CS_AI_KS_WS '
+ CASE @.is_nullable WHEN 'No' THEN 'NOT NULL' ELSE 'NULL' END
PRINT @.sql
FETCH NEXT FROM myCur INTO @.table_schema,
@.table_name,
@.column_name,
@.is_nullable,
@.data_type,
@.character_maximum_length
END
CLOSE myCur
DEALLOCATE myCur
GO
PRINT 'drop all'
USE master
GO
DROP DATABASE TEST
as you can see... you can do it by hand, changing each column setting or
doing something handy like letting SQL Server writing the actual
Transact-SQL ALTER TABLE ALTER COLUMN ... statements you have to execute to
perform the desired result...
the actual statements will be
ALTER TABLE dbo.TestTB ALTER COLUMN name varchar(10) COLLATE
Latin1_General_CS_AI_KS_WS NULL
ALTER TABLE dbo.TestTB2 ALTER COLUMN name varchar(10) COLLATE
Latin1_General_CS_AI_KS_WS NULL
I do not love cursors, but this is one of the cases I could not find another
way to have the job done...
by the way, the problem you describe (it's no more a problem in SQL Server
2000) is one of the reasons I do not deploy my databases using restore
and/or sp_attach_db features... you are not "inheriting" all users database
settings like general sort order, model database specifi settings and
objects the end user placed in that template database...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi again, Jorge,
actually it can be done without a cursor, but yesterday night I was too
tired... =;-D
I appologize...
SET NOCOUNT ON
SELECT DATABASEPROPERTYEX( 'master' , 'Collation') AS [master and sys
databases collation]
GO
CREATE DATABASE TEST
GO
USE TEST
GO
CREATE TABLE dbo.TestTB (
ID INT NOT NULL ,
name VARCHAR(10) COLLATE Latin1_General_CS_AI_KS_WS NOT NULL
)
INSERT INTO dbo.TestTB VALUES ( 1 , 'Andrea')
CREATE TABLE dbo.TestTB2 (
ID INT NOT NULL ,
name VARCHAR(10) COLLATE Latin1_General_CS_AI_KS_WS NOT NULL
)
GO
SELECT DATABASEPROPERTYEX( 'TEST' , 'Collation') AS [TEST database collation
original]
DECLARE @.sysCollation VARCHAR(128)
SELECT @.sysCollation = CONVERT(VARCHAR(128), DATABASEPROPERTYEX( 'TEST' ,
'Collation') )
SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + CHAR(10) + CHAR(9)
+ ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + '(' +
CONVERT(VARCHAR(5) , CHARACTER_MAXIMUM_LENGTH ) + ') '
+ 'COLLATE ' + @.sysCollation + ' '
+ CASE IS_NULLABLE WHEN 'No' THEN 'NOT NULL' ELSE 'NULL' END +
CHAR(10) + 'GO'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'TEST'
AND DATA_TYPE IN ('varchar', 'char', 'nvarchar', 'nchar')
AND TABLE_NAME NOT LIKE 'sys%'
ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME
GO
PRINT 'cleanup'
USE master
GO
DROP DATABASE TEST
the resultin ALTER TABLE statements,
ALTER TABLE dbo.TestTB
ALTER COLUMN name varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
GO
ALTER TABLE dbo.TestTB2
ALTER COLUMN name varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
GO
in this case, can be then clipped and executed to set the desired collation
setting...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you very very much!!!
You help me a lot!!!!
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> escribi en el mensaje
news:2s7q7pF1h8u71U1@.uni-berlin.de...
> hi again, Jorge,
> actually it can be done without a cursor, but yesterday night I was too
> tired... =;-D
> I appologize...
> SET NOCOUNT ON
> SELECT DATABASEPROPERTYEX( 'master' , 'Collation') AS [master and sys
> databases collation]
> GO
> CREATE DATABASE TEST
> GO
> USE TEST
> GO
> CREATE TABLE dbo.TestTB (
> ID INT NOT NULL ,
> name VARCHAR(10) COLLATE Latin1_General_CS_AI_KS_WS NOT NULL
> )
> INSERT INTO dbo.TestTB VALUES ( 1 , 'Andrea')
> CREATE TABLE dbo.TestTB2 (
> ID INT NOT NULL ,
> name VARCHAR(10) COLLATE Latin1_General_CS_AI_KS_WS NOT NULL
> )
> GO
> SELECT DATABASEPROPERTYEX( 'TEST' , 'Collation') AS [TEST database
> collation
> original]
> DECLARE @.sysCollation VARCHAR(128)
> SELECT @.sysCollation = CONVERT(VARCHAR(128), DATABASEPROPERTYEX( 'TEST' ,
> 'Collation') )
> SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + CHAR(10) +
> CHAR(9)
> + ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + '(' +
> CONVERT(VARCHAR(5) , CHARACTER_MAXIMUM_LENGTH ) + ') '
> + 'COLLATE ' + @.sysCollation + ' '
> + CASE IS_NULLABLE WHEN 'No' THEN 'NOT NULL' ELSE 'NULL' END +
> CHAR(10) + 'GO'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_CATALOG = 'TEST'
> AND DATA_TYPE IN ('varchar', 'char', 'nvarchar', 'nchar')
> AND TABLE_NAME NOT LIKE 'sys%'
> ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME
> GO
> PRINT 'cleanup'
> USE master
> GO
> DROP DATABASE TEST
> the resultin ALTER TABLE statements,
> ALTER TABLE dbo.TestTB
> ALTER COLUMN name varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
> GO
> ALTER TABLE dbo.TestTB2
> ALTER COLUMN name varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
> GO
> in this case, can be then clipped and executed to set the desired
> collation
> setting...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Restoring Database using Scripts

I have and MSDE environment and I use "OSQL" to administer the database. I've written a backup script and restore script. My problem/question as to do with the restore. Both scripts run just fine.
However, I want my restore to use the lastest and greatest backup set within the backup device. I never really know what the lastest backup set is most of the time. But when ever my restore process runs I want it to use the lastest backup within the back
up device. How do I specify in my script to use the lastest backup set when a restore is done?
Thanks
Hi,
If you are going to restore in the same server see the MSDB..BACKUPSET table
for position. See the below query.
select database_name,backup_finish_date,position from msdb..backupset where
database_name='msdb'
If you are doing it in a different machne use the below command.
restore headeronly from disk='c:\msdb.bak'
You can restore the latest backup based on position
Thanks
Hari
MCDBA
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:C6B6DEC9-1CA4-4624-A6F9-2A7004037BC1@.microsoft.com...
> I have and MSDE environment and I use "OSQL" to administer the database.
I've written a backup script and restore script. My problem/question as to
do with the restore. Both scripts run just fine.
> However, I want my restore to use the lastest and greatest backup set
within the backup device. I never really know what the lastest backup set
is most of the time. But when ever my restore process runs I want it to use
the lastest backup within the backup device. How do I specify in my script
to use the lastest backup set when a restore is done?
> Thanks
|||hi Larry,
"Larry Bird" <LarryBird@.discussions.microsoft.com> ha scritto nel messaggio
news:C6B6DEC9-1CA4-4624-A6F9-2A7004037BC1@.microsoft.com...
> I have and MSDE environment and I use "OSQL" to administer the database.
>I've written a backup script and restore script. My problem/question as to
do with
>the restore. Both scripts run just fine.
> However, I want my restore to use the lastest and greatest backup set
within the
> backup device. I never really know what the lastest backup set is most of
the time.
>But when ever my restore process runs I want it to use the lastest backup
within the
>backup device. How do I specify in my script to use the lastest backup set
when a
>restore is done?
Im'm late, and Hari already pointed out what needed, but, anyway =;-D
I woul'd not automatically scritp this... restore shoul'd be better a manual
operation as it's not a dayly house keeping activity...
anyway... you can start from getting the max file position...
SET NOCOUNT ON
DECLARE @.db VARCHAR(128)
DECLARE @.file VARCHAR(128)
DECLARE @.cmd VARCHAR(1024)
SELECT @.file = 'D:\ADOvb6\VBH_FILE\vbhotel.bak' , @.db = 'vbhotel'
SET @.cmd = 'RESTORE HEADERONLY FROM DISK = '''+ @.file + ''''
CREATE TABLE #restore (
BackupName VARCHAR(128) ,
BackupDescription VARCHAR(128) ,
BackupType INT ,
ExpirationDate DATETIME ,
Compressed INT ,
Position INT ,
DeviceType INT ,
UserName VARCHAR(128) ,
ServerName VARCHAR(128) ,
DatabaseName VARCHAR(128) ,
DatabaseVersion INT ,
DatabaseCreationDate DATETIME ,
BackupSize INT ,
FirstLsn VARCHAR(128) ,
LastLsn VARCHAR(128) ,
CheckPointLsn VARCHAR(128) ,
DifferentialBaseLsn VARCHAR(128) ,
BackupStartDate DATETIME ,
BackupFinishDate DATETIME ,
SortOrder SMALLINT ,
CodePage SMALLINT ,
UnicodeLocaleId INT ,
UnicodeComparisonStyle INT ,
CompatibilityLevel TINYINT ,
SoftwareVendorId INT ,
SoftwareVersionMajor INT ,
SoftwareVersionMinor INT ,
SoftwareVersionBuild INT ,
MachineName NVARCHAR(128) ,
Flags INT ,
BindingId uniqueidentifier ,
RecoveryForkId uniqueidentifier ,
Collation NVARCHAR(128)
)
INSERT INTO #restore EXEC(@.cmd)
SELECT MAX(r.Position) AS [MaxFile]
FROM #restore r
WHERE r.BackupType = 1
AND r.DatabaseName = @.db
AND r.MachineName = @.@.SERVERNAME
GO
DROP TABLE #restore
--<--
MaxFile
1
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

Restoring database problem

Hello All,
I tried to restore a msde database file in c#. Since SQL Server
requires that no users be connected to the database during the restore
operation, I tried to shut down the sql server, then start the sql
server again. Unfortunately, it did not work. The following is the
code:
//create an instance of a server class
SQLDMO.SQLServer srv = new SQLDMO.SQLServer();
//connect to the server
srv.Connect("local)\\NetSDK", "sa", "abc");
try
{
srv.Shutdown(5000); // stop db server, wait for 2 seconds
}
catch (Exception e)
{
; // do nothing
}
Thread.Sleep(10000); // sleep 10 seconds
// restart server
srv.Start(true, "(local)\\NetSDK", "sa", "abc");
//create a restore class instance
SQLDMO.Restore restore = new SQLDMO.Restore();
restore.Action = 0; // full db restore
//set the database to the chosen database
restore.Database = "mydbfile";
restore.Files = Path.GetFullPath(@."..\..\backup\DBLPS.bak");
SqlConnection conn = new SqlConnection("server=(local)\\NetSDK;
uid=sa; password=abc; pooling=false");
// Restore the database
restore.ReplaceDatabase = true;
restore.SQLRestore(srv);
An exception is thrown at srv.Start with message:
"An unhandled exception of type
'System.Runtime.InteropServices.COMException' occurred in myapp.exe
Additional information: [SQL-DMO]This server object is already
connected."
Mandy
Mandy wrote:

> Hello All,
> I tried to restore a msde database file in c#. Since SQL Server
> requires that no users be connected to the database during the restore
> operation, I tried to shut down the sql server, then start the sql
> server again. Unfortunately, it did not work. The following is the
> code:
> //create an instance of a server class
> SQLDMO.SQLServer srv = new SQLDMO.SQLServer();
> //connect to the server
> srv.Connect("local)\\NetSDK", "sa", "abc");
> try
> {
> srv.Shutdown(5000); // stop db server, wait for 2 seconds
> }
> catch (Exception e)
> {
> ; // do nothing
> }
> Thread.Sleep(10000); // sleep 10 seconds
> // restart server
> srv.Start(true, "(local)\\NetSDK", "sa", "abc");
> //create a restore class instance
> SQLDMO.Restore restore = new SQLDMO.Restore();
> restore.Action = 0; // full db restore
> //set the database to the chosen database
> restore.Database = "mydbfile";
> restore.Files = Path.GetFullPath(@."..\..\backup\DBLPS.bak");
> SqlConnection conn = new SqlConnection("server=(local)\\NetSDK;
> uid=sa; password=abc; pooling=false");
> // Restore the database
> restore.ReplaceDatabase = true;
> restore.SQLRestore(srv);
>
> An exception is thrown at srv.Start with message:
> "An unhandled exception of type
> 'System.Runtime.InteropServices.COMException' occurred in myapp.exe
> Additional information: [SQL-DMO]This server object is already
> connected."
And if you use the T-SQL commands instead of SQLDMO? (RESTORE DATABASE) You
could even shutdown the mssqlserver process through executing "net stop
mssqlserver" as a process and then restart it again with "net start
mssqlserver" as a process.
FB
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP
|||Hi,
Thanks for replying! How to execute T-SQL commands to stop and start
database in C#?
Mandy
"Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@.xs4all.nl> wrote in message news:<xn0dnlsxj3398s001@.msnews.microsoft.com>...
> Mandy wrote:
>
> And if you use the T-SQL commands instead of SQLDMO? (RESTORE DATABASE) You
> could even shutdown the mssqlserver process through executing "net stop
> mssqlserver" as a process and then restart it again with "net start
> mssqlserver" as a process.
> FB
|||Mandy wrote:
> Thanks for replying! How to execute T-SQL commands to stop and start
> database in C#?
stop and start the server process. You can do that through:
// stopping
System.Diagnostics.Process.Start("CMD.exe", "/C net stop mssqlserver");
// starting
System.Diagnostics.Process.Start("CMD.exe", "/C net start mssqlserver");
Of course you have to wait some seconds between the two.
Frans.[vbcol=seagreen]
> Mandy
>
> "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@.xs4all.nl> wrote in message
> news:<xn0dnlsxj3398s001@.msnews.microsoft.com>...
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP

Restoring database problem

Hello All,
I tried to restore a msde database file in c#. Since SQL Server
requires that no users be connected to the database during the restore
operation, I tried to shut down the sql server, then start the sql
server again. Unfortunately, it did not work. The following is the
code:
//create an instance of a server class
SQLDMO.SQLServer srv = new SQLDMO.SQLServer();
//connect to the server
srv.Connect("local)\\NetSDK", "sa", "abc");
try
{
srv.Shutdown(5000); // stop db server, wait for 2 seconds
}
catch (Exception e)
{
; // do nothing
}
Thread.Sleep(10000); // sleep 10 seconds
// restart server
srv.Start(true, "(local)\\NetSDK", "sa", "abc");
//create a restore class instance
SQLDMO.Restore restore = new SQLDMO.Restore();
restore.Action = 0; // full db restore
//set the database to the chosen database
restore.Database = "mydbfile";
restore.Files = Path.GetFullPath(@."..\..\backup\DBLPS.bak");
SqlConnection conn = new SqlConnection("server=(local)\\NetSDK;
uid=sa; password=abc; pooling=false");
// Restore the database
restore.ReplaceDatabase = true;
restore.SQLRestore(srv);
An exception is thrown at srv.Start with message:
"An unhandled exception of type
'System.Runtime.InteropServices.COMException' occurred in myapp.exe
Additional information: [SQL-DMO]This server object is already
connected."
MandyMandy wrote:
> Hello All,
> I tried to restore a msde database file in c#. Since SQL Server
> requires that no users be connected to the database during the restore
> operation, I tried to shut down the sql server, then start the sql
> server again. Unfortunately, it did not work. The following is the
> code:
> //create an instance of a server class
> SQLDMO.SQLServer srv = new SQLDMO.SQLServer();
> //connect to the server
> srv.Connect("local)\\NetSDK", "sa", "abc");
> try
> {
> srv.Shutdown(5000); // stop db server, wait for 2 seconds
> }
> catch (Exception e)
> {
> ; // do nothing
> }
> Thread.Sleep(10000); // sleep 10 seconds
> // restart server
> srv.Start(true, "(local)\\NetSDK", "sa", "abc");
> //create a restore class instance
> SQLDMO.Restore restore = new SQLDMO.Restore();
> restore.Action = 0; // full db restore
> //set the database to the chosen database
> restore.Database = "mydbfile";
> restore.Files = Path.GetFullPath(@."..\..\backup\DBLPS.bak");
> SqlConnection conn = new SqlConnection("server=(local)\\NetSDK;
> uid=sa; password=abc; pooling=false");
> // Restore the database
> restore.ReplaceDatabase = true;
> restore.SQLRestore(srv);
>
> An exception is thrown at srv.Start with message:
> "An unhandled exception of type
> 'System.Runtime.InteropServices.COMException' occurred in myapp.exe
> Additional information: [SQL-DMO]This server object is already
> connected."
And if you use the T-SQL commands instead of SQLDMO? (RESTORE DATABASE) You
could even shutdown the mssqlserver process through executing "net stop
mssqlserver" as a process and then restart it again with "net start
mssqlserver" as a process.
FB
--
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP|||Hi,
Thanks for replying! How to execute T-SQL commands to stop and start
database in C#?
Mandy
"Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@.xs4all.nl> wrote in message news:<xn0dnlsxj3398s001@.msnews.microsoft.com>...
> Mandy wrote:
> > Hello All,
> >
> > I tried to restore a msde database file in c#. Since SQL Server
> > requires that no users be connected to the database during the restore
> > operation, I tried to shut down the sql server, then start the sql
> > server again. Unfortunately, it did not work. The following is the
> > code:
> >
> > //create an instance of a server class
> > SQLDMO.SQLServer srv = new SQLDMO.SQLServer();
> > //connect to the server
> > srv.Connect("local)\\NetSDK", "sa", "abc");
> > try
> > {
> > srv.Shutdown(5000); // stop db server, wait for 2 seconds
> > }
> > catch (Exception e)
> > {
> > ; // do nothing
> > }
> >
> > Thread.Sleep(10000); // sleep 10 seconds
> > // restart server
> > srv.Start(true, "(local)\\NetSDK", "sa", "abc");
> >
> > //create a restore class instance
> > SQLDMO.Restore restore = new SQLDMO.Restore();
> > restore.Action = 0; // full db restore
> > //set the database to the chosen database
> > restore.Database = "mydbfile";
> > restore.Files = Path.GetFullPath(@."..\..\backup\DBLPS.bak");
> >
> > SqlConnection conn = new SqlConnection("server=(local)\\NetSDK;
> > uid=sa; password=abc; pooling=false");
> >
> > // Restore the database
> > restore.ReplaceDatabase = true;
> > restore.SQLRestore(srv);
> >
> >
> >
> > An exception is thrown at srv.Start with message:
> >
> > "An unhandled exception of type
> > 'System.Runtime.InteropServices.COMException' occurred in myapp.exe
> >
> > Additional information: [SQL-DMO]This server object is already
> > connected."
> And if you use the T-SQL commands instead of SQLDMO? (RESTORE DATABASE) You
> could even shutdown the mssqlserver process through executing "net stop
> mssqlserver" as a process and then restart it again with "net start
> mssqlserver" as a process.
> FB|||Mandy wrote:
> Thanks for replying! How to execute T-SQL commands to stop and start
> database in C#?
stop and start the server process. You can do that through:
// stopping
System.Diagnostics.Process.Start("CMD.exe", "/C net stop mssqlserver");
// starting
System.Diagnostics.Process.Start("CMD.exe", "/C net start mssqlserver");
Of course you have to wait some seconds between the two.
Frans.
> Mandy
>
> "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@.xs4all.nl> wrote in message
> news:<xn0dnlsxj3398s001@.msnews.microsoft.com>...
> > Mandy wrote:
> >
> > > Hello All,
> > >
> > > I tried to restore a msde database file in c#. Since SQL Server
> > > requires that no users be connected to the database during the restore
> > > operation, I tried to shut down the sql server, then start the sql
> > > server again. Unfortunately, it did not work. The following is the
> > > code:
> > >
> > > //create an instance of a server class
> > > SQLDMO.SQLServer srv = new SQLDMO.SQLServer();
> > > //connect to the server
> > > srv.Connect("local)\\NetSDK", "sa", "abc");
> > > try
> > > {
> > > srv.Shutdown(5000); // stop db server, wait for 2 seconds
> > > }
> > > catch (Exception e)
> > > {
> > > ; // do nothing
> > > }
> > >
> > > Thread.Sleep(10000); // sleep 10 seconds
> > > // restart server
> > > srv.Start(true, "(local)\\NetSDK", "sa", "abc");
> > >
> > > //create a restore class instance
> > > SQLDMO.Restore restore = new SQLDMO.Restore();
> > > restore.Action = 0; // full db restore
> > > //set the database to the chosen database
> > > restore.Database = "mydbfile";
> > > restore.Files = Path.GetFullPath(@."..\..\backup\DBLPS.bak");
> > >
> > > SqlConnection conn = new SqlConnection("server=(local)\\NetSDK;
> > > uid=sa; password=abc; pooling=false");
> > >
> > > // Restore the database
> > > restore.ReplaceDatabase = true;
> > > restore.SQLRestore(srv);
> > >
> > >
> > >
> > > An exception is thrown at srv.Start with message:
> > >
> > > "An unhandled exception of type
> > > 'System.Runtime.InteropServices.COMException' occurred in myapp.exe
> > >
> > > Additional information: [SQL-DMO]This server object is already
> > > connected."
> >
> > And if you use the T-SQL commands instead of SQLDMO? (RESTORE DATABASE)
> > You could even shutdown the mssqlserver process through executing "net
> > stop mssqlserver" as a process and then restart it again with "net start
> > mssqlserver" as a process.
> >
> > FB
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP