Backup and Restore DB using DOS Batch command

Hi Everyone:

How could I stop the DB service, backup DB and restart the SQL Server 2005 in Batch command? My intention is to automate the process of backup of a DB to a remote location. Is there any better way to accomplish this task?

Thank in advance.

Regards,
LS

Solution: Backup and Restore DB using DOS Batch command

I wouldn't stop de SQL Server service because than you wouldn't be able to execute any queries against it. You can use the following code to create a backup.

The osql.exe is replaced with "sqlcmd" in SQL Server 2005

The only reason why you should stop the SQL Service is when you just want to make a copy backup using the "COPY" or "XCOPY" command in de command line.

More information on the command:

osql.exe: http://msdn.microsoft.com/en-us/library/aa213090(SQL.80).aspx
sqlcmd: http://msdn.microsoft.com/en-us/library/ms162773.aspx

                       
             "{sqlserverinstall}\80\Tools\Binn\osql.exe" -E -Q "BACKUP DATABASE {databasename} TO DISK='{location to copy to}' WITH FORMAT"

Open in New Window

Select All