How to SAVE/COPY DB to a CD Rom

[JohnBates]

Hi experts,

I would like to save or copy a SQL Server 2000 database to a CD, and then restore or copy the database to another computer that also has SQL 2000.

Will the Enterprise Manager Save DB/Restore Db work? Can I do this with other SQL Server tools ?

Thanks for any advice. John

 

[nigelrivett]

Backup the database to disk, copy the nackup file to CD.
Reverse the precess at the other end.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[JohnBates]

Ok I was finally able to do the restore..

when I simply tried to restore the .BAK that was on the CD-ROM, I got an 'Activation Error'.

The solution was to: 1. Create a new db of the same name that I am restoring

2. Specify 'Force Restore Over existing database'

Then the Restore from Device worked.


Maybe this will help someone else.
John

 

[osjohnm]

Another thing to note is that you should run sp_change_users_login for the database like so:

exec sp_change_users_login 'report'

this will return a list of users in your database that don't matchup to a login on the new server.

you might have to first create the missing login(s) on the server. the database that you "copied" across stores the sids that are created on the old server in its sysusers table.

if you run exec sp_change_users_login 'update_one', 'john', 'john'
sql server will merge the two and update the sids.

BOL contains more info on sp_change_users_login.

I would also recommend that you run sp_changedbowner 'sa'
against the database.

Hope this helps