Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

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

Hi,

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

Thanks.
0
jjoz
Asked:
jjoz
  • 5
  • 4
  • 2
  • +3
4 Solutions
 
kamindaCommented:
Hi,

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.
0
 
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 ?
0
 
mayank_joshiCommented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 ?
0
 
mayank_joshiCommented:
backup encryption is supported from SQL Server 2008 onwards
0
 
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
0
 
jjozAuthor Commented:
ok, so in this case the SQL Server 2000 doesn't have any encryption or even simple password protection ?
0
 
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.
0
 
Alpesh PatelAssistant ConsultantCommented:
No Attach -detach Best way is the Backup and restore because your are going to upgrade to 2008
0
 
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.
0
 
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 ?
0
 
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 ;)
0
 
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.
0
 
jjozAuthor Commented:
many thanks for the suggestion.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now