prod data to test server

I need to do some testing on our test servers but with production data. What
is the best way to get all of the production data into my test database? I'm
going from a SQL 2000 database in production to a SQL 2005 database in test.
Would the import data wizard work or a 'backup of the 2000 database and
restore to SQL 2005 database work?
Which would be the best way to do this?
Mike
Hi
On Jun 18, 3:41 pm, "Mike" <M...@.community.nospam.com> wrote:
> I need to do some testing on our test servers but with production data. What
> is the best way to get all of the production data into my test database? I'm
> going from a SQL 2000 database in production to a SQL 2005 database in test.
> Would the import data wizard work or a 'backup of the 2000 database and
> restore to SQL 2005 database work?
> Which would be the best way to do this?
> Mike
You can also use sp_detach_db/sp_attach_db as well see
http://support.microsoft.com/kb/314546, you would need to make sure
that you have the correct logins and the users are not orphaned. The
quickest methods tend to be backup/restore or sp_detach_db/
sp_attach_db. SQL 2000 database can be attached/restored to SQL 2005
but not vice versa.
Watch out for data protection issues!!
John
|||I can't do that due to we need both SQl 2000 and SQL 05 running on the same
test box. None of the apps can go down, even in test. So I have both SQL
versions running on the same box in our test environment. Though once all of
the databases are tested that is the way I'm looking to convert the
production database server over to 05, the detach and attach method, but
first I need to test and get all of the prod data (data only) to my test
server.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1182178695.604642.31080@.j4g2000prf.googlegrou ps.com...
> Hi
> On Jun 18, 3:41 pm, "Mike" <M...@.community.nospam.com> wrote:
> You can also use sp_detach_db/sp_attach_db as well see
> http://support.microsoft.com/kb/314546, you would need to make sure
> that you have the correct logins and the users are not orphaned. The
> quickest methods tend to be backup/restore or sp_detach_db/
> sp_attach_db. SQL 2000 database can be attached/restored to SQL 2005
> but not vice versa.
> Watch out for data protection issues!!
> John
>
|||Hi
On Jun 18, 4:05 pm, "Mike" <M...@.community.nospam.com> wrote:
> I can't do that due to we need both SQl 2000 and SQL 05 running on the same
> test box. None of the apps can go down, even in test. So I have both SQL
> versions running on the same box in our test environment. Though once all of
> the databases are tested that is the way I'm looking to convert the
> production database server over to 05, the detach and attach method, but
> first I need to test and get all of the prod data (data only) to my test
> server.
> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> news:1182178695.604642.31080@.j4g2000prf.googlegrou ps.com...
>
>
>
>
> - Show quoted text -
But you can still use backup/restore which require no downtime of the
production system. The KB shows all three methods.
John
|||I'm in the process of backup/restore method now.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1182184181.666277.268590@.n15g2000prd.googlegr oups.com...
> Hi
> On Jun 18, 4:05 pm, "Mike" <M...@.community.nospam.com> wrote:
> But you can still use backup/restore which require no downtime of the
> production system. The KB shows all three methods.
> John
>
|||If you just need to sync small amounts of test/prod data, consider ApexSQL's
Diff product.
TheSQLGuru
President
Indicium Resources, Inc.
"Mike" <Mike@.community.nospam.com> wrote in message
news:%236r99absHHA.1060@.TK2MSFTNGP06.phx.gbl...
>I need to do some testing on our test servers but with production data.
>What is the best way to get all of the production data into my test
>database? I'm going from a SQL 2000 database in production to a SQL 2005
>database in test.
> Would the import data wizard work or a 'backup of the 2000 database and
> restore to SQL 2005 database work?
> Which would be the best way to do this?
> Mike
>
>