Backup without using SQL Server Management Studio

Question

I am trying to take a backup on a Windows Server 2003 and SQL Server 2005 installed on it. But, there is no SQL Server Management Studio on it. How can I take full backup of a particular database.

• swasheck: sqlcmd to run your backup script
• RLF: In addition, if you have SSMS installed on your desktop (and you have sufficient rights) you can issue the command from your desktop.
• Patrick: I would second running the backup via ssms on your desktop unless you can't for some reason.

Answer 1

You can use sqlcmd from the command prompt.

It's default location for SQL 2012 is here :- C:\Program Files\Microsoft SQL Server110円\Tools\Binn

For windows authentication run :- sqlcmd -SSERVERNAME

or for sql authentication run:- sqlcmd -SSERVERNAME -ULogin -PPassword

You can then run a backup sql statement.

http://msdn.microsoft.com/en-us/library/ms180944.aspx

• billinkc: Needs moar PowerShell
• dbafromthecold: Sorry, do you mean you want to run it in powershell?
• billinkc: Just being obnoxious. ;) I'd have gone sqlcmd route myself but was just pointing out the capability and infatuation with PS many DBAs have
• Bob Klimes: PS is the way to go, Backup-SqlDatabase -ServerInstance servername -Database databasename
• Chuck D: 2005 doesn't like Backup-SqlDatabase : Backup to URL device is not supported on server version 9.0.1

Answer 2

Try this in command promt

In winodws authentication mode

SQLCMD -E -S SERVERNAME -Q "BACKUP DATABASE DatabaseName TO DISK='D:\Backup\DatabaseName.bak'"

In Sql Authentication mode

SQLCMD -S SERVERNAME -U sqlUser -P Password 

then run the BACKUP DATABASE DatabaseName TO DISK='D:\Backup\DatabaseName.bak' in next step