Restrict IP connections

I need to modify SQL Server 2000 Enterprise Edition so that it only
listens on the two local IP addresses, not the IP address of the
Internet.
I found KB article Q244980, but the "Maintain a Virtual Server for
Failover Clustering" can be selected.
Is there a registry setting or something where I can set which IP's SQL
server listens on?
--DaveHi Dave,
As far as I know that's not possible. Your best option is probably to close
ports 1433 and 1434 on your firewall. These are the default ports SQL Server
listens on. If you changed the ports, you of course need to close these. If
you're running on Windows 2003, you can restrict IP's on the network level.
Karl Gram, BSc, MBA
http://www.gramonline.com
"Dave Navarro" <dave@.dave.dave> wrote in message
news:MPG.1acfa74cf9ba4b9a9897d5@.news-40.giganews.com...
> I need to modify SQL Server 2000 Enterprise Edition so that it only
> listens on the two local IP addresses, not the IP address of the
> Internet.
> I found KB article Q244980, but the "Maintain a Virtual Server for
> Failover Clustering" can be selected.
> Is there a registry setting or something where I can set which IP's SQL
> server listens on?
> --Dave

restoring system db's on new instance

Hi There

We have a database server that must be completely re-setup, the hard drives will be replaced and the OS and Sql Server 2000 EE edition re-installed from scratch, a fresh new instance.

However the client has requested that we restore the master and msdb databases of the old instance over the new one once it is installed.

Now is this ok to do ? Can i simply restore a backup of master and msdb ont he new instance and what is the correct procedure to do this ?

I am not to worried about the user DB's i will simply dettach and re-attach. But must this be done before or after restoring the old master if i can do that ?

Thanx

To restore Master db you should build your box with exactly same OS and SQL configuration as from which you have taken the backup. ie. OS and sql server has to be same version /edition/sp /patches applied

Refer this : http://support.microsoft.com/kb/264474

Madhu

|||

These resources may help:

http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving system dbs 2005
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users

Restoring Master from Enterprise Edition to Standard edition

Does anyone know if there are any issues with restoring Master DB onto
a server running Standard Edition from a server running Enterprise
Edition of SQL Server?Hi

The structure of master database is not different, if you are changing
hosts, domains, collations or SQL Server versions (e.g from not service
packed to service packed) then you may have some issues.

If you check out
http://support.microsoft.com/defaul...b;en-us;Q314546 you should
be able to move databases without needing to move the master database.

John

"Tina" <tsorvari@.libertysite.com> wrote in message
news:49c3c8d5.0406081249.1df31c38@.posting.google.c om...
> Does anyone know if there are any issues with restoring Master DB onto
> a server running Standard Edition from a server running Enterprise
> Edition of SQL Server?

Restoring from 7.0 to 2000 Enterprise Edition...

