how to take server database backup with data in sql server 2005 [closed]

Question

My data base is in US Server and i want to take the database backup with data using sql server 2005 or sql wizard is it possible. and after that i want to store the database in my local system

Answer 1

Please see the documentation: How to: Back Up a Database (SQL Server Management Studio)

Basic backup-restore in SSMS:

Right click database in the Object Explorer and click Tasks -> Backup

Set options in the Backup Database dialog:

In the wanted instance, right click Databases node in the Object Explorer pane and select Restore Database:

Browse to your backup file and click OK:

Hope this helps

Answer 2

Just take a regular backup :

backup database database_name to disk = 'D:\backups\database_name.bak'
with init, stats =10

and then move that backup to your local machine and restore it

restore database database_name from disk = 'D:\backups\database_name.bak'
with stats =10, recovery,
move 'logical_file_data' to 'physical file location_data.mdf'
move 'logical_file_log' to 'physical file location_log.ldf'

You can even zip the backup or compress using third party tools as well.

Caution: Unless the database backup does not contain any sensitive data and it does not violate your client or company's agreements, you should be fine. Always de-sensitize the data before you start playing with it.