3

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.

  • 1
    sqlcmd to run your backup script – swasheck Sep 19 '14 at 14:54
  • In addition, if you have SSMS installed on your desktop (and you have sufficient rights) you can issue the command from your desktop. – RLF Sep 19 '14 at 14:58
  • I would second running the backup via ssms on your desktop unless you can't for some reason. – Patrick Sep 12 '18 at 2:57
3

You can use sqlcmd from the command prompt.

It's default location for SQL 2012 is here :- C:\Program Files\Microsoft SQL Server\110\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

  • Needs moar PowerShell – billinkc Sep 19 '14 at 15:05
  • Sorry, do you mean you want to run it in powershell? – dbafromthecold Sep 19 '14 at 15:07
  • Just being obnoxious. ;) I'd have gone sqlcmd route myself but was just pointing out the capability and infatuation with PS many DBAs have – billinkc Sep 19 '14 at 15:11
  • PS is the way to go, Backup-SqlDatabase -ServerInstance servername -Database databasename – Bob Klimes Sep 11 '18 at 20:40
  • 2005 doesn't like Backup-SqlDatabase : Backup to URL device is not supported on server version 9.0.1 – Chuck D Nov 5 '18 at 17:47
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.