Hi,
I have read restore on BOL , but i am unable to restore a SQL 7.0 back
up to SQL Server 2000. I have tried things given in the following
article
http://msdn.microsoft.com/library/d...>
kpt_15ro.asp
and i have also tried to restore the back up by using the following
procedure.
My database name is "Hello". I have first created a new database named
"Hello" by using Enterprise Manager and then i opened up Query Analyzer
and choose the database named 'Hello' from dropdown and wrote down the
following
Restore DATABASE HELLO
>From disk = 'd:\Documents and
Settings\Administrator\DEsktop\backup\He
llo'
WITH Move 'Hello' to 'D:\test\Hello.mdf'
Go
But i get some errors as in "Exclusive access could not be obtained
because the datavase is in use' and 'Restore Database is terminating
abnormally.
Note: My SQL 7.0 back up stored in the location 'd:\Documents and
Settings\Administrator\DEsktop\backup\He
llo'. I am using SQL Server
2000Enterprise Edition on Windows 2000 Advanced Server.
Please do reply me with what i ammissing. I have read quite a few
things on BOL and on MSDN. However, i am stumped. I would really
appreciate any help as im a newbie so please point me to the solution
or to any article / online-resource that would lead me to the solution.
Take care and looking forward.
Please do reply me what i am missing here.The problem is that you are selecting the Hello database in Query Analyzer
and then, when you run the restore command, the Hello database is already in
use.
Supposing that you want to replace the existing Hello database, select
another database in Query Analyzer, like master, and run the same command.
Ben Nevarez
"Erland" <Erland.Erikson@.gmail.com> wrote in message
news:1132539237.114914.201650@.o13g2000cwo.googlegroups.com...
> Hi,
> I have read restore on BOL , but i am unable to restore a SQL 7.0 back
> up to SQL Server 2000. I have tried things given in the following
> article
> http://msdn.microsoft.com/library/d...
ackpt_15ro.asp
> and i have also tried to restore the back up by using the following
> procedure.
> My database name is "Hello". I have first created a new database named
> "Hello" by using Enterprise Manager and then i opened up Query Analyzer
> and choose the database named 'Hello' from dropdown and wrote down the
> following
> Restore DATABASE HELLO
> Settings\Administrator\DEsktop\backup\He
llo'
> WITH Move 'Hello' to 'D:\test\Hello.mdf'
> Go
> But i get some errors as in "Exclusive access could not be obtained
> because the datavase is in use' and 'Restore Database is terminating
> abnormally.
> Note: My SQL 7.0 back up stored in the location 'd:\Documents and
> Settings\Administrator\DEsktop\backup\He
llo'. I am using SQL Server
> 2000Enterprise Edition on Windows 2000 Advanced Server.
> Please do reply me with what i ammissing. I have read quite a few
> things on BOL and on MSDN. However, i am stumped. I would really
> appreciate any help as im a newbie so please point me to the solution
> or to any article / online-resource that would lead me to the solution.
> Take care and looking forward.
> Please do reply me what i am missing here.
>|||Hi Erland,
The error indicates that you have the DB locked.
You can't restore when you have the Hello database opened as you have by
selecting the Hello database from teh dropdown menu.
Change databases to any other database and try again. If you still get the
error close down all the EM and QA sessions and try it again.
You can use the sp_who command to see how is in what database
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"Erland" <Erland.Erikson@.gmail.com> wrote in message
news:1132539237.114914.201650@.o13g2000cwo.googlegroups.com...
> Hi,
> I have read restore on BOL , but i am unable to restore a SQL 7.0 back
> up to SQL Server 2000. I have tried things given in the following
> article
> http://msdn.microsoft.com/library/d...
ackpt_15ro.asp
> and i have also tried to restore the back up by using the following
> procedure.
> My database name is "Hello". I have first created a new database named
> "Hello" by using Enterprise Manager and then i opened up Query Analyzer
> and choose the database named 'Hello' from dropdown and wrote down the
> following
> Restore DATABASE HELLO
> Settings\Administrator\DEsktop\backup\He
llo'
> WITH Move 'Hello' to 'D:\test\Hello.mdf'
> Go
> But i get some errors as in "Exclusive access could not be obtained
> because the datavase is in use' and 'Restore Database is terminating
> abnormally.
> Note: My SQL 7.0 back up stored in the location 'd:\Documents and
> Settings\Administrator\DEsktop\backup\He
llo'. I am using SQL Server
> 2000Enterprise Edition on Windows 2000 Advanced Server.
> Please do reply me with what i ammissing. I have read quite a few
> things on BOL and on MSDN. However, i am stumped. I would really
> appreciate any help as im a newbie so please point me to the solution
> or to any article / online-resource that would lead me to the solution.
> Take care and looking forward.
> Please do reply me what i am missing here.
>|||Hi ,
Many thanks for your message. I tried the following by choosing
someother database from dropdown in QueryAnalyzer.
Restre Database Hello
from disk='d:\Documents and
Settings\Administrator\Desktop\backup\MS
SQL7\BACKUP\Hello'
iwth move 'hello' to ''d:\Documents and
Settings\Administrator\Desktop\backup\MS
SQL7\BACKUP\Hello.mdf'
Go
but i get the following errors
i)-Logical file 'hello' is not part of database 'hello'. Use Restore
filelistonly to list the logical file names
ii)-Restore database is terminating abnormally.
Then i tried the following
Restore filelistonly
from disk='d:\Documents and
Settings\Administrator\Desktop\backup\MS
SQL7\BACKUP\Hello'
Restore Database Hello
from disk='d:\Documents and
Settings\Administrator\Desktop\backup\MS
SQL7\BACKUP\Hello'
with move 'hello' to ''d:\Documents and
Settings\Administrator\Desktop\backup\MS
SQL7\BACKUP\Hello.mdf'
Go
but i get the same two errors . Just to remind you i am trying to
restore a SQL 7.0 back up on SQL Server 2000 and my SQL 7.0 back is
named as Hello and store at following location
D:\Documents and
Settings\Administrator\Desktop\backup\MS
SQL7\BACKUP\Hello
Now i don't have any idea what i am missing here. Any help or comments
will be highly appreciated. Please help.
Thanks
-Erland.
GregO wrote:[vbcol=seagreen]
> Hi Erland,
> The error indicates that you have the DB locked.
> You can't restore when you have the Hello database opened as you have by
> selecting the Hello database from teh dropdown menu.
> Change databases to any other database and try again. If you still get th
e
> error close down all the EM and QA sessions and try it again.
> You can use the sp_who command to see how is in what database
> kind regards
> Greg O
> --
> Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
> AGS SQL 2005 Utilities, over 20+ functions
> http://www.ag-software.com/?tabid=38
>
> "Erland" <Erland.Erikson@.gmail.com> wrote in message
> news:1132539237.114914.201650@.o13g2000cwo.googlegroups.com...

