SQL Server command line backup statement

Question

Does any one know if there is a way to script out SQL Server backup in to a batch file, so that it could be executed from a command line?

Answer 1

Here's an example you can run as a batch script (copy-paste into a .bat file), using the SQLCMD utility in Sql Server client tools:

BACKUP:

echo off cls echo -- BACKUP DATABASE -- set /p DATABASENAME=Enter database name:  :: filename format Name-Date (eg MyDatabase-2009.5.19.bak) set DATESTAMP=%DATE:~-4%.%DATE:~7,2%.%DATE:~4,2% set BACKUPFILENAME=%CD%\%DATABASENAME%-%DATESTAMP%.bak set SERVERNAME=your server name here echo.  sqlcmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%DATABASENAME%] TO DISK = N'%BACKUPFILENAME%' WITH INIT , NOUNLOAD , NAME = N'%DATABASENAME% backup', NOSKIP , STATS = 10, NOFORMAT" echo. pause 

RESTORE:

echo off cls echo -- RESTORE DATABASE -- set /p BACKUPFILENAME=Enter backup file name:%CD%\ set /p DATABASENAME=Enter database name: set SERVERNAME=your server name here sqlcmd -E -S %SERVERNAME% -d master -Q "ALTER DATABASE [%DATABASENAME%] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"  :: WARNING - delete the database, suits me :: sqlcmd -E -S %SERVERNAME% -d master -Q "IF EXISTS (SELECT * FROM sysdatabases WHERE name=N'%DATABASENAME%' ) DROP DATABASE [%DATABASENAME%]" :: sqlcmd -E -S %SERVERNAME% -d master -Q "CREATE DATABASE [%DATABASENAME%]"  :: restore sqlcmd -E -S %SERVERNAME% -d master -Q "RESTORE DATABASE [%DATABASENAME%] FROM DISK = N'%CD%\%BACKUPFILENAME%' WITH REPLACE"  :: remap user/login (http://msdn.microsoft.com/en-us/library/ms174378.aspx) sqlcmd -E -S %SERVERNAME% -d %DATABASENAME% -Q "sp_change_users_login 'Update_One', 'login-name', 'user-name'" sqlcmd -E -S %SERVERNAME% -d master -Q "ALTER DATABASE [%DATABASENAME%] SET MULTI_USER" echo. pause