I have a backup of a SQL7 DB that i need to restore into SQL 2000. I only have the backup of the DB , i don't have access to the SQL7 server. When ever I try to restore the DB into SQL2000 nothing works, the tables and properties all restore properly and the data is there but i can't retrieve info from it. I'm trying to move a website DB from SQL7 to SQL 2000 for use with Cold Fusion. I know they're is issues with going from 7 to 2000 but i don't know what they are. If anyone could help me with this i would greatly appreciate it. I know I need to do some special steps or changes before it will work in 2000, does anyone know what these are?
Thanks in advance,
B"...I can't retrieve info from it..."
can you provide a few more details? What are you using to retrieve info (QA, development IDE, EM)? What error messages are you getting?
Recall that if you restore a database onto a new server, the logins and permissions don't convey (kind of like the furniture in my house when I go to sell it).
Permissions may be a part of your issue.
Regards,
hmscott|||I've got it working, I had to do a DTS Import and I checked Use Collation. This seemed to fix the problem, thanks to those who helped!!!
Showing posts with label sql2000. Show all posts
Showing posts with label sql2000. Show all posts
Wednesday, March 28, 2012
Monday, March 19, 2012
move table from SQL2000 to SQL2005?
I need to move some tables from an SQL2000 database on one server to an
SQL2005 database on another server(and in some cases back again). I don't
have enterprise manager just SQL Manager Express which doesn't play nice with
SQL2000.
Any suggestions would be appreciated. T-SQL solutions would be preferred
because they're free ;)
Thanks!
Hi
"Dabbler" wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice with
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
If you don't want to re-create the table definition (which you would have to
script and run through SQLCMD if you did!) then you can populate the table
using BCP or if you have a linked server INSERT...SELECT
John
|||I don't think I can use BCP because both servers are hosted at Appliedi.net
so I don't have access to their file systems.
Is there a way to simultaneously connect to two databases on two different
servers with T-SQL? that would allow me to use your "linked server"
suggestion.
Thanks much!
Michael
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
>
> If you don't want to re-create the table definition (which you would have to
> script and run through SQLCMD if you did!) then you can populate the table
> using BCP or if you have a linked server INSERT...SELECT
> John
|||Hi
"Dabbler" wrote:
[vbcol=seagreen]
> I don't think I can use BCP because both servers are hosted at Appliedi.net
> so I don't have access to their file systems.
> Is there a way to simultaneously connect to two databases on two different
> servers with T-SQL? that would allow me to use your "linked server"
> suggestion.
> Thanks much!
> Michael
> "John Bell" wrote:
Use sp_addlinkedserver to create the linked server and use either OPENQUERY
or four part names to run the query on the linked server.
John
|||Thanks John, that's the clue I needed.
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
>
> Use sp_addlinkedserver to create the linked server and use either OPENQUERY
> or four part names to run the query on the linked server.
> John
|||On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice with
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
I noticed you mentioned that you don't have SQL Server Management
Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
the tools come with the DVD. If you used the tool, you could simply
use the copy database wizard to achieve these results. If you wanted
to do another method, you could simply utilize the backup and restore
method using your normal T-SQL methods. Simply backup your 2000
database and restore it on your 2005 database.
Again, I might be missing something, but wanted to assist you in any
way possible.
Aaron
|||Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
SQL Server 2005 Express. I only have SSMS Express which is lite version of
SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
but of course the install blocks because of my SQL Server Express install. Of
course all I really need is Enterprise Manager but I'm not an Enterprise,
just an independent developer ;)
"acorcoran" wrote:
> On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> I noticed you mentioned that you don't have SQL Server Management
> Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
> the tools come with the DVD. If you used the tool, you could simply
> use the copy database wizard to achieve these results. If you wanted
> to do another method, you could simply utilize the backup and restore
> method using your normal T-SQL methods. Simply backup your 2000
> database and restore it on your 2005 database.
> Again, I might be missing something, but wanted to assist you in any
> way possible.
> Aaron
>
|||Hi
"Dabbler" wrote:
> Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
> SQL Server 2005 Express. I only have SSMS Express which is lite version of
> SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
> but of course the install blocks because of my SQL Server Express install. Of
> course all I really need is Enterprise Manager but I'm not an Enterprise,
> just an independent developer ;)
>
You may want to consider buying yourself a copy of the developer edition
which is about $50 even if your deployments are on the Express, although for
your current issue it may not help.
John
|||Thanks John... I will get DEV, just have been putting it off, especially
since I'm concerned about the memory footprint of full SQL2005 vs express
edition. In the meantime I've installed the client tools from the trial
version which should hold me till I win the lottery ;)
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
>
> You may want to consider buying yourself a copy of the developer edition
> which is about $50 even if your deployments are on the Express, although for
> your current issue it may not help.
> John
|||Hi
"Dabbler" wrote:
> Thanks John... I will get DEV, just have been putting it off, especially
> since I'm concerned about the memory footprint of full SQL2005 vs express
> edition. In the meantime I've installed the client tools from the trial
> version which should hold me till I win the lottery ;)
>
For $50 you won't need all the numbers!! Using the tools should prove that
it is excellent value!
John
SQL2005 database on another server(and in some cases back again). I don't
have enterprise manager just SQL Manager Express which doesn't play nice with
SQL2000.
Any suggestions would be appreciated. T-SQL solutions would be preferred
because they're free ;)
Thanks!
Hi
"Dabbler" wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice with
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
If you don't want to re-create the table definition (which you would have to
script and run through SQLCMD if you did!) then you can populate the table
using BCP or if you have a linked server INSERT...SELECT
John
|||I don't think I can use BCP because both servers are hosted at Appliedi.net
so I don't have access to their file systems.
Is there a way to simultaneously connect to two databases on two different
servers with T-SQL? that would allow me to use your "linked server"
suggestion.
Thanks much!
Michael
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
>
> If you don't want to re-create the table definition (which you would have to
> script and run through SQLCMD if you did!) then you can populate the table
> using BCP or if you have a linked server INSERT...SELECT
> John
|||Hi
"Dabbler" wrote:
[vbcol=seagreen]
> I don't think I can use BCP because both servers are hosted at Appliedi.net
> so I don't have access to their file systems.
> Is there a way to simultaneously connect to two databases on two different
> servers with T-SQL? that would allow me to use your "linked server"
> suggestion.
> Thanks much!
> Michael
> "John Bell" wrote:
Use sp_addlinkedserver to create the linked server and use either OPENQUERY
or four part names to run the query on the linked server.
John
|||Thanks John, that's the clue I needed.
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
>
> Use sp_addlinkedserver to create the linked server and use either OPENQUERY
> or four part names to run the query on the linked server.
> John
|||On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice with
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
I noticed you mentioned that you don't have SQL Server Management
Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
the tools come with the DVD. If you used the tool, you could simply
use the copy database wizard to achieve these results. If you wanted
to do another method, you could simply utilize the backup and restore
method using your normal T-SQL methods. Simply backup your 2000
database and restore it on your 2005 database.
Again, I might be missing something, but wanted to assist you in any
way possible.
Aaron
|||Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
SQL Server 2005 Express. I only have SSMS Express which is lite version of
SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
but of course the install blocks because of my SQL Server Express install. Of
course all I really need is Enterprise Manager but I'm not an Enterprise,
just an independent developer ;)
"acorcoran" wrote:
> On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> I noticed you mentioned that you don't have SQL Server Management
> Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
> the tools come with the DVD. If you used the tool, you could simply
> use the copy database wizard to achieve these results. If you wanted
> to do another method, you could simply utilize the backup and restore
> method using your normal T-SQL methods. Simply backup your 2000
> database and restore it on your 2005 database.
> Again, I might be missing something, but wanted to assist you in any
> way possible.
> Aaron
>
|||Hi
"Dabbler" wrote:
> Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
> SQL Server 2005 Express. I only have SSMS Express which is lite version of
> SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
> but of course the install blocks because of my SQL Server Express install. Of
> course all I really need is Enterprise Manager but I'm not an Enterprise,
> just an independent developer ;)
>
You may want to consider buying yourself a copy of the developer edition
which is about $50 even if your deployments are on the Express, although for
your current issue it may not help.
John
|||Thanks John... I will get DEV, just have been putting it off, especially
since I'm concerned about the memory footprint of full SQL2005 vs express
edition. In the meantime I've installed the client tools from the trial
version which should hold me till I win the lottery ;)
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
>
> You may want to consider buying yourself a copy of the developer edition
> which is about $50 even if your deployments are on the Express, although for
> your current issue it may not help.
> John
|||Hi
"Dabbler" wrote:
> Thanks John... I will get DEV, just have been putting it off, especially
> since I'm concerned about the memory footprint of full SQL2005 vs express
> edition. In the meantime I've installed the client tools from the trial
> version which should hold me till I win the lottery ;)
>
For $50 you won't need all the numbers!! Using the tools should prove that
it is excellent value!
John
move table from SQL2000 to SQL2005?
I need to move some tables from an SQL2000 database on one server to an
SQL2005 database on another server(and in some cases back again). I don't
have enterprise manager just SQL Manager Express which doesn't play nice wit
h
SQL2000.
Any suggestions would be appreciated. T-SQL solutions would be preferred
because they're free ;)
Thanks!Hi
"Dabbler" wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice w
ith
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
If you don't want to re-create the table definition (which you would have to
script and run through SQLCMD if you did!) then you can populate the table
using BCP or if you have a linked server INSERT...SELECT
John|||I don't think I can use BCP because both servers are hosted at Appliedi.net
so I don't have access to their file systems.
Is there a way to simultaneously connect to two databases on two different
servers with T-SQL? that would allow me to use your "linked server"
suggestion.
Thanks much!
Michael
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
>
> If you don't want to re-create the table definition (which you would have
to
> script and run through SQLCMD if you did!) then you can populate the table
> using BCP or if you have a linked server INSERT...SELECT
> John|||Hi
"Dabbler" wrote:
[vbcol=seagreen]
> I don't think I can use BCP because both servers are hosted at Appliedi.ne
t
> so I don't have access to their file systems.
> Is there a way to simultaneously connect to two databases on two different
> servers with T-SQL? that would allow me to use your "linked server"
> suggestion.
> Thanks much!
> Michael
> "John Bell" wrote:
>
Use sp_addlinkedserver to create the linked server and use either OPENQUERY
or four part names to run the query on the linked server.
John|||Thanks John, that's the clue I needed.
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
>
> Use sp_addlinkedserver to create the linked server and use either OPENQUER
Y
> or four part names to run the query on the linked server.
> John|||On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice w
ith
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
I noticed you mentioned that you don't have SQL Server Management
Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
the tools come with the DVD. If you used the tool, you could simply
use the copy database wizard to achieve these results. If you wanted
to do another method, you could simply utilize the backup and restore
method using your normal T-SQL methods. Simply backup your 2000
database and restore it on your 2005 database.
Again, I might be missing something, but wanted to assist you in any
way possible.
Aaron|||Ya, the DVD you're thinking of is full SQL Server 2005 but I have download o
f
SQL Server 2005 Express. I only have SSMS Express which is lite version of
SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
but of course the install blocks because of my SQL Server Express install. O
f
course all I really need is Enterprise Manager but I'm not an Enterprise,
just an independent developer ;)
"acorcoran" wrote:
> On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> I noticed you mentioned that you don't have SQL Server Management
> Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
> the tools come with the DVD. If you used the tool, you could simply
> use the copy database wizard to achieve these results. If you wanted
> to do another method, you could simply utilize the backup and restore
> method using your normal T-SQL methods. Simply backup your 2000
> database and restore it on your 2005 database.
> Again, I might be missing something, but wanted to assist you in any
> way possible.
> Aaron
>|||Hi
"Dabbler" wrote:
> Ya, the DVD you're thinking of is full SQL Server 2005 but I have download
of
> SQL Server 2005 Express. I only have SSMS Express which is lite version of
> SSMS. I'm trying to figure out how to install the full SQL Server 2005 tri
al
> but of course the install blocks because of my SQL Server Express install.
Of
> course all I really need is Enterprise Manager but I'm not an Enterprise,
> just an independent developer ;)
>
You may want to consider buying yourself a copy of the developer edition
which is about $50 even if your deployments are on the Express, although for
your current issue it may not help.
John|||Thanks John... I will get DEV, just have been putting it off, especially
since I'm concerned about the memory footprint of full SQL2005 vs express
edition. In the meantime I've installed the client tools from the trial
version which should hold me till I win the lottery ;)
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
>
> You may want to consider buying yourself a copy of the developer edition
> which is about $50 even if your deployments are on the Express, although f
or
> your current issue it may not help.
> John|||Hi
"Dabbler" wrote:
> Thanks John... I will get DEV, just have been putting it off, especially
> since I'm concerned about the memory footprint of full SQL2005 vs express
> edition. In the meantime I've installed the client tools from the trial
> version which should hold me till I win the lottery ;)
>
For $50 you won't need all the numbers!! Using the tools should prove that
it is excellent value!
John
SQL2005 database on another server(and in some cases back again). I don't
have enterprise manager just SQL Manager Express which doesn't play nice wit
h
SQL2000.
Any suggestions would be appreciated. T-SQL solutions would be preferred
because they're free ;)
Thanks!Hi
"Dabbler" wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice w
ith
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
If you don't want to re-create the table definition (which you would have to
script and run through SQLCMD if you did!) then you can populate the table
using BCP or if you have a linked server INSERT...SELECT
John|||I don't think I can use BCP because both servers are hosted at Appliedi.net
so I don't have access to their file systems.
Is there a way to simultaneously connect to two databases on two different
servers with T-SQL? that would allow me to use your "linked server"
suggestion.
Thanks much!
Michael
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
>
> If you don't want to re-create the table definition (which you would have
to
> script and run through SQLCMD if you did!) then you can populate the table
> using BCP or if you have a linked server INSERT...SELECT
> John|||Hi
"Dabbler" wrote:
[vbcol=seagreen]
> I don't think I can use BCP because both servers are hosted at Appliedi.ne
t
> so I don't have access to their file systems.
> Is there a way to simultaneously connect to two databases on two different
> servers with T-SQL? that would allow me to use your "linked server"
> suggestion.
> Thanks much!
> Michael
> "John Bell" wrote:
>
Use sp_addlinkedserver to create the linked server and use either OPENQUERY
or four part names to run the query on the linked server.
John|||Thanks John, that's the clue I needed.
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
>
> Use sp_addlinkedserver to create the linked server and use either OPENQUER
Y
> or four part names to run the query on the linked server.
> John|||On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice w
ith
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
I noticed you mentioned that you don't have SQL Server Management
Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
the tools come with the DVD. If you used the tool, you could simply
use the copy database wizard to achieve these results. If you wanted
to do another method, you could simply utilize the backup and restore
method using your normal T-SQL methods. Simply backup your 2000
database and restore it on your 2005 database.
Again, I might be missing something, but wanted to assist you in any
way possible.
Aaron|||Ya, the DVD you're thinking of is full SQL Server 2005 but I have download o
f
SQL Server 2005 Express. I only have SSMS Express which is lite version of
SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
but of course the install blocks because of my SQL Server Express install. O
f
course all I really need is Enterprise Manager but I'm not an Enterprise,
just an independent developer ;)
"acorcoran" wrote:
> On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> I noticed you mentioned that you don't have SQL Server Management
> Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
> the tools come with the DVD. If you used the tool, you could simply
> use the copy database wizard to achieve these results. If you wanted
> to do another method, you could simply utilize the backup and restore
> method using your normal T-SQL methods. Simply backup your 2000
> database and restore it on your 2005 database.
> Again, I might be missing something, but wanted to assist you in any
> way possible.
> Aaron
>|||Hi
"Dabbler" wrote:
> Ya, the DVD you're thinking of is full SQL Server 2005 but I have download
of
> SQL Server 2005 Express. I only have SSMS Express which is lite version of
> SSMS. I'm trying to figure out how to install the full SQL Server 2005 tri
al
> but of course the install blocks because of my SQL Server Express install.
Of
> course all I really need is Enterprise Manager but I'm not an Enterprise,
> just an independent developer ;)
>
You may want to consider buying yourself a copy of the developer edition
which is about $50 even if your deployments are on the Express, although for
your current issue it may not help.
John|||Thanks John... I will get DEV, just have been putting it off, especially
since I'm concerned about the memory footprint of full SQL2005 vs express
edition. In the meantime I've installed the client tools from the trial
version which should hold me till I win the lottery ;)
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
>
> You may want to consider buying yourself a copy of the developer edition
> which is about $50 even if your deployments are on the Express, although f
or
> your current issue it may not help.
> John|||Hi
"Dabbler" wrote:
> Thanks John... I will get DEV, just have been putting it off, especially
> since I'm concerned about the memory footprint of full SQL2005 vs express
> edition. In the meantime I've installed the client tools from the trial
> version which should hold me till I win the lottery ;)
>
For $50 you won't need all the numbers!! Using the tools should prove that
it is excellent value!
John
move table from SQL2000 to SQL2005?
I need to move some tables from an SQL2000 database on one server to an
SQL2005 database on another server(and in some cases back again). I don't
have enterprise manager just SQL Manager Express which doesn't play nice with
SQL2000.
Any suggestions would be appreciated. T-SQL solutions would be preferred
because they're free ;)
Thanks!Hi
"Dabbler" wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice with
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
If you don't want to re-create the table definition (which you would have to
script and run through SQLCMD if you did!) then you can populate the table
using BCP or if you have a linked server INSERT...SELECT
John|||I don't think I can use BCP because both servers are hosted at Appliedi.net
so I don't have access to their file systems.
Is there a way to simultaneously connect to two databases on two different
servers with T-SQL? that would allow me to use your "linked server"
suggestion.
Thanks much!
Michael
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
> > I need to move some tables from an SQL2000 database on one server to an
> > SQL2005 database on another server(and in some cases back again). I don't
> > have enterprise manager just SQL Manager Express which doesn't play nice with
> > SQL2000.
> >
> > Any suggestions would be appreciated. T-SQL solutions would be preferred
> > because they're free ;)
> >
> > Thanks!
> If you don't want to re-create the table definition (which you would have to
> script and run through SQLCMD if you did!) then you can populate the table
> using BCP or if you have a linked server INSERT...SELECT
> John|||Hi
"Dabbler" wrote:
> I don't think I can use BCP because both servers are hosted at Appliedi.net
> so I don't have access to their file systems.
> Is there a way to simultaneously connect to two databases on two different
> servers with T-SQL? that would allow me to use your "linked server"
> suggestion.
> Thanks much!
> Michael
> "John Bell" wrote:
> > Hi
> >
> > "Dabbler" wrote:
> >
> > > I need to move some tables from an SQL2000 database on one server to an
> > > SQL2005 database on another server(and in some cases back again). I don't
> > > have enterprise manager just SQL Manager Express which doesn't play nice with
> > > SQL2000.
> > >
> > > Any suggestions would be appreciated. T-SQL solutions would be preferred
> > > because they're free ;)
> > >
> > > Thanks!
> >
> > If you don't want to re-create the table definition (which you would have to
> > script and run through SQLCMD if you did!) then you can populate the table
> > using BCP or if you have a linked server INSERT...SELECT
> >
> > John
Use sp_addlinkedserver to create the linked server and use either OPENQUERY
or four part names to run the query on the linked server.
John|||Thanks John, that's the clue I needed.
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
> > I don't think I can use BCP because both servers are hosted at Appliedi.net
> > so I don't have access to their file systems.
> >
> > Is there a way to simultaneously connect to two databases on two different
> > servers with T-SQL? that would allow me to use your "linked server"
> > suggestion.
> >
> > Thanks much!
> >
> > Michael
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > "Dabbler" wrote:
> > >
> > > > I need to move some tables from an SQL2000 database on one server to an
> > > > SQL2005 database on another server(and in some cases back again). I don't
> > > > have enterprise manager just SQL Manager Express which doesn't play nice with
> > > > SQL2000.
> > > >
> > > > Any suggestions would be appreciated. T-SQL solutions would be preferred
> > > > because they're free ;)
> > > >
> > > > Thanks!
> > >
> > > If you don't want to re-create the table definition (which you would have to
> > > script and run through SQLCMD if you did!) then you can populate the table
> > > using BCP or if you have a linked server INSERT...SELECT
> > >
> > > John
> Use sp_addlinkedserver to create the linked server and use either OPENQUERY
> or four part names to run the query on the linked server.
> John|||On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice with
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
I noticed you mentioned that you don't have SQL Server Management
Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
the tools come with the DVD. If you used the tool, you could simply
use the copy database wizard to achieve these results. If you wanted
to do another method, you could simply utilize the backup and restore
method using your normal T-SQL methods. Simply backup your 2000
database and restore it on your 2005 database.
Again, I might be missing something, but wanted to assist you in any
way possible.
Aaron|||Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
SQL Server 2005 Express. I only have SSMS Express which is lite version of
SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
but of course the install blocks because of my SQL Server Express install. Of
course all I really need is Enterprise Manager but I'm not an Enterprise,
just an independent developer ;)
"acorcoran" wrote:
> On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> > I need to move some tables from an SQL2000 database on one server to an
> > SQL2005 database on another server(and in some cases back again). I don't
> > have enterprise manager just SQL Manager Express which doesn't play nice with
> > SQL2000.
> >
> > Any suggestions would be appreciated. T-SQL solutions would be preferred
> > because they're free ;)
> >
> > Thanks!
> I noticed you mentioned that you don't have SQL Server Management
> Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
> the tools come with the DVD. If you used the tool, you could simply
> use the copy database wizard to achieve these results. If you wanted
> to do another method, you could simply utilize the backup and restore
> method using your normal T-SQL methods. Simply backup your 2000
> database and restore it on your 2005 database.
> Again, I might be missing something, but wanted to assist you in any
> way possible.
> Aaron
>|||Hi
"Dabbler" wrote:
> Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
> SQL Server 2005 Express. I only have SSMS Express which is lite version of
> SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
> but of course the install blocks because of my SQL Server Express install. Of
> course all I really need is Enterprise Manager but I'm not an Enterprise,
> just an independent developer ;)
>
You may want to consider buying yourself a copy of the developer edition
which is about $50 even if your deployments are on the Express, although for
your current issue it may not help.
John|||Thanks John... I will get DEV, just have been putting it off, especially
since I'm concerned about the memory footprint of full SQL2005 vs express
edition. In the meantime I've installed the client tools from the trial
version which should hold me till I win the lottery ;)
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
> > Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
> > SQL Server 2005 Express. I only have SSMS Express which is lite version of
> > SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
> > but of course the install blocks because of my SQL Server Express install. Of
> > course all I really need is Enterprise Manager but I'm not an Enterprise,
> > just an independent developer ;)
> >
> You may want to consider buying yourself a copy of the developer edition
> which is about $50 even if your deployments are on the Express, although for
> your current issue it may not help.
> John|||Hi
"Dabbler" wrote:
> Thanks John... I will get DEV, just have been putting it off, especially
> since I'm concerned about the memory footprint of full SQL2005 vs express
> edition. In the meantime I've installed the client tools from the trial
> version which should hold me till I win the lottery ;)
>
For $50 you won't need all the numbers!! Using the tools should prove that
it is excellent value!
John
SQL2005 database on another server(and in some cases back again). I don't
have enterprise manager just SQL Manager Express which doesn't play nice with
SQL2000.
Any suggestions would be appreciated. T-SQL solutions would be preferred
because they're free ;)
Thanks!Hi
"Dabbler" wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice with
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
If you don't want to re-create the table definition (which you would have to
script and run through SQLCMD if you did!) then you can populate the table
using BCP or if you have a linked server INSERT...SELECT
John|||I don't think I can use BCP because both servers are hosted at Appliedi.net
so I don't have access to their file systems.
Is there a way to simultaneously connect to two databases on two different
servers with T-SQL? that would allow me to use your "linked server"
suggestion.
Thanks much!
Michael
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
> > I need to move some tables from an SQL2000 database on one server to an
> > SQL2005 database on another server(and in some cases back again). I don't
> > have enterprise manager just SQL Manager Express which doesn't play nice with
> > SQL2000.
> >
> > Any suggestions would be appreciated. T-SQL solutions would be preferred
> > because they're free ;)
> >
> > Thanks!
> If you don't want to re-create the table definition (which you would have to
> script and run through SQLCMD if you did!) then you can populate the table
> using BCP or if you have a linked server INSERT...SELECT
> John|||Hi
"Dabbler" wrote:
> I don't think I can use BCP because both servers are hosted at Appliedi.net
> so I don't have access to their file systems.
> Is there a way to simultaneously connect to two databases on two different
> servers with T-SQL? that would allow me to use your "linked server"
> suggestion.
> Thanks much!
> Michael
> "John Bell" wrote:
> > Hi
> >
> > "Dabbler" wrote:
> >
> > > I need to move some tables from an SQL2000 database on one server to an
> > > SQL2005 database on another server(and in some cases back again). I don't
> > > have enterprise manager just SQL Manager Express which doesn't play nice with
> > > SQL2000.
> > >
> > > Any suggestions would be appreciated. T-SQL solutions would be preferred
> > > because they're free ;)
> > >
> > > Thanks!
> >
> > If you don't want to re-create the table definition (which you would have to
> > script and run through SQLCMD if you did!) then you can populate the table
> > using BCP or if you have a linked server INSERT...SELECT
> >
> > John
Use sp_addlinkedserver to create the linked server and use either OPENQUERY
or four part names to run the query on the linked server.
John|||Thanks John, that's the clue I needed.
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
> > I don't think I can use BCP because both servers are hosted at Appliedi.net
> > so I don't have access to their file systems.
> >
> > Is there a way to simultaneously connect to two databases on two different
> > servers with T-SQL? that would allow me to use your "linked server"
> > suggestion.
> >
> > Thanks much!
> >
> > Michael
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > "Dabbler" wrote:
> > >
> > > > I need to move some tables from an SQL2000 database on one server to an
> > > > SQL2005 database on another server(and in some cases back again). I don't
> > > > have enterprise manager just SQL Manager Express which doesn't play nice with
> > > > SQL2000.
> > > >
> > > > Any suggestions would be appreciated. T-SQL solutions would be preferred
> > > > because they're free ;)
> > > >
> > > > Thanks!
> > >
> > > If you don't want to re-create the table definition (which you would have to
> > > script and run through SQLCMD if you did!) then you can populate the table
> > > using BCP or if you have a linked server INSERT...SELECT
> > >
> > > John
> Use sp_addlinkedserver to create the linked server and use either OPENQUERY
> or four part names to run the query on the linked server.
> John|||On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> I need to move some tables from an SQL2000 database on one server to an
> SQL2005 database on another server(and in some cases back again). I don't
> have enterprise manager just SQL Manager Express which doesn't play nice with
> SQL2000.
> Any suggestions would be appreciated. T-SQL solutions would be preferred
> because they're free ;)
> Thanks!
I noticed you mentioned that you don't have SQL Server Management
Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
the tools come with the DVD. If you used the tool, you could simply
use the copy database wizard to achieve these results. If you wanted
to do another method, you could simply utilize the backup and restore
method using your normal T-SQL methods. Simply backup your 2000
database and restore it on your 2005 database.
Again, I might be missing something, but wanted to assist you in any
way possible.
Aaron|||Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
SQL Server 2005 Express. I only have SSMS Express which is lite version of
SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
but of course the install blocks because of my SQL Server Express install. Of
course all I really need is Enterprise Manager but I'm not an Enterprise,
just an independent developer ;)
"acorcoran" wrote:
> On Jul 5, 11:08 am, Dabbler <Dabb...@.discussions.microsoft.com> wrote:
> > I need to move some tables from an SQL2000 database on one server to an
> > SQL2005 database on another server(and in some cases back again). I don't
> > have enterprise manager just SQL Manager Express which doesn't play nice with
> > SQL2000.
> >
> > Any suggestions would be appreciated. T-SQL solutions would be preferred
> > because they're free ;)
> >
> > Thanks!
> I noticed you mentioned that you don't have SQL Server Management
> Studio (SSMS)...but wasn't sure if this simply wasn't plausible, as
> the tools come with the DVD. If you used the tool, you could simply
> use the copy database wizard to achieve these results. If you wanted
> to do another method, you could simply utilize the backup and restore
> method using your normal T-SQL methods. Simply backup your 2000
> database and restore it on your 2005 database.
> Again, I might be missing something, but wanted to assist you in any
> way possible.
> Aaron
>|||Hi
"Dabbler" wrote:
> Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
> SQL Server 2005 Express. I only have SSMS Express which is lite version of
> SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
> but of course the install blocks because of my SQL Server Express install. Of
> course all I really need is Enterprise Manager but I'm not an Enterprise,
> just an independent developer ;)
>
You may want to consider buying yourself a copy of the developer edition
which is about $50 even if your deployments are on the Express, although for
your current issue it may not help.
John|||Thanks John... I will get DEV, just have been putting it off, especially
since I'm concerned about the memory footprint of full SQL2005 vs express
edition. In the meantime I've installed the client tools from the trial
version which should hold me till I win the lottery ;)
"John Bell" wrote:
> Hi
> "Dabbler" wrote:
> > Ya, the DVD you're thinking of is full SQL Server 2005 but I have download of
> > SQL Server 2005 Express. I only have SSMS Express which is lite version of
> > SSMS. I'm trying to figure out how to install the full SQL Server 2005 trial
> > but of course the install blocks because of my SQL Server Express install. Of
> > course all I really need is Enterprise Manager but I'm not an Enterprise,
> > just an independent developer ;)
> >
> You may want to consider buying yourself a copy of the developer edition
> which is about $50 even if your deployments are on the Express, although for
> your current issue it may not help.
> John|||Hi
"Dabbler" wrote:
> Thanks John... I will get DEV, just have been putting it off, especially
> since I'm concerned about the memory footprint of full SQL2005 vs express
> edition. In the meantime I've installed the client tools from the trial
> version which should hold me till I win the lottery ;)
>
For $50 you won't need all the numbers!! Using the tools should prove that
it is excellent value!
John
Move SQL2005 from Default Instance to Named Instance
I have a server with sql server 2005 installed as the default instance -- I have a piece of software that needs SQL2000 to be the default instance. Is there a way other than install new sql2005 named instance and move databases to rename my SQL2005 instance from <machinename> to <machinename>\sql05 for example?
Bryan
SQL Server 2005 instance name cannot be changed after installation. You need to install second names instance side by side, old the databases and uninstall the default instance. Then you can install SQL 2000 default instance.
|||Not unexpected and not unreasonable that this cannot be done. Just hoping I could be a little lazier
Move SQL2005 from Default Instance to Named Instance
I have a server with sql server 2005 installed as the default instance -- I have a piece of software that needs SQL2000 to be the default instance. Is there a way other than install new sql2005 named instance and move databases to rename my SQL2005 instance from <machinename> to <machinename>\sql05 for example?
Bryan
SQL Server 2005 instance name cannot be changed after installation. You need to install second names instance side by side, old the databases and uninstall the default instance. Then you can install SQL 2000 default instance.
|||Not unexpected and not unreasonable that this cannot be done. Just hoping I could be a little lazier
Move SQl2000 database to another SQL2000 server
Is there I a KB article on this? I know absolutely nothing about SQL and
could use some help.
Thanks in advance.The database I am wanting to move to a new server is located in
d:\sql\mssql\....
I am wanting to restore it to C:\Program Files\Microsoft SQL
Server\MSSQL\... on a completely different server.
"butter" wrote:
> Is there I a KB article on this? I know absolutely nothing about SQL and
> could use some help.
> Thanks in advance.|||Perhaps this one?
http://www.support.microsoft.com/?id=314546
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"butter" <butter@.discussions.microsoft.com> wrote in message
news:F7C6FF13-9BCF-412F-A7A8-8D6B0B9AF8CA@.microsoft.com...
> The database I am wanting to move to a new server is located in
> d:\sql\mssql\....
> I am wanting to restore it to C:\Program Files\Microsoft SQL
> Server\MSSQL\... on a completely different server.
> "butter" wrote:
>> Is there I a KB article on this? I know absolutely nothing about SQL and
>> could use some help.
>> Thanks in advance.|||Hi,
you will do this in differnet method.
you can take a backup and restore to other server, otherwise you can use DTS
and also you can use attach database by copying the mdf and ldf file to other
server.
hope this will help
Herbert
"butter" wrote:
> The database I am wanting to move to a new server is located in
> d:\sql\mssql\....
> I am wanting to restore it to C:\Program Files\Microsoft SQL
> Server\MSSQL\... on a completely different server.
> "butter" wrote:
> > Is there I a KB article on this? I know absolutely nothing about SQL and
> > could use some help.
> >
> > Thanks in advance.
could use some help.
Thanks in advance.The database I am wanting to move to a new server is located in
d:\sql\mssql\....
I am wanting to restore it to C:\Program Files\Microsoft SQL
Server\MSSQL\... on a completely different server.
"butter" wrote:
> Is there I a KB article on this? I know absolutely nothing about SQL and
> could use some help.
> Thanks in advance.|||Perhaps this one?
http://www.support.microsoft.com/?id=314546
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"butter" <butter@.discussions.microsoft.com> wrote in message
news:F7C6FF13-9BCF-412F-A7A8-8D6B0B9AF8CA@.microsoft.com...
> The database I am wanting to move to a new server is located in
> d:\sql\mssql\....
> I am wanting to restore it to C:\Program Files\Microsoft SQL
> Server\MSSQL\... on a completely different server.
> "butter" wrote:
>> Is there I a KB article on this? I know absolutely nothing about SQL and
>> could use some help.
>> Thanks in advance.|||Hi,
you will do this in differnet method.
you can take a backup and restore to other server, otherwise you can use DTS
and also you can use attach database by copying the mdf and ldf file to other
server.
hope this will help
Herbert
"butter" wrote:
> The database I am wanting to move to a new server is located in
> d:\sql\mssql\....
> I am wanting to restore it to C:\Program Files\Microsoft SQL
> Server\MSSQL\... on a completely different server.
> "butter" wrote:
> > Is there I a KB article on this? I know absolutely nothing about SQL and
> > could use some help.
> >
> > Thanks in advance.
Move SQl2000 database to another SQL2000 server
Is there I a KB article on this? I know absolutely nothing about SQL and
could use some help.
Thanks in advance.
The database I am wanting to move to a new server is located in
d:\sql\mssql\....
I am wanting to restore it to C:\Program Files\Microsoft SQL
Server\MSSQL\... on a completely different server.
"butter" wrote:
> Is there I a KB article on this? I know absolutely nothing about SQL and
> could use some help.
> Thanks in advance.
|||Perhaps this one?
http://www.support.microsoft.com/?id=314546
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"butter" <butter@.discussions.microsoft.com> wrote in message
news:F7C6FF13-9BCF-412F-A7A8-8D6B0B9AF8CA@.microsoft.com...[vbcol=seagreen]
> The database I am wanting to move to a new server is located in
> d:\sql\mssql\....
> I am wanting to restore it to C:\Program Files\Microsoft SQL
> Server\MSSQL\... on a completely different server.
> "butter" wrote:
|||Hi,
you will do this in differnet method.
you can take a backup and restore to other server, otherwise you can use DTS
and also you can use attach database by copying the mdf and ldf file to other
server.
hope this will help
Herbert
"butter" wrote:
[vbcol=seagreen]
> The database I am wanting to move to a new server is located in
> d:\sql\mssql\....
> I am wanting to restore it to C:\Program Files\Microsoft SQL
> Server\MSSQL\... on a completely different server.
> "butter" wrote:
could use some help.
Thanks in advance.
The database I am wanting to move to a new server is located in
d:\sql\mssql\....
I am wanting to restore it to C:\Program Files\Microsoft SQL
Server\MSSQL\... on a completely different server.
"butter" wrote:
> Is there I a KB article on this? I know absolutely nothing about SQL and
> could use some help.
> Thanks in advance.
|||Perhaps this one?
http://www.support.microsoft.com/?id=314546
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"butter" <butter@.discussions.microsoft.com> wrote in message
news:F7C6FF13-9BCF-412F-A7A8-8D6B0B9AF8CA@.microsoft.com...[vbcol=seagreen]
> The database I am wanting to move to a new server is located in
> d:\sql\mssql\....
> I am wanting to restore it to C:\Program Files\Microsoft SQL
> Server\MSSQL\... on a completely different server.
> "butter" wrote:
|||Hi,
you will do this in differnet method.
you can take a backup and restore to other server, otherwise you can use DTS
and also you can use attach database by copying the mdf and ldf file to other
server.
hope this will help
Herbert
"butter" wrote:
[vbcol=seagreen]
> The database I am wanting to move to a new server is located in
> d:\sql\mssql\....
> I am wanting to restore it to C:\Program Files\Microsoft SQL
> Server\MSSQL\... on a completely different server.
> "butter" wrote:
Move SQl2000 database to another SQL2000 server
Is there I a KB article on this? I know absolutely nothing about SQL and
could use some help.
Thanks in advance.The database I am wanting to move to a new server is located in
d:\sql\mssql\....
I am wanting to restore it to C:\Program Files\Microsoft SQL
Server\MSSQL\... on a completely different server.
"butter" wrote:
> Is there I a KB article on this? I know absolutely nothing about SQL and
> could use some help.
> Thanks in advance.|||Perhaps this one?
http://www.support.microsoft.com/?id=314546
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"butter" <butter@.discussions.microsoft.com> wrote in message
news:F7C6FF13-9BCF-412F-A7A8-8D6B0B9AF8CA@.microsoft.com...[vbcol=seagreen]
> The database I am wanting to move to a new server is located in
> d:\sql\mssql\....
> I am wanting to restore it to C:\Program Files\Microsoft SQL
> Server\MSSQL\... on a completely different server.
> "butter" wrote:
>|||Hi,
you will do this in differnet method.
you can take a backup and restore to other server, otherwise you can use DTS
and also you can use attach database by copying the mdf and ldf file to othe
r
server.
hope this will help
Herbert
"butter" wrote:
[vbcol=seagreen]
> The database I am wanting to move to a new server is located in
> d:\sql\mssql\....
> I am wanting to restore it to C:\Program Files\Microsoft SQL
> Server\MSSQL\... on a completely different server.
> "butter" wrote:
>
could use some help.
Thanks in advance.The database I am wanting to move to a new server is located in
d:\sql\mssql\....
I am wanting to restore it to C:\Program Files\Microsoft SQL
Server\MSSQL\... on a completely different server.
"butter" wrote:
> Is there I a KB article on this? I know absolutely nothing about SQL and
> could use some help.
> Thanks in advance.|||Perhaps this one?
http://www.support.microsoft.com/?id=314546
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"butter" <butter@.discussions.microsoft.com> wrote in message
news:F7C6FF13-9BCF-412F-A7A8-8D6B0B9AF8CA@.microsoft.com...[vbcol=seagreen]
> The database I am wanting to move to a new server is located in
> d:\sql\mssql\....
> I am wanting to restore it to C:\Program Files\Microsoft SQL
> Server\MSSQL\... on a completely different server.
> "butter" wrote:
>|||Hi,
you will do this in differnet method.
you can take a backup and restore to other server, otherwise you can use DTS
and also you can use attach database by copying the mdf and ldf file to othe
r
server.
hope this will help
Herbert
"butter" wrote:
[vbcol=seagreen]
> The database I am wanting to move to a new server is located in
> d:\sql\mssql\....
> I am wanting to restore it to C:\Program Files\Microsoft SQL
> Server\MSSQL\... on a completely different server.
> "butter" wrote:
>
Move SQL Server to new hardware
We need to move SQL to a new server hardware platform. Can you make any
suggestions or direct me to the relevent information. We are running
SQL2000 on Windows 2000 Advanced Server.
ThanksFound this sometime ago. It is from Tibor Karaszi. I have tried it and it
works, thanks Tibor.
------
One method, if you have the same directory structure on the two machines, is
to install on the new
machine, stop both SQL Servers and copy over *all* database files (mdf, ndf,
ldf). After that you
need to take care of two things (assuming the new machine has different name
from the old):
1. The machine name in master..sysservers:
EXEC sp_dropserver oldname
EXEC sp_addserver newname, LOCAL
2. The machine name for your SQL Server agent jobs. Here's my "canned
response on that":
The old machine name is still in msdb..sysjobs. Since you changed the
machine name, SQLAgent now
thinks that this job is owned by a master server. You can change
the machine names for all jobs with below SQL Statement. Warning: Hacking
system tables is not
supported and should be done with care (below should be rather
safe, though).
DECLARE @.srv sysname
SET @.srv = CAST(SERVERPROPERTY('ServerName') AS sysname)
UPDATE sysjobs SET originating_server = @.srv
NOTE:
If this is a target server (you have jobs sent from a master server), you
have to exclude them, so
you don't transfer those jobs into local jobs:
WHERE originating_server = 'oldservname'
If you want to do this the supported way, see below KB article
http://support.microsoft.com/default.aspx?scid=kb;en-us;281642
Tibor Karaszi, SQL Server MVP
------
"Randy Dalton" <RPDalton@.bibb.com> wrote in message
news:OIpxAuGCFHA.2804@.TK2MSFTNGP15.phx.gbl...
> We need to move SQL to a new server hardware platform. Can you make any
> suggestions or direct me to the relevent information. We are running
> SQL2000 on Windows 2000 Advanced Server.
> Thanks|||Randy
In addition you may find these articles useful.
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://support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
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 http://www.support.microsoft.com/?id=168001 User Logon and/or
Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission Issues
When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
Hope this helps
John
"Alex" wrote:
> Found this sometime ago. It is from Tibor Karaszi. I have tried it and it
> works, thanks Tibor.
> ------
> One method, if you have the same directory structure on the two machines, is
> to install on the new
> machine, stop both SQL Servers and copy over *all* database files (mdf, ndf,
> ldf). After that you
> need to take care of two things (assuming the new machine has different name
> from the old):
> 1. The machine name in master..sysservers:
> EXEC sp_dropserver oldname
> EXEC sp_addserver newname, LOCAL
> 2. The machine name for your SQL Server agent jobs. Here's my "canned
> response on that":
> The old machine name is still in msdb..sysjobs. Since you changed the
> machine name, SQLAgent now
> thinks that this job is owned by a master server. You can change
> the machine names for all jobs with below SQL Statement. Warning: Hacking
> system tables is not
> supported and should be done with care (below should be rather
> safe, though).
> DECLARE @.srv sysname
> SET @.srv = CAST(SERVERPROPERTY('ServerName') AS sysname)
> UPDATE sysjobs SET originating_server = @.srv
>
> NOTE:
> If this is a target server (you have jobs sent from a master server), you
> have to exclude them, so
> you don't transfer those jobs into local jobs:
> WHERE originating_server = 'oldservname'
>
> If you want to do this the supported way, see below KB article
> http://support.microsoft.com/default.aspx?scid=kb;en-us;281642
>
> --
> Tibor Karaszi, SQL Server MVP
> ------
>
> "Randy Dalton" <RPDalton@.bibb.com> wrote in message
> news:OIpxAuGCFHA.2804@.TK2MSFTNGP15.phx.gbl...
> > We need to move SQL to a new server hardware platform. Can you make any
> > suggestions or direct me to the relevent information. We are running
> > SQL2000 on Windows 2000 Advanced Server.
> >
> > Thanks
>
>
suggestions or direct me to the relevent information. We are running
SQL2000 on Windows 2000 Advanced Server.
ThanksFound this sometime ago. It is from Tibor Karaszi. I have tried it and it
works, thanks Tibor.
------
One method, if you have the same directory structure on the two machines, is
to install on the new
machine, stop both SQL Servers and copy over *all* database files (mdf, ndf,
ldf). After that you
need to take care of two things (assuming the new machine has different name
from the old):
1. The machine name in master..sysservers:
EXEC sp_dropserver oldname
EXEC sp_addserver newname, LOCAL
2. The machine name for your SQL Server agent jobs. Here's my "canned
response on that":
The old machine name is still in msdb..sysjobs. Since you changed the
machine name, SQLAgent now
thinks that this job is owned by a master server. You can change
the machine names for all jobs with below SQL Statement. Warning: Hacking
system tables is not
supported and should be done with care (below should be rather
safe, though).
DECLARE @.srv sysname
SET @.srv = CAST(SERVERPROPERTY('ServerName') AS sysname)
UPDATE sysjobs SET originating_server = @.srv
NOTE:
If this is a target server (you have jobs sent from a master server), you
have to exclude them, so
you don't transfer those jobs into local jobs:
WHERE originating_server = 'oldservname'
If you want to do this the supported way, see below KB article
http://support.microsoft.com/default.aspx?scid=kb;en-us;281642
Tibor Karaszi, SQL Server MVP
------
"Randy Dalton" <RPDalton@.bibb.com> wrote in message
news:OIpxAuGCFHA.2804@.TK2MSFTNGP15.phx.gbl...
> We need to move SQL to a new server hardware platform. Can you make any
> suggestions or direct me to the relevent information. We are running
> SQL2000 on Windows 2000 Advanced Server.
> Thanks|||Randy
In addition you may find these articles useful.
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://support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
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 http://www.support.microsoft.com/?id=168001 User Logon and/or
Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission Issues
When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
Hope this helps
John
"Alex" wrote:
> Found this sometime ago. It is from Tibor Karaszi. I have tried it and it
> works, thanks Tibor.
> ------
> One method, if you have the same directory structure on the two machines, is
> to install on the new
> machine, stop both SQL Servers and copy over *all* database files (mdf, ndf,
> ldf). After that you
> need to take care of two things (assuming the new machine has different name
> from the old):
> 1. The machine name in master..sysservers:
> EXEC sp_dropserver oldname
> EXEC sp_addserver newname, LOCAL
> 2. The machine name for your SQL Server agent jobs. Here's my "canned
> response on that":
> The old machine name is still in msdb..sysjobs. Since you changed the
> machine name, SQLAgent now
> thinks that this job is owned by a master server. You can change
> the machine names for all jobs with below SQL Statement. Warning: Hacking
> system tables is not
> supported and should be done with care (below should be rather
> safe, though).
> DECLARE @.srv sysname
> SET @.srv = CAST(SERVERPROPERTY('ServerName') AS sysname)
> UPDATE sysjobs SET originating_server = @.srv
>
> NOTE:
> If this is a target server (you have jobs sent from a master server), you
> have to exclude them, so
> you don't transfer those jobs into local jobs:
> WHERE originating_server = 'oldservname'
>
> If you want to do this the supported way, see below KB article
> http://support.microsoft.com/default.aspx?scid=kb;en-us;281642
>
> --
> Tibor Karaszi, SQL Server MVP
> ------
>
> "Randy Dalton" <RPDalton@.bibb.com> wrote in message
> news:OIpxAuGCFHA.2804@.TK2MSFTNGP15.phx.gbl...
> > We need to move SQL to a new server hardware platform. Can you make any
> > suggestions or direct me to the relevent information. We are running
> > SQL2000 on Windows 2000 Advanced Server.
> >
> > Thanks
>
>
Wednesday, March 7, 2012
move db from sql2000 to 2005sp1
I have sql2000 db that had two domain groups in the dbo role and one SQL
acct also in the dbo role. There are only tables in the db and all owned by
dbo, I am not sure if the developers created the tables and then changed the
owner or specified on creation. I restored the db to sql2005sp1 and setup
the logins via the sp_helprevlogins and now crap has hit the fan.
1) The domain group can login to SQL but when one of them clicks on the
user tables to expand they get the error:
Failed to retrieve data for this request (Microsoft.sql.server.smoenum)
Additional Information:
Am exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.sqlserver.connectioninfo)
Select permission denied on object 'extended properties', database
mssqlsystemresource', schema 'sys'.(Microsoft sql server, error 329)
I know the one that is having the issue is the one that created the tables.
Others in the domain group can see the tables.
2) Others in group that can see the table, when they try and create a new
table and get the following error:
Property DefaultSchema is not available for the Database '[XXXXDB]'. This
property may not exist for this object, or may not be retrievable due to
insufficient access rights. (sqleditors)
I guess I am missing something.
Thanks
CHi
It sounds like your upgrade procedure has not given the users the correct
permissions. I would expect all users within the same domain group to have
the same problems, therefore Iif they get different problems they are
probably in different groups.
Do you really want to grant such wide permissions to so many users? If you
created a database role with the minimum number of permissions you require
then the domain group could be added to that. In which case dropping the
domain group as a login and adding it again would not be an issue.
You may want to use GRANT LOGIN when you add the groups rather than
sp_addlogin.
John
"CD" wrote:
> I have sql2000 db that had two domain groups in the dbo role and one SQL
> acct also in the dbo role. There are only tables in the db and all owned by
> dbo, I am not sure if the developers created the tables and then changed the
> owner or specified on creation. I restored the db to sql2005sp1 and setup
> the logins via the sp_helprevlogins and now crap has hit the fan.
>
> 1) The domain group can login to SQL but when one of them clicks on the
> user tables to expand they get the error:
>
> Failed to retrieve data for this request (Microsoft.sql.server.smoenum)
> Additional Information:
> Am exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.sqlserver.connectioninfo)
> Select permission denied on object 'extended properties', database
> mssqlsystemresource', schema 'sys'.(Microsoft sql server, error 329)
>
> I know the one that is having the issue is the one that created the tables.
> Others in the domain group can see the tables.
>
> 2) Others in group that can see the table, when they try and create a new
> table and get the following error:
>
> Property DefaultSchema is not available for the Database '[XXXXDB]'. This
> property may not exist for this object, or may not be retrievable due to
> insufficient access rights. (sqleditors)
>
>
> I guess I am missing something.
>
> Thanks
> C
>
>|||Thanks for the reply, no actually the group constist of 3 domain accts with
the group being the database pre-defined dbo role, only one of them can not
see the table objects. I know this one created the tables on the sql2000
side and then changed the owner of the tables to dbo (error 1 note). The
other 2 accounts can see all the tables but can not create a new one.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9A9CE67E-59B0-4A5B-A797-4F02FCCD9A7C@.microsoft.com...
> Hi
> It sounds like your upgrade procedure has not given the users the correct
> permissions. I would expect all users within the same domain group to have
> the same problems, therefore Iif they get different problems they are
> probably in different groups.
> Do you really want to grant such wide permissions to so many users? If you
> created a database role with the minimum number of permissions you require
> then the domain group could be added to that. In which case dropping the
> domain group as a login and adding it again would not be an issue.
> You may want to use GRANT LOGIN when you add the groups rather than
> sp_addlogin.
> John
> "CD" wrote:
>> I have sql2000 db that had two domain groups in the dbo role and one SQL
>> acct also in the dbo role. There are only tables in the db and all owned
>> by
>> dbo, I am not sure if the developers created the tables and then changed
>> the
>> owner or specified on creation. I restored the db to sql2005sp1 and
>> setup
>> the logins via the sp_helprevlogins and now crap has hit the fan.
>>
>> 1) The domain group can login to SQL but when one of them clicks on the
>> user tables to expand they get the error:
>>
>> Failed to retrieve data for this request (Microsoft.sql.server.smoenum)
>> Additional Information:
>> Am exception occurred while executing a Transact-SQL statement or batch.
>> (Microsoft.sqlserver.connectioninfo)
>> Select permission denied on object 'extended properties', database
>> mssqlsystemresource', schema 'sys'.(Microsoft sql server, error 329)
>>
>> I know the one that is having the issue is the one that created the
>> tables.
>> Others in the domain group can see the tables.
>>
>> 2) Others in group that can see the table, when they try and create a new
>> table and get the following error:
>>
>> Property DefaultSchema is not available for the Database '[XXXXDB]'.
>> This
>> property may not exist for this object, or may not be retrievable due to
>> insufficient access rights. (sqleditors)
>>
>>
>> I guess I am missing something.
>>
>> Thanks
>> C
>>|||Hi
If there are no permissions granted directly to the group then you should
not have an issue dropping and re-creating it. The logins would not require
their own schemas. To create tables on the schemas they would require CREATE
TABLE permission on the database and ALTER permission on the schema. You
would require other permissions if you started to use XML schemas.
John
"CD" wrote:
> Thanks for the reply, no actually the group constist of 3 domain accts with
> the group being the database pre-defined dbo role, only one of them can not
> see the table objects. I know this one created the tables on the sql2000
> side and then changed the owner of the tables to dbo (error 1 note). The
> other 2 accounts can see all the tables but can not create a new one.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9A9CE67E-59B0-4A5B-A797-4F02FCCD9A7C@.microsoft.com...
> > Hi
> >
> > It sounds like your upgrade procedure has not given the users the correct
> > permissions. I would expect all users within the same domain group to have
> > the same problems, therefore Iif they get different problems they are
> > probably in different groups.
> >
> > Do you really want to grant such wide permissions to so many users? If you
> > created a database role with the minimum number of permissions you require
> > then the domain group could be added to that. In which case dropping the
> > domain group as a login and adding it again would not be an issue.
> >
> > You may want to use GRANT LOGIN when you add the groups rather than
> > sp_addlogin.
> >
> > John
> >
> > "CD" wrote:
> >
> >> I have sql2000 db that had two domain groups in the dbo role and one SQL
> >> acct also in the dbo role. There are only tables in the db and all owned
> >> by
> >> dbo, I am not sure if the developers created the tables and then changed
> >> the
> >> owner or specified on creation. I restored the db to sql2005sp1 and
> >> setup
> >> the logins via the sp_helprevlogins and now crap has hit the fan.
> >>
> >>
> >>
> >> 1) The domain group can login to SQL but when one of them clicks on the
> >> user tables to expand they get the error:
> >>
> >>
> >>
> >> Failed to retrieve data for this request (Microsoft.sql.server.smoenum)
> >>
> >> Additional Information:
> >>
> >> Am exception occurred while executing a Transact-SQL statement or batch.
> >>
> >> (Microsoft.sqlserver.connectioninfo)
> >>
> >> Select permission denied on object 'extended properties', database
> >> mssqlsystemresource', schema 'sys'.(Microsoft sql server, error 329)
> >>
> >>
> >>
> >> I know the one that is having the issue is the one that created the
> >> tables.
> >> Others in the domain group can see the tables.
> >>
> >>
> >>
> >> 2) Others in group that can see the table, when they try and create a new
> >> table and get the following error:
> >>
> >>
> >>
> >> Property DefaultSchema is not available for the Database '[XXXXDB]'.
> >> This
> >> property may not exist for this object, or may not be retrievable due to
> >> insufficient access rights. (sqleditors)
> >>
> >>
> >>
> >>
> >>
> >> I guess I am missing something.
> >>
> >>
> >>
> >> Thanks
> >>
> >> C
> >>
> >>
> >>
>
>|||I am feeling stupid here.
But I need to get the domain\DevTeam rights to create a table. They can
alter or delete an existing but not create they get the error:
Property DefaultSchema is not available for the Database '[XXXXDB]'. This
property may not exist for this object, or may not be retrievable due to
insufficient access rights. (sqleditors)
I tried at the DB permission level setting what you desribed but get the
same error.
What am I missing here?
1) I create the user database.
2) add the group to at the SQL/Security/Logins level
2a) then on the User Mapping, check the Map to the database on the top,
check the database role of db_owner at the bottom.
What else?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6BA2DFCA-32EF-42F5-89BB-ED26646AE4F4@.microsoft.com...
> Hi
> If there are no permissions granted directly to the group then you should
> not have an issue dropping and re-creating it. The logins would not
> require
> their own schemas. To create tables on the schemas they would require
> CREATE
> TABLE permission on the database and ALTER permission on the schema. You
> would require other permissions if you started to use XML schemas.
> John|||Hi
You can not define a default schema for a windows group and this seems to
stop you using the New Table wizard in Managment Studio. You will be able to
create tables from a query window and specify the schema as dbo, therefore
eliminating any need to change the schema later. Using a role, which has the
permissions I stated in the last post then a warning will appear when you
enter the new table dialog, but it will not create the tables in the dbo
schema.
John
"CD" wrote:
> I am feeling stupid here.
> But I need to get the domain\DevTeam rights to create a table. They can
> alter or delete an existing but not create they get the error:
> Property DefaultSchema is not available for the Database '[XXXXDB]'. This
> property may not exist for this object, or may not be retrievable due to
> insufficient access rights. (sqleditors)
> I tried at the DB permission level setting what you desribed but get the
> same error.
> What am I missing here?
> 1) I create the user database.
> 2) add the group to at the SQL/Security/Logins level
> 2a) then on the User Mapping, check the Map to the database on the top,
> check the database role of db_owner at the bottom.
> What else?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6BA2DFCA-32EF-42F5-89BB-ED26646AE4F4@.microsoft.com...
> > Hi
> >
> > If there are no permissions granted directly to the group then you should
> > not have an issue dropping and re-creating it. The logins would not
> > require
> > their own schemas. To create tables on the schemas they would require
> > CREATE
> > TABLE permission on the database and ALTER permission on the schema. You
> > would require other permissions if you started to use XML schemas.
> >
> > John
>
>
acct also in the dbo role. There are only tables in the db and all owned by
dbo, I am not sure if the developers created the tables and then changed the
owner or specified on creation. I restored the db to sql2005sp1 and setup
the logins via the sp_helprevlogins and now crap has hit the fan.
1) The domain group can login to SQL but when one of them clicks on the
user tables to expand they get the error:
Failed to retrieve data for this request (Microsoft.sql.server.smoenum)
Additional Information:
Am exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.sqlserver.connectioninfo)
Select permission denied on object 'extended properties', database
mssqlsystemresource', schema 'sys'.(Microsoft sql server, error 329)
I know the one that is having the issue is the one that created the tables.
Others in the domain group can see the tables.
2) Others in group that can see the table, when they try and create a new
table and get the following error:
Property DefaultSchema is not available for the Database '[XXXXDB]'. This
property may not exist for this object, or may not be retrievable due to
insufficient access rights. (sqleditors)
I guess I am missing something.
Thanks
CHi
It sounds like your upgrade procedure has not given the users the correct
permissions. I would expect all users within the same domain group to have
the same problems, therefore Iif they get different problems they are
probably in different groups.
Do you really want to grant such wide permissions to so many users? If you
created a database role with the minimum number of permissions you require
then the domain group could be added to that. In which case dropping the
domain group as a login and adding it again would not be an issue.
You may want to use GRANT LOGIN when you add the groups rather than
sp_addlogin.
John
"CD" wrote:
> I have sql2000 db that had two domain groups in the dbo role and one SQL
> acct also in the dbo role. There are only tables in the db and all owned by
> dbo, I am not sure if the developers created the tables and then changed the
> owner or specified on creation. I restored the db to sql2005sp1 and setup
> the logins via the sp_helprevlogins and now crap has hit the fan.
>
> 1) The domain group can login to SQL but when one of them clicks on the
> user tables to expand they get the error:
>
> Failed to retrieve data for this request (Microsoft.sql.server.smoenum)
> Additional Information:
> Am exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.sqlserver.connectioninfo)
> Select permission denied on object 'extended properties', database
> mssqlsystemresource', schema 'sys'.(Microsoft sql server, error 329)
>
> I know the one that is having the issue is the one that created the tables.
> Others in the domain group can see the tables.
>
> 2) Others in group that can see the table, when they try and create a new
> table and get the following error:
>
> Property DefaultSchema is not available for the Database '[XXXXDB]'. This
> property may not exist for this object, or may not be retrievable due to
> insufficient access rights. (sqleditors)
>
>
> I guess I am missing something.
>
> Thanks
> C
>
>|||Thanks for the reply, no actually the group constist of 3 domain accts with
the group being the database pre-defined dbo role, only one of them can not
see the table objects. I know this one created the tables on the sql2000
side and then changed the owner of the tables to dbo (error 1 note). The
other 2 accounts can see all the tables but can not create a new one.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9A9CE67E-59B0-4A5B-A797-4F02FCCD9A7C@.microsoft.com...
> Hi
> It sounds like your upgrade procedure has not given the users the correct
> permissions. I would expect all users within the same domain group to have
> the same problems, therefore Iif they get different problems they are
> probably in different groups.
> Do you really want to grant such wide permissions to so many users? If you
> created a database role with the minimum number of permissions you require
> then the domain group could be added to that. In which case dropping the
> domain group as a login and adding it again would not be an issue.
> You may want to use GRANT LOGIN when you add the groups rather than
> sp_addlogin.
> John
> "CD" wrote:
>> I have sql2000 db that had two domain groups in the dbo role and one SQL
>> acct also in the dbo role. There are only tables in the db and all owned
>> by
>> dbo, I am not sure if the developers created the tables and then changed
>> the
>> owner or specified on creation. I restored the db to sql2005sp1 and
>> setup
>> the logins via the sp_helprevlogins and now crap has hit the fan.
>>
>> 1) The domain group can login to SQL but when one of them clicks on the
>> user tables to expand they get the error:
>>
>> Failed to retrieve data for this request (Microsoft.sql.server.smoenum)
>> Additional Information:
>> Am exception occurred while executing a Transact-SQL statement or batch.
>> (Microsoft.sqlserver.connectioninfo)
>> Select permission denied on object 'extended properties', database
>> mssqlsystemresource', schema 'sys'.(Microsoft sql server, error 329)
>>
>> I know the one that is having the issue is the one that created the
>> tables.
>> Others in the domain group can see the tables.
>>
>> 2) Others in group that can see the table, when they try and create a new
>> table and get the following error:
>>
>> Property DefaultSchema is not available for the Database '[XXXXDB]'.
>> This
>> property may not exist for this object, or may not be retrievable due to
>> insufficient access rights. (sqleditors)
>>
>>
>> I guess I am missing something.
>>
>> Thanks
>> C
>>|||Hi
If there are no permissions granted directly to the group then you should
not have an issue dropping and re-creating it. The logins would not require
their own schemas. To create tables on the schemas they would require CREATE
TABLE permission on the database and ALTER permission on the schema. You
would require other permissions if you started to use XML schemas.
John
"CD" wrote:
> Thanks for the reply, no actually the group constist of 3 domain accts with
> the group being the database pre-defined dbo role, only one of them can not
> see the table objects. I know this one created the tables on the sql2000
> side and then changed the owner of the tables to dbo (error 1 note). The
> other 2 accounts can see all the tables but can not create a new one.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9A9CE67E-59B0-4A5B-A797-4F02FCCD9A7C@.microsoft.com...
> > Hi
> >
> > It sounds like your upgrade procedure has not given the users the correct
> > permissions. I would expect all users within the same domain group to have
> > the same problems, therefore Iif they get different problems they are
> > probably in different groups.
> >
> > Do you really want to grant such wide permissions to so many users? If you
> > created a database role with the minimum number of permissions you require
> > then the domain group could be added to that. In which case dropping the
> > domain group as a login and adding it again would not be an issue.
> >
> > You may want to use GRANT LOGIN when you add the groups rather than
> > sp_addlogin.
> >
> > John
> >
> > "CD" wrote:
> >
> >> I have sql2000 db that had two domain groups in the dbo role and one SQL
> >> acct also in the dbo role. There are only tables in the db and all owned
> >> by
> >> dbo, I am not sure if the developers created the tables and then changed
> >> the
> >> owner or specified on creation. I restored the db to sql2005sp1 and
> >> setup
> >> the logins via the sp_helprevlogins and now crap has hit the fan.
> >>
> >>
> >>
> >> 1) The domain group can login to SQL but when one of them clicks on the
> >> user tables to expand they get the error:
> >>
> >>
> >>
> >> Failed to retrieve data for this request (Microsoft.sql.server.smoenum)
> >>
> >> Additional Information:
> >>
> >> Am exception occurred while executing a Transact-SQL statement or batch.
> >>
> >> (Microsoft.sqlserver.connectioninfo)
> >>
> >> Select permission denied on object 'extended properties', database
> >> mssqlsystemresource', schema 'sys'.(Microsoft sql server, error 329)
> >>
> >>
> >>
> >> I know the one that is having the issue is the one that created the
> >> tables.
> >> Others in the domain group can see the tables.
> >>
> >>
> >>
> >> 2) Others in group that can see the table, when they try and create a new
> >> table and get the following error:
> >>
> >>
> >>
> >> Property DefaultSchema is not available for the Database '[XXXXDB]'.
> >> This
> >> property may not exist for this object, or may not be retrievable due to
> >> insufficient access rights. (sqleditors)
> >>
> >>
> >>
> >>
> >>
> >> I guess I am missing something.
> >>
> >>
> >>
> >> Thanks
> >>
> >> C
> >>
> >>
> >>
>
>|||I am feeling stupid here.
But I need to get the domain\DevTeam rights to create a table. They can
alter or delete an existing but not create they get the error:
Property DefaultSchema is not available for the Database '[XXXXDB]'. This
property may not exist for this object, or may not be retrievable due to
insufficient access rights. (sqleditors)
I tried at the DB permission level setting what you desribed but get the
same error.
What am I missing here?
1) I create the user database.
2) add the group to at the SQL/Security/Logins level
2a) then on the User Mapping, check the Map to the database on the top,
check the database role of db_owner at the bottom.
What else?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6BA2DFCA-32EF-42F5-89BB-ED26646AE4F4@.microsoft.com...
> Hi
> If there are no permissions granted directly to the group then you should
> not have an issue dropping and re-creating it. The logins would not
> require
> their own schemas. To create tables on the schemas they would require
> CREATE
> TABLE permission on the database and ALTER permission on the schema. You
> would require other permissions if you started to use XML schemas.
> John|||Hi
You can not define a default schema for a windows group and this seems to
stop you using the New Table wizard in Managment Studio. You will be able to
create tables from a query window and specify the schema as dbo, therefore
eliminating any need to change the schema later. Using a role, which has the
permissions I stated in the last post then a warning will appear when you
enter the new table dialog, but it will not create the tables in the dbo
schema.
John
"CD" wrote:
> I am feeling stupid here.
> But I need to get the domain\DevTeam rights to create a table. They can
> alter or delete an existing but not create they get the error:
> Property DefaultSchema is not available for the Database '[XXXXDB]'. This
> property may not exist for this object, or may not be retrievable due to
> insufficient access rights. (sqleditors)
> I tried at the DB permission level setting what you desribed but get the
> same error.
> What am I missing here?
> 1) I create the user database.
> 2) add the group to at the SQL/Security/Logins level
> 2a) then on the User Mapping, check the Map to the database on the top,
> check the database role of db_owner at the bottom.
> What else?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6BA2DFCA-32EF-42F5-89BB-ED26646AE4F4@.microsoft.com...
> > Hi
> >
> > If there are no permissions granted directly to the group then you should
> > not have an issue dropping and re-creating it. The logins would not
> > require
> > their own schemas. To create tables on the schemas they would require
> > CREATE
> > TABLE permission on the database and ALTER permission on the schema. You
> > would require other permissions if you started to use XML schemas.
> >
> > John
>
>
move db from sql2000 to 2005sp1
I have sql2000 db that had two domain groups in the dbo role and one SQL
acct also in the dbo role. There are only tables in the db and all owned by
dbo, I am not sure if the developers created the tables and then changed the
owner or specified on creation. I restored the db to sql2005sp1 and setup
the logins via the sp_helprevlogins and now crap has hit the fan.
1) The domain group can login to SQL but when one of them clicks on the
user tables to expand they get the error:
Failed to retrieve data for this request (Microsoft.sql.server.smoenum)
Additional Information:
Am exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.sqlserver.connectioninfo)
Select permission denied on object 'extended properties', database
mssqlsystemresource', schema 'sys'.(Microsoft sql server, error 329)
I know the one that is having the issue is the one that created the tables.
Others in the domain group can see the tables.
2) Others in group that can see the table, when they try and create a new
table and get the following error:
Property DefaultSchema is not available for the Database '[XXXXDB]'. Th
is
property may not exist for this object, or may not be retrievable due to
insufficient access rights. (sqleditors)
I guess I am missing something.
Thanks
CHi
It sounds like your upgrade procedure has not given the users the correct
permissions. I would expect all users within the same domain group to have
the same problems, therefore Iif they get different problems they are
probably in different groups.
Do you really want to grant such wide permissions to so many users? If you
created a database role with the minimum number of permissions you require
then the domain group could be added to that. In which case dropping the
domain group as a login and adding it again would not be an issue.
You may want to use GRANT LOGIN when you add the groups rather than
sp_addlogin.
John
"CD" wrote:
> I have sql2000 db that had two domain groups in the dbo role and one SQL
> acct also in the dbo role. There are only tables in the db and all owned
by
> dbo, I am not sure if the developers created the tables and then changed t
he
> owner or specified on creation. I restored the db to sql2005sp1 and setup
> the logins via the sp_helprevlogins and now crap has hit the fan.
>
> 1) The domain group can login to SQL but when one of them clicks on the
> user tables to expand they get the error:
>
> Failed to retrieve data for this request (Microsoft.sql.server.smoenum)
> Additional Information:
> Am exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.sqlserver.connectioninfo)
> Select permission denied on object 'extended properties', database
> mssqlsystemresource', schema 'sys'.(Microsoft sql server, error 329)
>
> I know the one that is having the issue is the one that created the tables
.
> Others in the domain group can see the tables.
>
> 2) Others in group that can see the table, when they try and create a new
> table and get the following error:
>
> Property DefaultSchema is not available for the Database '[XXXXDB]'.
This
> property may not exist for this object, or may not be retrievable due to
> insufficient access rights. (sqleditors)
>
>
> I guess I am missing something.
>
> Thanks
> C
>
>|||Thanks for the reply, no actually the group constist of 3 domain accts with
the group being the database pre-defined dbo role, only one of them can not
see the table objects. I know this one created the tables on the sql2000
side and then changed the owner of the tables to dbo (error 1 note). The
other 2 accounts can see all the tables but can not create a new one.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9A9CE67E-59B0-4A5B-A797-4F02FCCD9A7C@.microsoft.com...[vbcol=seagreen]
> Hi
> It sounds like your upgrade procedure has not given the users the correct
> permissions. I would expect all users within the same domain group to have
> the same problems, therefore Iif they get different problems they are
> probably in different groups.
> Do you really want to grant such wide permissions to so many users? If you
> created a database role with the minimum number of permissions you require
> then the domain group could be added to that. In which case dropping the
> domain group as a login and adding it again would not be an issue.
> You may want to use GRANT LOGIN when you add the groups rather than
> sp_addlogin.
> John
> "CD" wrote:
>|||Hi
If there are no permissions granted directly to the group then you should
not have an issue dropping and re-creating it. The logins would not require
their own schemas. To create tables on the schemas they would require CREATE
TABLE permission on the database and ALTER permission on the schema. You
would require other permissions if you started to use XML schemas.
John
"CD" wrote:
> Thanks for the reply, no actually the group constist of 3 domain accts wi
th
> the group being the database pre-defined dbo role, only one of them can n
ot
> see the table objects. I know this one created the tables on the sql2000
> side and then changed the owner of the tables to dbo (error 1 note). The
> other 2 accounts can see all the tables but can not create a new one.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9A9CE67E-59B0-4A5B-A797-4F02FCCD9A7C@.microsoft.com...
>
>|||I am feeling stupid here.
But I need to get the domain\DevTeam rights to create a table. They can
alter or delete an existing but not create they get the error:
Property DefaultSchema is not available for the Database '[XXXXDB]'. Th
is
property may not exist for this object, or may not be retrievable due to
insufficient access rights. (sqleditors)
I tried at the DB permission level setting what you desribed but get the
same error.
What am I missing here?
1) I create the user database.
2) add the group to at the SQL/Security/Logins level
2a) then on the User Mapping, check the Map to the database on the top,
check the database role of db_owner at the bottom.
What else?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6BA2DFCA-32EF-42F5-89BB-ED26646AE4F4@.microsoft.com...
> Hi
> If there are no permissions granted directly to the group then you should
> not have an issue dropping and re-creating it. The logins would not
> require
> their own schemas. To create tables on the schemas they would require
> CREATE
> TABLE permission on the database and ALTER permission on the schema. You
> would require other permissions if you started to use XML schemas.
> John|||Hi
You can not define a default schema for a windows group and this seems to
stop you using the New Table wizard in Managment Studio. You will be able to
create tables from a query window and specify the schema as dbo, therefore
eliminating any need to change the schema later. Using a role, which has the
permissions I stated in the last post then a warning will appear when you
enter the new table dialog, but it will not create the tables in the dbo
schema.
John
"CD" wrote:
> I am feeling stupid here.
> But I need to get the domain\DevTeam rights to create a table. They can
> alter or delete an existing but not create they get the error:
> Property DefaultSchema is not available for the Database '[XXXXDB]'.
This
> property may not exist for this object, or may not be retrievable due to
> insufficient access rights. (sqleditors)
> I tried at the DB permission level setting what you desribed but get the
> same error.
> What am I missing here?
> 1) I create the user database.
> 2) add the group to at the SQL/Security/Logins level
> 2a) then on the User Mapping, check the Map to the database on the top,
> check the database role of db_owner at the bottom.
> What else?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6BA2DFCA-32EF-42F5-89BB-ED26646AE4F4@.microsoft.com...
>
>
acct also in the dbo role. There are only tables in the db and all owned by
dbo, I am not sure if the developers created the tables and then changed the
owner or specified on creation. I restored the db to sql2005sp1 and setup
the logins via the sp_helprevlogins and now crap has hit the fan.
1) The domain group can login to SQL but when one of them clicks on the
user tables to expand they get the error:
Failed to retrieve data for this request (Microsoft.sql.server.smoenum)
Additional Information:
Am exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.sqlserver.connectioninfo)
Select permission denied on object 'extended properties', database
mssqlsystemresource', schema 'sys'.(Microsoft sql server, error 329)
I know the one that is having the issue is the one that created the tables.
Others in the domain group can see the tables.
2) Others in group that can see the table, when they try and create a new
table and get the following error:
Property DefaultSchema is not available for the Database '[XXXXDB]'. Th
is
property may not exist for this object, or may not be retrievable due to
insufficient access rights. (sqleditors)
I guess I am missing something.
Thanks
CHi
It sounds like your upgrade procedure has not given the users the correct
permissions. I would expect all users within the same domain group to have
the same problems, therefore Iif they get different problems they are
probably in different groups.
Do you really want to grant such wide permissions to so many users? If you
created a database role with the minimum number of permissions you require
then the domain group could be added to that. In which case dropping the
domain group as a login and adding it again would not be an issue.
You may want to use GRANT LOGIN when you add the groups rather than
sp_addlogin.
John
"CD" wrote:
> I have sql2000 db that had two domain groups in the dbo role and one SQL
> acct also in the dbo role. There are only tables in the db and all owned
by
> dbo, I am not sure if the developers created the tables and then changed t
he
> owner or specified on creation. I restored the db to sql2005sp1 and setup
> the logins via the sp_helprevlogins and now crap has hit the fan.
>
> 1) The domain group can login to SQL but when one of them clicks on the
> user tables to expand they get the error:
>
> Failed to retrieve data for this request (Microsoft.sql.server.smoenum)
> Additional Information:
> Am exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.sqlserver.connectioninfo)
> Select permission denied on object 'extended properties', database
> mssqlsystemresource', schema 'sys'.(Microsoft sql server, error 329)
>
> I know the one that is having the issue is the one that created the tables
.
> Others in the domain group can see the tables.
>
> 2) Others in group that can see the table, when they try and create a new
> table and get the following error:
>
> Property DefaultSchema is not available for the Database '[XXXXDB]'.
This
> property may not exist for this object, or may not be retrievable due to
> insufficient access rights. (sqleditors)
>
>
> I guess I am missing something.
>
> Thanks
> C
>
>|||Thanks for the reply, no actually the group constist of 3 domain accts with
the group being the database pre-defined dbo role, only one of them can not
see the table objects. I know this one created the tables on the sql2000
side and then changed the owner of the tables to dbo (error 1 note). The
other 2 accounts can see all the tables but can not create a new one.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9A9CE67E-59B0-4A5B-A797-4F02FCCD9A7C@.microsoft.com...[vbcol=seagreen]
> Hi
> It sounds like your upgrade procedure has not given the users the correct
> permissions. I would expect all users within the same domain group to have
> the same problems, therefore Iif they get different problems they are
> probably in different groups.
> Do you really want to grant such wide permissions to so many users? If you
> created a database role with the minimum number of permissions you require
> then the domain group could be added to that. In which case dropping the
> domain group as a login and adding it again would not be an issue.
> You may want to use GRANT LOGIN when you add the groups rather than
> sp_addlogin.
> John
> "CD" wrote:
>|||Hi
If there are no permissions granted directly to the group then you should
not have an issue dropping and re-creating it. The logins would not require
their own schemas. To create tables on the schemas they would require CREATE
TABLE permission on the database and ALTER permission on the schema. You
would require other permissions if you started to use XML schemas.
John
"CD" wrote:
> Thanks for the reply, no actually the group constist of 3 domain accts wi
th
> the group being the database pre-defined dbo role, only one of them can n
ot
> see the table objects. I know this one created the tables on the sql2000
> side and then changed the owner of the tables to dbo (error 1 note). The
> other 2 accounts can see all the tables but can not create a new one.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9A9CE67E-59B0-4A5B-A797-4F02FCCD9A7C@.microsoft.com...
>
>|||I am feeling stupid here.
But I need to get the domain\DevTeam rights to create a table. They can
alter or delete an existing but not create they get the error:
Property DefaultSchema is not available for the Database '[XXXXDB]'. Th
is
property may not exist for this object, or may not be retrievable due to
insufficient access rights. (sqleditors)
I tried at the DB permission level setting what you desribed but get the
same error.
What am I missing here?
1) I create the user database.
2) add the group to at the SQL/Security/Logins level
2a) then on the User Mapping, check the Map to the database on the top,
check the database role of db_owner at the bottom.
What else?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6BA2DFCA-32EF-42F5-89BB-ED26646AE4F4@.microsoft.com...
> Hi
> If there are no permissions granted directly to the group then you should
> not have an issue dropping and re-creating it. The logins would not
> require
> their own schemas. To create tables on the schemas they would require
> CREATE
> TABLE permission on the database and ALTER permission on the schema. You
> would require other permissions if you started to use XML schemas.
> John|||Hi
You can not define a default schema for a windows group and this seems to
stop you using the New Table wizard in Managment Studio. You will be able to
create tables from a query window and specify the schema as dbo, therefore
eliminating any need to change the schema later. Using a role, which has the
permissions I stated in the last post then a warning will appear when you
enter the new table dialog, but it will not create the tables in the dbo
schema.
John
"CD" wrote:
> I am feeling stupid here.
> But I need to get the domain\DevTeam rights to create a table. They can
> alter or delete an existing but not create they get the error:
> Property DefaultSchema is not available for the Database '[XXXXDB]'.
This
> property may not exist for this object, or may not be retrievable due to
> insufficient access rights. (sqleditors)
> I tried at the DB permission level setting what you desribed but get the
> same error.
> What am I missing here?
> 1) I create the user database.
> 2) add the group to at the SQL/Security/Logins level
> 2a) then on the User Mapping, check the Map to the database on the top,
> check the database role of db_owner at the bottom.
> What else?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6BA2DFCA-32EF-42F5-89BB-ED26646AE4F4@.microsoft.com...
>
>
Move DB by backup/restore or SP_Detach?
Question: Which method should I use to do the simultaneous change from SQL
2000 to 2005, and the change from an old PC (server) to a new one?
Current: SQL 2000 + Old Hardware
New (Future): SQL 2005 + New Hardware
What's the best way to get a single live database from the current to the
new? I expect to add users manually and point the client applications to
the new server. I've done the Upgrade Advisor and resolved a couple minor
things. For testing, I've taken a BAK file from the old server (sql 2000)
and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
2005 in the process, and a few test queries execute properly. So I'm
inclined to just do a backup and restore at that magic moment when we do the
upgrade. And then I'd add logins and jobs manually. Is there some
reason why SP_Detach & Attach is better? At least with a backup/restore
method, I can easily switch back to the old database if something doesn't
work. Plus, I can test this method many times before the big day, but I
can't easily test a detach of this database in advance. Thoughts?
Hi HK
You can use both forms obviously, the detach and attach would be quicker and
you can just take a copy of your data and log files where a backup and
restore will take a bit longer, which ever method you choose i would suggest
you rebuild all your indexes, stored procs, functions and views and then last
run update stats.
Regards
Charl
http://www.sqlserver.co.za
"HK" wrote:
> Question: Which method should I use to do the simultaneous change from SQL
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple minor
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do the
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>
|||How to move databases between computers that are running SQL Server
http://support.microsoft.com/default...b;en-us;314546
AMB
"HK" wrote:
> Question: Which method should I use to do the simultaneous change from SQL
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple minor
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do the
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>
|||Why would I have to do anything with stored procs?
Re-indexing doesn't seem required, but I can easily do that, and it is a
good idea anyway while I have the servers down. I don't have any functions
or views.
Can you elaborate on "update stats"?
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:FE5CFB19-0B91-48E4-A9C5-97BC0D419D03@.microsoft.com...
> Hi HK
> You can use both forms obviously, the detach and attach would be quicker
and
> you can just take a copy of your data and log files where a backup and
> restore will take a bit longer, which ever method you choose i would
suggest
> you rebuild all your indexes, stored procs, functions and views and then
last[vbcol=seagreen]
> run update stats.
> Regards
> Charl
> --
> http://www.sqlserver.co.za
>
> "HK" wrote:
SQL[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
minor[vbcol=seagreen]
2000)[vbcol=seagreen]
FILELISTONLY[vbcol=seagreen]
the[vbcol=seagreen]
backup/restore[vbcol=seagreen]
doesn't[vbcol=seagreen]
|||Hi HK
The reasoning behind this is based on personal experience when we moved a
SQL 2000 DB to SQL 2005, we ran into some performance issues when migrating,
we rebuilt all the objects and ran sp_updatestats to update the table
statistics again and it resolved our problems. MS have made changes to the
query optimizer and to ensure that your procs use the best possible execution
plan i would suggest it. SQL should be intelligent enough to do it but we did
it as a precausionary and it helped our implementation.
Regards
charl
http://www.sqlserver.co.za
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> How to move databases between computers that are running SQL Server
> http://support.microsoft.com/default...b;en-us;314546
>
> AMB
> "HK" wrote:
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:427477
You wrote that you rebuild all the objects. Which types of objects were
rebuilt besides indexes? Thanks.
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:7736D8DD-EEFC-4470-8EF8-D2F08BE8D96A@.microsoft.com...
> Hi HK
> The reasoning behind this is based on personal experience when we moved a
> SQL 2000 DB to SQL 2005, we ran into some performance issues when
migrating,
> we rebuilt all the objects and ran sp_updatestats to update the table
> statistics again and it resolved our problems. MS have made changes to the
> query optimizer and to ensure that your procs use the best possible
execution
> plan i would suggest it. SQL should be intelligent enough to do it but we
did[vbcol=seagreen]
> it as a precausionary and it helped our implementation.
> Regards
> charl
>
> --
> http://www.sqlserver.co.za
>
> "Alejandro Mesa" wrote:
from SQL[vbcol=seagreen]
the[vbcol=seagreen]
applications to[vbcol=seagreen]
minor[vbcol=seagreen]
2000)[vbcol=seagreen]
FILELISTONLY[vbcol=seagreen]
to[vbcol=seagreen]
I'm[vbcol=seagreen]
do the[vbcol=seagreen]
backup/restore[vbcol=seagreen]
doesn't[vbcol=seagreen]
I[vbcol=seagreen]
2000 to 2005, and the change from an old PC (server) to a new one?
Current: SQL 2000 + Old Hardware
New (Future): SQL 2005 + New Hardware
What's the best way to get a single live database from the current to the
new? I expect to add users manually and point the client applications to
the new server. I've done the Upgrade Advisor and resolved a couple minor
things. For testing, I've taken a BAK file from the old server (sql 2000)
and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
2005 in the process, and a few test queries execute properly. So I'm
inclined to just do a backup and restore at that magic moment when we do the
upgrade. And then I'd add logins and jobs manually. Is there some
reason why SP_Detach & Attach is better? At least with a backup/restore
method, I can easily switch back to the old database if something doesn't
work. Plus, I can test this method many times before the big day, but I
can't easily test a detach of this database in advance. Thoughts?
Hi HK
You can use both forms obviously, the detach and attach would be quicker and
you can just take a copy of your data and log files where a backup and
restore will take a bit longer, which ever method you choose i would suggest
you rebuild all your indexes, stored procs, functions and views and then last
run update stats.
Regards
Charl
http://www.sqlserver.co.za
"HK" wrote:
> Question: Which method should I use to do the simultaneous change from SQL
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple minor
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do the
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>
|||How to move databases between computers that are running SQL Server
http://support.microsoft.com/default...b;en-us;314546
AMB
"HK" wrote:
> Question: Which method should I use to do the simultaneous change from SQL
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple minor
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do the
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>
|||Why would I have to do anything with stored procs?
Re-indexing doesn't seem required, but I can easily do that, and it is a
good idea anyway while I have the servers down. I don't have any functions
or views.
Can you elaborate on "update stats"?
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:FE5CFB19-0B91-48E4-A9C5-97BC0D419D03@.microsoft.com...
> Hi HK
> You can use both forms obviously, the detach and attach would be quicker
and
> you can just take a copy of your data and log files where a backup and
> restore will take a bit longer, which ever method you choose i would
suggest
> you rebuild all your indexes, stored procs, functions and views and then
last[vbcol=seagreen]
> run update stats.
> Regards
> Charl
> --
> http://www.sqlserver.co.za
>
> "HK" wrote:
SQL[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
minor[vbcol=seagreen]
2000)[vbcol=seagreen]
FILELISTONLY[vbcol=seagreen]
the[vbcol=seagreen]
backup/restore[vbcol=seagreen]
doesn't[vbcol=seagreen]
|||Hi HK
The reasoning behind this is based on personal experience when we moved a
SQL 2000 DB to SQL 2005, we ran into some performance issues when migrating,
we rebuilt all the objects and ran sp_updatestats to update the table
statistics again and it resolved our problems. MS have made changes to the
query optimizer and to ensure that your procs use the best possible execution
plan i would suggest it. SQL should be intelligent enough to do it but we did
it as a precausionary and it helped our implementation.
Regards
charl
http://www.sqlserver.co.za
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> How to move databases between computers that are running SQL Server
> http://support.microsoft.com/default...b;en-us;314546
>
> AMB
> "HK" wrote:
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:427477
You wrote that you rebuild all the objects. Which types of objects were
rebuilt besides indexes? Thanks.
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:7736D8DD-EEFC-4470-8EF8-D2F08BE8D96A@.microsoft.com...
> Hi HK
> The reasoning behind this is based on personal experience when we moved a
> SQL 2000 DB to SQL 2005, we ran into some performance issues when
migrating,
> we rebuilt all the objects and ran sp_updatestats to update the table
> statistics again and it resolved our problems. MS have made changes to the
> query optimizer and to ensure that your procs use the best possible
execution
> plan i would suggest it. SQL should be intelligent enough to do it but we
did[vbcol=seagreen]
> it as a precausionary and it helped our implementation.
> Regards
> charl
>
> --
> http://www.sqlserver.co.za
>
> "Alejandro Mesa" wrote:
from SQL[vbcol=seagreen]
the[vbcol=seagreen]
applications to[vbcol=seagreen]
minor[vbcol=seagreen]
2000)[vbcol=seagreen]
FILELISTONLY[vbcol=seagreen]
to[vbcol=seagreen]
I'm[vbcol=seagreen]
do the[vbcol=seagreen]
backup/restore[vbcol=seagreen]
doesn't[vbcol=seagreen]
I[vbcol=seagreen]
Move DB by backup/restore or SP_Detach?
Question: Which method should I use to do the simultaneous change from SQL
2000 to 2005, and the change from an old PC (server) to a new one?
Current: SQL 2000 + Old Hardware
New (Future): SQL 2005 + New Hardware
What's the best way to get a single live database from the current to the
new? I expect to add users manually and point the client applications to
the new server. I've done the Upgrade Advisor and resolved a couple minor
things. For testing, I've taken a BAK file from the old server (sql 2000)
and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
2005 in the process, and a few test queries execute properly. So I'm
inclined to just do a backup and restore at that magic moment when we do the
upgrade. And then I'd add logins and jobs manually. Is there some
reason why SP_Detach & Attach is better? At least with a backup/restore
method, I can easily switch back to the old database if something doesn't
work. Plus, I can test this method many times before the big day, but I
can't easily test a detach of this database in advance. Thoughts?Hi HK
You can use both forms obviously, the detach and attach would be quicker and
you can just take a copy of your data and log files where a backup and
restore will take a bit longer, which ever method you choose i would sugges
t
you rebuild all your indexes, stored procs, functions and views and then las
t
run update stats.
Regards
Charl
http://www.sqlserver.co.za
"HK" wrote:
> Question: Which method should I use to do the simultaneous change from SQ
L
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple mino
r
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do t
he
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>|||How to move databases between computers that are running SQL Server
http://support.microsoft.com/defaul...kb;en-us;314546
AMB
"HK" wrote:
> Question: Which method should I use to do the simultaneous change from SQ
L
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple mino
r
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do t
he
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>|||Why would I have to do anything with stored procs?
Re-indexing doesn't seem required, but I can easily do that, and it is a
good idea anyway while I have the servers down. I don't have any functions
or views.
Can you elaborate on "update stats"?
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:FE5CFB19-0B91-48E4-A9C5-97BC0D419D03@.microsoft.com...
> Hi HK
> You can use both forms obviously, the detach and attach would be quicker
and
> you can just take a copy of your data and log files where a backup and
> restore will take a bit longer, which ever method you choose i would
suggest
> you rebuild all your indexes, stored procs, functions and views and then
last[vbcol=seagreen]
> run update stats.
> Regards
> Charl
> --
> http://www.sqlserver.co.za
>
> "HK" wrote:
>
SQL[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
minor[vbcol=seagreen]
2000)[vbcol=seagreen]
FILELISTONLY[vbcol=seagreen]
the[vbcol=seagreen]
backup/restore[vbcol=seagreen]
doesn't[vbcol=seagreen]|||Hi HK
The reasoning behind this is based on personal experience when we moved a
SQL 2000 DB to SQL 2005, we ran into some performance issues when migrating,
we rebuilt all the objects and ran sp_updatestats to update the table
statistics again and it resolved our problems. MS have made changes to the
query optimizer and to ensure that your procs use the best possible executio
n
plan i would suggest it. SQL should be intelligent enough to do it but we di
d
it as a precausionary and it helped our implementation.
Regards
charl
http://www.sqlserver.co.za
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> How to move databases between computers that are running SQL Server
> http://support.microsoft.com/defaul...kb;en-us;314546
>
> AMB
> "HK" wrote:
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:427477
You wrote that you rebuild all the objects. Which types of objects were
rebuilt besides indexes? Thanks.
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:7736D8DD-EEFC-4470-8EF8-D2F08BE8D96A@.microsoft.com...
> Hi HK
> The reasoning behind this is based on personal experience when we moved a
> SQL 2000 DB to SQL 2005, we ran into some performance issues when
migrating,
> we rebuilt all the objects and ran sp_updatestats to update the table
> statistics again and it resolved our problems. MS have made changes to the
> query optimizer and to ensure that your procs use the best possible
execution
> plan i would suggest it. SQL should be intelligent enough to do it but we
did[vbcol=seagreen]
> it as a precausionary and it helped our implementation.
> Regards
> charl
>
> --
> http://www.sqlserver.co.za
>
> "Alejandro Mesa" wrote:
>
from SQL[vbcol=seagreen]
the[vbcol=seagreen]
applications to[vbcol=seagreen]
minor[vbcol=seagreen]
2000)[vbcol=seagreen]
FILELISTONLY[vbcol=seagreen]
to[vbcol=seagreen]
I'm[vbcol=seagreen]
do the[vbcol=seagreen]
backup/restore[vbcol=seagreen]
doesn't[vbcol=seagreen]
I[vbcol=seagreen]
2000 to 2005, and the change from an old PC (server) to a new one?
Current: SQL 2000 + Old Hardware
New (Future): SQL 2005 + New Hardware
What's the best way to get a single live database from the current to the
new? I expect to add users manually and point the client applications to
the new server. I've done the Upgrade Advisor and resolved a couple minor
things. For testing, I've taken a BAK file from the old server (sql 2000)
and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
2005 in the process, and a few test queries execute properly. So I'm
inclined to just do a backup and restore at that magic moment when we do the
upgrade. And then I'd add logins and jobs manually. Is there some
reason why SP_Detach & Attach is better? At least with a backup/restore
method, I can easily switch back to the old database if something doesn't
work. Plus, I can test this method many times before the big day, but I
can't easily test a detach of this database in advance. Thoughts?Hi HK
You can use both forms obviously, the detach and attach would be quicker and
you can just take a copy of your data and log files where a backup and
restore will take a bit longer, which ever method you choose i would sugges
t
you rebuild all your indexes, stored procs, functions and views and then las
t
run update stats.
Regards
Charl
http://www.sqlserver.co.za
"HK" wrote:
> Question: Which method should I use to do the simultaneous change from SQ
L
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple mino
r
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do t
he
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>|||How to move databases between computers that are running SQL Server
http://support.microsoft.com/defaul...kb;en-us;314546
AMB
"HK" wrote:
> Question: Which method should I use to do the simultaneous change from SQ
L
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple mino
r
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do t
he
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>|||Why would I have to do anything with stored procs?
Re-indexing doesn't seem required, but I can easily do that, and it is a
good idea anyway while I have the servers down. I don't have any functions
or views.
Can you elaborate on "update stats"?
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:FE5CFB19-0B91-48E4-A9C5-97BC0D419D03@.microsoft.com...
> Hi HK
> You can use both forms obviously, the detach and attach would be quicker
and
> you can just take a copy of your data and log files where a backup and
> restore will take a bit longer, which ever method you choose i would
suggest
> you rebuild all your indexes, stored procs, functions and views and then
last[vbcol=seagreen]
> run update stats.
> Regards
> Charl
> --
> http://www.sqlserver.co.za
>
> "HK" wrote:
>
SQL[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
minor[vbcol=seagreen]
2000)[vbcol=seagreen]
FILELISTONLY[vbcol=seagreen]
the[vbcol=seagreen]
backup/restore[vbcol=seagreen]
doesn't[vbcol=seagreen]|||Hi HK
The reasoning behind this is based on personal experience when we moved a
SQL 2000 DB to SQL 2005, we ran into some performance issues when migrating,
we rebuilt all the objects and ran sp_updatestats to update the table
statistics again and it resolved our problems. MS have made changes to the
query optimizer and to ensure that your procs use the best possible executio
n
plan i would suggest it. SQL should be intelligent enough to do it but we di
d
it as a precausionary and it helped our implementation.
Regards
charl
http://www.sqlserver.co.za
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> How to move databases between computers that are running SQL Server
> http://support.microsoft.com/defaul...kb;en-us;314546
>
> AMB
> "HK" wrote:
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:427477
You wrote that you rebuild all the objects. Which types of objects were
rebuilt besides indexes? Thanks.
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:7736D8DD-EEFC-4470-8EF8-D2F08BE8D96A@.microsoft.com...
> Hi HK
> The reasoning behind this is based on personal experience when we moved a
> SQL 2000 DB to SQL 2005, we ran into some performance issues when
migrating,
> we rebuilt all the objects and ran sp_updatestats to update the table
> statistics again and it resolved our problems. MS have made changes to the
> query optimizer and to ensure that your procs use the best possible
execution
> plan i would suggest it. SQL should be intelligent enough to do it but we
did[vbcol=seagreen]
> it as a precausionary and it helped our implementation.
> Regards
> charl
>
> --
> http://www.sqlserver.co.za
>
> "Alejandro Mesa" wrote:
>
from SQL[vbcol=seagreen]
the[vbcol=seagreen]
applications to[vbcol=seagreen]
minor[vbcol=seagreen]
2000)[vbcol=seagreen]
FILELISTONLY[vbcol=seagreen]
to[vbcol=seagreen]
I'm[vbcol=seagreen]
do the[vbcol=seagreen]
backup/restore[vbcol=seagreen]
doesn't[vbcol=seagreen]
I[vbcol=seagreen]
Subscribe to:
Posts (Atom)