Using SQL Server Management Studio Express to Backup DB

Question

Hi,

I am wanting to backup a SQl Server 2005 db at a remote site (ie. to ultimately have a backup file of the db here locally on my machine).

Can this be done using SQL Server Management Studio Express ? I have this installed and running.. but cannot seem to find a way of backing up using it. If this isn't possible, how do I create a backup of my remote db some other way ?

Thank you,

Bazza

Answer 1

It should be possible, right click on the database, select Tasks->Backup.

The other good option is the bcp command line utility. If your backup needs to be done regularly it's a better option since you can use it in a bacth file or script and create a scehduler task for it.

Answer 2

I'm not sure, however if you have remote access to the system concerned you should be able to run a SQLCMD and issue the backup from the command line.

1) create a script called say backup.sql

USE [master]
GO


BACKUP DATABASE [somedatabase] 
TO  DISK = N'C:\somedatabase.bak' 
WITH NOFORMAT, 
INIT,  
NAME = N'Full Database Backup of somedatabase', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

2) issue:

sqlcmd -U username -P password -i backup.sql

Then copy the file over.

Answer 3

Unfortunately, I can't answer your specific question regarding SSMS Express - I've not used the tool extensively. However, I do know that you can open a New Query window and issue a BACKUP DATABASE command. An example of this would be:

backup database <dbname,,> to disk='c:\mydbbackup.bak';

You could then use standard methods (such as FTP) to get the file copied locally. Hope that helps!

Answer 4

This works in SSMS 2008 Express:

Right-click the database name in object explorer > Tasks > Backup…

Select "Full" (should already be default), enter a name, and at the bottom click "Add" and create the file to which you want to back up.

Run the backup.

Copy the file to your local machine.

Connected to the local machine, select the "Databases" node in Object Explorer > Restore Database…

Now name the new database, select "from device" and choose the backup file from which to create the new database, and go.

Done.