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.
2 Answers
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.
answered Sep 19, 2014 at 14:58
-
-
Sorry, do you mean you want to run it in powershell? Commented Sep 19, 2014 at 15:07
-
Just being obnoxious. ;) I'd have gone
sqlcmdroute myself but was just pointing out the capability and infatuation with PS many DBAs have– billinkcCommented Sep 19, 2014 at 15:11 -
PS is the way to go, Backup-SqlDatabase -ServerInstance servername -Database databasename Commented Sep 11, 2018 at 20:40
-
2005 doesn't like Backup-SqlDatabase : Backup to URL device is not supported on server version 9.0.1– Chuck DCommented Nov 5, 2018 at 17:47
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
sqlcmdto run your backup script