Restoring from 7.0 to 2000 Enterprise Edition...

Hi,
I have read restore on BOL , but i am unable to restore a SQL 7.0 back
up to SQL Server 2000. I have tried things given in the following
article
http://msdn.microsoft.com/library/de...ackpt_15ro.asp
and i have also tried to restore the back up by using the following
procedure.
My database name is "Hello". I have first created a new database named
"Hello" by using Enterprise Manager and then i opened up Query Analyzer
and choose the database named 'Hello' from dropdown and wrote down the
following
Restore DATABASE HELLO
>From disk = 'd:\Documents and
Settings\Administrator\DEsktop\backup\Hello'
WITH Move 'Hello' to 'D:\test\Hello.mdf'
Go
But i get some errors as in "Exclusive access could not be obtained
because the datavase is in use' and 'Restore Database is terminating
abnormally.
Note: My SQL 7.0 back up stored in the location 'd:\Documents and
Settings\Administrator\DEsktop\backup\Hello'. I am using SQL Server
2000Enterprise Edition on Windows 2000 Advanced Server.
Please do reply me with what i ammissing. I have read quite a few
things on BOL and on MSDN. However, i am stumped. I would really
appreciate any help as im a newbie so please point me to the solution
or to any article / online-resource that would lead me to the solution.
Take care and looking forward.
Please do reply me what i am missing here.
The problem is that you are selecting the Hello database in Query Analyzer
and then, when you run the restore command, the Hello database is already in
use.
Supposing that you want to replace the existing Hello database, select
another database in Query Analyzer, like master, and run the same command.
Ben Nevarez
"Erland" <Erland.Erikson@.gmail.com> wrote in message
news:1132539237.114914.201650@.o13g2000cwo.googlegr oups.com...
> Hi,
> I have read restore on BOL , but i am unable to restore a SQL 7.0 back
> up to SQL Server 2000. I have tried things given in the following
> article
> http://msdn.microsoft.com/library/de...ackpt_15ro.asp
> and i have also tried to restore the back up by using the following
> procedure.
> My database name is "Hello". I have first created a new database named
> "Hello" by using Enterprise Manager and then i opened up Query Analyzer
> and choose the database named 'Hello' from dropdown and wrote down the
> following
> Restore DATABASE HELLO
> Settings\Administrator\DEsktop\backup\Hello'
> WITH Move 'Hello' to 'D:\test\Hello.mdf'
> Go
> But i get some errors as in "Exclusive access could not be obtained
> because the datavase is in use' and 'Restore Database is terminating
> abnormally.
> Note: My SQL 7.0 back up stored in the location 'd:\Documents and
> Settings\Administrator\DEsktop\backup\Hello'. I am using SQL Server
> 2000Enterprise Edition on Windows 2000 Advanced Server.
> Please do reply me with what i ammissing. I have read quite a few
> things on BOL and on MSDN. However, i am stumped. I would really
> appreciate any help as im a newbie so please point me to the solution
> or to any article / online-resource that would lead me to the solution.
> Take care and looking forward.
> Please do reply me what i am missing here.
>
|||Hi Erland,
The error indicates that you have the DB locked.
You can't restore when you have the Hello database opened as you have by
selecting the Hello database from teh dropdown menu.
Change databases to any other database and try again. If you still get the
error close down all the EM and QA sessions and try it again.
You can use the sp_who command to see how is in what database
kind regards
Greg O
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"Erland" <Erland.Erikson@.gmail.com> wrote in message
news:1132539237.114914.201650@.o13g2000cwo.googlegr oups.com...
> Hi,
> I have read restore on BOL , but i am unable to restore a SQL 7.0 back
> up to SQL Server 2000. I have tried things given in the following
> article
> http://msdn.microsoft.com/library/de...ackpt_15ro.asp
> and i have also tried to restore the back up by using the following
> procedure.
> My database name is "Hello". I have first created a new database named
> "Hello" by using Enterprise Manager and then i opened up Query Analyzer
> and choose the database named 'Hello' from dropdown and wrote down the
> following
> Restore DATABASE HELLO
> Settings\Administrator\DEsktop\backup\Hello'
> WITH Move 'Hello' to 'D:\test\Hello.mdf'
> Go
> But i get some errors as in "Exclusive access could not be obtained
> because the datavase is in use' and 'Restore Database is terminating
> abnormally.
> Note: My SQL 7.0 back up stored in the location 'd:\Documents and
> Settings\Administrator\DEsktop\backup\Hello'. I am using SQL Server
> 2000Enterprise Edition on Windows 2000 Advanced Server.
> Please do reply me with what i ammissing. I have read quite a few
> things on BOL and on MSDN. However, i am stumped. I would really
> appreciate any help as im a newbie so please point me to the solution
> or to any article / online-resource that would lead me to the solution.
> Take care and looking forward.
> Please do reply me what i am missing here.
>
|||Hi ,
Many thanks for your message. I tried the following by choosing
someother database from dropdown in QueryAnalyzer.
Restre Database Hello
from disk='d:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKU P\Hello'
iwth move 'hello' to ''d:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKU P\Hello.mdf'
Go
but i get the following errors
i)-Logical file 'hello' is not part of database 'hello'. Use Restore
filelistonly to list the logical file names
ii)-Restore database is terminating abnormally.
Then i tried the following
Restore filelistonly
from disk='d:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKU P\Hello'
Restore Database Hello
from disk='d:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKU P\Hello'
with move 'hello' to ''d:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKU P\Hello.mdf'
Go
but i get the same two errors . Just to remind you i am trying to
restore a SQL 7.0 back up on SQL Server 2000 and my SQL 7.0 back is
named as Hello and store at following location
D:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKU P\Hello
Now i don't have any idea what i am missing here. Any help or comments
will be highly appreciated. Please help.
Thanks
-Erland.
GregO wrote:[vbcol=seagreen]
> Hi Erland,
> The error indicates that you have the DB locked.
> You can't restore when you have the Hello database opened as you have by
> selecting the Hello database from teh dropdown menu.
> Change databases to any other database and try again. If you still get the
> error close down all the EM and QA sessions and try it again.
> You can use the sp_who command to see how is in what database
> kind regards
> Greg O
> --
> Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
> AGS SQL 2005 Utilities, over 20+ functions
> http://www.ag-software.com/?tabid=38
>
> "Erland" <Erland.Erikson@.gmail.com> wrote in message
> news:1132539237.114914.201650@.o13g2000cwo.googlegr oups.com...

