Export a database - keep Identity

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Export a database - keep Identity

Author

Topic

djhmateer
Starting Member

4 Posts

Posted - 2004-12-14 : 16:42:23

Hi

SQL Server 2000, Enterprise Manager:

All Tasks, Export Data, Copy Tables and Views from Source Database, Create a New Destination Database, Select All (tables), Run Immediately.

The Identity is not exported over to the new destination database.

Any ideas how I can keep my identity?

Dave.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-14 : 16:50:40

Do not use the wizard to transfer a database. Use backup/restore or sp_detach_db/sp_attach_db.

Tara

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-12-14 : 17:41:24

C'mon Tara, you could be a little more helpful, even though you are doing this for free. I think you're becoming the queen of the one-liner.

djhmateer, try using the option to "Copy objects and Data between SQL Server Database" instead of the default Copy Table and Views...

-----------------------------------------
Professional IT Solutions from Infoneering

djhmateer
Starting Member

4 Posts

Posted - 2004-12-14 : 19:30:01

Hi tduggan and AjarnMark

Thanks for the replies...

I fixxed the problem with a Copy Objects and Data... then had to use:

sp_MSForEachTable 'sp_changeobjectowner ''?'',''dbo'''

to change the owner over to dbo (it was a remote db I was copying)..

Cheers

Dave.

Tarr - My 2 liner -

Q: What is brown and sticky?

A: A stick.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-12-14 : 19:43:59

Only if it's from a maple tree or you used it for something......anyway.

??? Wasn't the owner dbo on the remote database? If so, you need to change your scripting options so it catches the owner name. Go to Query Analyzer and change it in the options. You then won't have to do this extra step.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

djhmateer
Starting Member

4 Posts

Posted - 2004-12-15 : 18:20:03

Thanks MeanOldDBA..

Dave.

No matter what happens if this life, never let it spoil you dinner!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources