Ever tried to back up an SQL Server 2005 / 2008 / 2008 R2 or 2012 Database and found that you have to stop the service? We have been trying for years to back up our databases, but every time we have to stop and re-start the sqlserver service, until now.
Its really simple to backup AND restore a database using the command line. Note that you have to store the database on a drive, not a UNC path or mapped share.
C:\>SqlCmd -E -S ServerName –Q “BACKUP DATABASE NameOfDatabase TO DISK=’C:\backup\NameOfDatabase.bak’”
Whats great about this is that you can create a batch file that not only does this, but then FTP’s the backup file directly to another server, so that you have multiple backups.
SqlCmd -E -S ServerName –Q “RESTORE DATABASE NameOfDatabase FROM DISK=’C:\backup\NameOfDatabase.bak’”
An important thing to remember when using the restore command is that it is meant to be used on the same SQL Server that the backup file was created on.
SQL backup files store file location information that control where and how the data files in the backup file are copied. If you are restoring a backup from a different SQL Server, the path locations in the backup file may not match the server you are restoring to and an error will result. While this can be worked around, it is much easier to restore backups created on another SQL Server using the SQL Management Studio tool.