Restoring from 7.0 to 2000 Enterprise Edition...

Hi,
I have read restore on BOL , but i am unable to restore a SQL 7.0 back
up to SQL Server 2000. I have tried things given in the following
article
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_15ro.asp
and i have also tried to restore the back up by using the following
procedure.
My database name is "Hello". I have first created a new database named
"Hello" by using Enterprise Manager and then i opened up Query Analyzer
and choose the database named 'Hello' from dropdown and wrote down the
following
Restore DATABASE HELLO
>From disk = 'd:\Documents and
Settings\Administrator\DEsktop\backup\Hello'
WITH Move 'Hello' to 'D:\test\Hello.mdf'
Go
But i get some errors as in "Exclusive access could not be obtained
because the datavase is in use' and 'Restore Database is terminating
abnormally.
Note: My SQL 7.0 back up stored in the location 'd:\Documents and
Settings\Administrator\DEsktop\backup\Hello'. I am using SQL Server
2000Enterprise Edition on Windows 2000 Advanced Server.
Please do reply me with what i ammissing. I have read quite a few
things on BOL and on MSDN. However, i am stumped. I would really
appreciate any help as im a newbie so please point me to the solution
or to any article / online-resource that would lead me to the solution.
Take care and looking forward.
Please do reply me what i am missing here.The problem is that you are selecting the Hello database in Query Analyzer
and then, when you run the restore command, the Hello database is already in
use.
Supposing that you want to replace the existing Hello database, select
another database in Query Analyzer, like master, and run the same command.
Ben Nevarez
"Erland" <Erland.Erikson@.gmail.com> wrote in message
news:1132539237.114914.201650@.o13g2000cwo.googlegroups.com...
> Hi,
> I have read restore on BOL , but i am unable to restore a SQL 7.0 back
> up to SQL Server 2000. I have tried things given in the following
> article
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_15ro.asp
> and i have also tried to restore the back up by using the following
> procedure.
> My database name is "Hello". I have first created a new database named
> "Hello" by using Enterprise Manager and then i opened up Query Analyzer
> and choose the database named 'Hello' from dropdown and wrote down the
> following
> Restore DATABASE HELLO
>>From disk = 'd:\Documents and
> Settings\Administrator\DEsktop\backup\Hello'
> WITH Move 'Hello' to 'D:\test\Hello.mdf'
> Go
> But i get some errors as in "Exclusive access could not be obtained
> because the datavase is in use' and 'Restore Database is terminating
> abnormally.
> Note: My SQL 7.0 back up stored in the location 'd:\Documents and
> Settings\Administrator\DEsktop\backup\Hello'. I am using SQL Server
> 2000Enterprise Edition on Windows 2000 Advanced Server.
> Please do reply me with what i ammissing. I have read quite a few
> things on BOL and on MSDN. However, i am stumped. I would really
> appreciate any help as im a newbie so please point me to the solution
> or to any article / online-resource that would lead me to the solution.
> Take care and looking forward.
> Please do reply me what i am missing here.
>|||Hi Erland,
The error indicates that you have the DB locked.
You can't restore when you have the Hello database opened as you have by
selecting the Hello database from teh dropdown menu.
Change databases to any other database and try again. If you still get the
error close down all the EM and QA sessions and try it again.
You can use the sp_who command to see how is in what database
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"Erland" <Erland.Erikson@.gmail.com> wrote in message
news:1132539237.114914.201650@.o13g2000cwo.googlegroups.com...
> Hi,
> I have read restore on BOL , but i am unable to restore a SQL 7.0 back
> up to SQL Server 2000. I have tried things given in the following
> article
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_15ro.asp
> and i have also tried to restore the back up by using the following
> procedure.
> My database name is "Hello". I have first created a new database named
> "Hello" by using Enterprise Manager and then i opened up Query Analyzer
> and choose the database named 'Hello' from dropdown and wrote down the
> following
> Restore DATABASE HELLO
>>From disk = 'd:\Documents and
> Settings\Administrator\DEsktop\backup\Hello'
> WITH Move 'Hello' to 'D:\test\Hello.mdf'
> Go
> But i get some errors as in "Exclusive access could not be obtained
> because the datavase is in use' and 'Restore Database is terminating
> abnormally.
> Note: My SQL 7.0 back up stored in the location 'd:\Documents and
> Settings\Administrator\DEsktop\backup\Hello'. I am using SQL Server
> 2000Enterprise Edition on Windows 2000 Advanced Server.
> Please do reply me with what i ammissing. I have read quite a few
> things on BOL and on MSDN. However, i am stumped. I would really
> appreciate any help as im a newbie so please point me to the solution
> or to any article / online-resource that would lead me to the solution.
> Take care and looking forward.
> Please do reply me what i am missing here.
>|||Hi ,
Many thanks for your message. I tried the following by choosing
someother database from dropdown in QueryAnalyzer.
Restre Database Hello
from disk='d:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKUP\Hello'
iwth move 'hello' to ''d:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKUP\Hello.mdf'
Go
but i get the following errors
i)-Logical file 'hello' is not part of database 'hello'. Use Restore
filelistonly to list the logical file names
ii)-Restore database is terminating abnormally.
Then i tried the following
Restore filelistonly
from disk='d:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKUP\Hello'
Restore Database Hello
from disk='d:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKUP\Hello'
with move 'hello' to ''d:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKUP\Hello.mdf'
Go
but i get the same two errors . Just to remind you i am trying to
restore a SQL 7.0 back up on SQL Server 2000 and my SQL 7.0 back is
named as Hello and store at following location
D:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKUP\Hello
Now i don't have any idea what i am missing here. Any help or comments
will be highly appreciated. Please help.
Thanks
-Erland.
GregO wrote:
> Hi Erland,
> The error indicates that you have the DB locked.
> You can't restore when you have the Hello database opened as you have by
> selecting the Hello database from teh dropdown menu.
> Change databases to any other database and try again. If you still get the
> error close down all the EM and QA sessions and try it again.
> You can use the sp_who command to see how is in what database
> kind regards
> Greg O
> --
> Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
> AGS SQL 2005 Utilities, over 20+ functions
> http://www.ag-software.com/?tabid=38
>
> "Erland" <Erland.Erikson@.gmail.com> wrote in message
> news:1132539237.114914.201650@.o13g2000cwo.googlegroups.com...
> > Hi,
> > I have read restore on BOL , but i am unable to restore a SQL 7.0 back
> > up to SQL Server 2000. I have tried things given in the following
> > article
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_15ro.asp
> >
> > and i have also tried to restore the back up by using the following
> > procedure.
> > My database name is "Hello". I have first created a new database named
> > "Hello" by using Enterprise Manager and then i opened up Query Analyzer
> > and choose the database named 'Hello' from dropdown and wrote down the
> > following
> > Restore DATABASE HELLO
> >>From disk = 'd:\Documents and
> > Settings\Administrator\DEsktop\backup\Hello'
> > WITH Move 'Hello' to 'D:\test\Hello.mdf'
> > Go
> > But i get some errors as in "Exclusive access could not be obtained
> > because the datavase is in use' and 'Restore Database is terminating
> > abnormally.
> > Note: My SQL 7.0 back up stored in the location 'd:\Documents and
> > Settings\Administrator\DEsktop\backup\Hello'. I am using SQL Server
> > 2000Enterprise Edition on Windows 2000 Advanced Server.
> > Please do reply me with what i ammissing. I have read quite a few
> > things on BOL and on MSDN. However, i am stumped. I would really
> > appreciate any help as im a newbie so please point me to the solution
> > or to any article / online-resource that would lead me to the solution.
> > Take care and looking forward.
> > Please do reply me what i am missing here.
> >

