What is the proper and best way of exporting live SQL DB ?


I need to copy SQL Server database instance into another server off-site quickly, what is my best option ? is it using Database backup while the server is still online ?

I will need to have stored procedure, logins and all objects that is associated with this particular DB instance.

and also what is the encryption strength if it is possible ?

Note: I'm using SQL Server 2000 Enterprise and would like to migrate this into SQL Server 2008

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Backup and restore is the best method for me but alternativly you can use Dettach and Attach option which will take lesser time when compare with backup restore.

You will have to move master database as well to move logins.
jjozAuthor Commented:
ah ok many thanks, so how about the Export / Import option ? I'm confused with the backup/restore against Export/import.

and also is there any way to password protect the export archive ? what's the encryption strength ?
Export/Import is used for copying  only data and not the entire schema.Using export/import tables can be copied but without any keys or constraints.Exporting or importing views converts them into tables.
Procedures,Functions,Triggers  and all other objects except tables and views can not be copied with export/import.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

jjozAuthor Commented:
ah ok, so in this case then backup and restore is the only way to do for complete exact copy replica.

Can I encrypt the backup / restore in SQL Server 2000 ? I cannot find any way to encrypt it somehow ?
backup encryption is supported from SQL Server 2008 onwards
Daniel_PLDB Expert/ArchitectCommented:
In SQL Server 2008 you have Copy Database Wizard which you can use to just copy your database (all data in it) from SQL Server 2000 to your SQL Server 2008 instance.
Bunch of links:
Using the Copy Database Wizard
How to: Upgrade SQL Server with the Copy Database Wizard
Upgrading to SQL Server 2008 using Copy Database Wizard
The SQL Server Copy Database Wizard

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jjozAuthor Commented:
ok, so in this case the SQL Server 2000 doesn't have any encryption or even simple password protection ?
Daniel_PLDB Expert/ArchitectCommented:
You can use backup password protection:
BACKUP DATABASE AdventureWorks TO DISK = 'D:\Backup\adv_bckp.bak' WITH PASSWORD = 'P@ssw0rd!'

Open in new window

Password will not encrypt your data, it only prevents (quite weak protection) from restoring backup without password.
Alpesh PatelAssistant ConsultantCommented:
No Attach -detach Best way is the Backup and restore because your are going to upgrade to 2008
Daniel_PLDB Expert/ArchitectCommented:
Both methods allows upgrade to higher version of SQL Server, it's not that point. Backup/restore method is safer and can be performed online. Attach/detach method requires database/server inactivity (it depends on how you want to move database files). Attach/detach method may be faster than backup.
I'd suggest backup/restore method which is safer, also you can use database copy wizard and copy database over to your another instance.
jjozAuthor Commented:
@Daniel: so how strong is the encryption ? is it 64 bit or 256 bit ?
I'll do the backup and restore with the T-SQL you provided above.

what's the difference between using that T-SQL and the right click backup method ?
Daniel_PLDB Expert/ArchitectCommented:
@Daniel: so how strong is the encryption ? is it 64 bit or 256 bit ?
There is no encryption at all, contents of backup are not encrypted, only restore is password protected.

what's the difference between using that T-SQL and the right click backup method ?
No differencies, SQL Server issues T-SQL uder scenes ;)
Anthony PerkinsCommented:
>>what's the difference between using that T-SQL and the right click backup method ? <<
With the first you can use all the backup options.  The second is only intended for beginners and a limited functionality.
jjozAuthor Commented:
many thanks for the suggestion.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.