Restoring Databases from Enterprise Edition to an enviroment with Standard Edition

I believe it is possible to restore databases from Enterprise edition to an enviroment with Standard edition. We have been able to do it on databases without any partitioned tables. But if a database has partitioned tables it will not start up in the Standard edition after a successful restore. The error log states that the database will not start because partitions are not allowed in Standard edition (which we knew). But we were led to believe that the databases would restore and open fine, the partitions would just not be there in Standard edition. Are we possibly doing something wrong or will this just not work?

Thanks

Chris

This will just not work.

Where did you get your info on partitioned tables in standard edition?

|||

From a vendor who must not have researched it enough. Thanks

|||

Chris:

If you have partitioned table in a database that was created using Enterprise Edition, it will fail to attach to the Standard SKU. Unpartitioning a table is an expensive operation and there by cannot be done transparently as part of the restore. What you can do is to restore this database on Developers Edition, remove partitioning, backup the database and then restore on standard.

I was wondering if you can provide more details on your scenario of why you want to migrate your database to Standard SKU. Also, I am curious to find more about your comment 'we were led to believe....' . Is this mentioned in some documentation?

Thanks

Sunil

|||

It is really a cost saving thing. We are a small company and want to take advantage of the benefits of Enterprise edition in our production environment, but can't afford a 2nd, 3rd, and 4th copy of Enterprise for our test environments. So we wanted to leave our testing enviroments as Standard. This way we could test system functionality and web functionality in an environment where we didn't need partitioning, etc. But we need at times to move the live data down into test at times.

The "led to believe" part came from a third party that we use for some development who said that they had tested this. But they may have been confused. Thanks.

|||

Ok. One quick observation is that this way you can test for functionality but not for performance because partitioned table will have a different performance profile than the corresponding unpartitioned table.

|||As long as you don't use the test servers in production then you should use developer edition, you need licenses but it is considerably cheaper than standard.