SEARCH

How-To Geek

Backup and Restore Your SQL Server Database from the Command Line

The most important part of a SQL Server maintenance plan is backing up your databases regularly. To backup a database, you cannot simply copy the respective MDF and LDF files of the database as SQL Server has a lock on these. Instead, you need to create a true backup file through SQL Server.

While this can be done by developing a Maintenance Plan inside of SQL Management Studio, the free Express editions of SQL Server do not offer this interface. To work around this, you can easily backup your databases by running the command below while logged in as a Windows Administrator:

SqlCmd -E -S Server_Name –Q “BACKUP DATABASE [Name_of_Database] TO DISK=’X:PathToBackupLocation[Name_of_Database].bak'”

The examples below will help.

Default SQL Server instance:

SqlCmd -E -S MyServer –Q “BACKUP DATABASE [MyDB] TO DISK=’D:BackupsMyDB.bak'”

Named SQL Server instance:

SqlCmd -E -S MyServerMyInstance –Q “BACKUP DATABASE [MyDB] TO DISK=’D:BackupsMyDB.bak'”

The above create a fully restorable backup copy of “MyDB” to the file “D:BackupsMyDB.bak” which can be used for disaster recovery. Of course, you can change the backup location and file to whatever you need, but make sure you specify a folder location which exists on the local machine. This backup file can then be copied to a tape drive or another external backup location.

A common question is “Can a backup file be created to a mapped drive or UNC location?” and the quick answer is no. The reason is because the SQL Server Windows Service runs as a user account which only has access to the local machine. You could change the account the service runs as, but this is highly discouraged for security reasons.

Restoring a Database Backup from the Command Line

To restore a database from a backup file, simply use the command:

SqlCmd -E -S Server_Name –Q “RESTORE DATABASE [Name_of_Database] FROM DISK=’X:PathToBackupFile[File_Name].bak'”

For example:

SqlCmd -E -S MyServer –Q “RESTORE DATABASE [MyDB] FROM DISK=’D:BackupsMyDB.bak'”

The above command will restore a backup of “MyDB” from the data stored in the backup file “D:BackupsMyDB.bak”. Any changes made to MyDB since the backup file was created will be lost.

An important thing to remember when using the above command is that it is intended to be used on the same SQL Server that the respective backup file was created on. SQL backup files store ‘behind the scenes’ 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.

Note: the commands above will work on SQL 2005 and higher (any edition). For SQL 2000 and earlier, replace ‘SqlCmd’ with ‘oSql’.

Jason Faulkner is a developer and IT professional who never has a hot cup of coffee far away. Interact with him on Google+

  • Published 07/29/10

Comments (4)

  1. Nige

    With this syntax is there any way to get a timestamp in the destination file name to allow this command to be scheduled in a nightly batch file, for example ?

  2. Jason Faulkner

    To append a date stamp, include this in your batch script before the backup:

    REM Put date in MM-DD-YYYY format (assumes default US date/time settings)
    for /F “tokens=2,3,4 delims=/ ” %%a in (‘date /t’) do set filedate=%%a-%%b-%%c

    Now edit the backup line to include the %filedate% variable:

    SqlCmd -E -S Server_Name –Q “RESTORE DATABASE [Name_of_Database] FROM DISK=’X:PathToBackupFile%filedate%_[File_Name].bak’”

  3. danorth

    Thanks for the article. I remember spending the better part of the day piecing this same information together from several different websites.

    If anyone prefers using a GUI tool, there’s a good free one from SQLBackupandFTP. This tool is for backup only, and currently does not offer a GUI to restore.

    http://sqlbackupandftp.com/

    The free version does allow for backing up to a remote file share, while the pay versions offer encryption of the backed up SQL files.

  4. ErickDillinger

    Hi. Is there a way to restore a database with a different name, I mean, I want to restore a database but also I want to specify a new name in the sqlcmd.

Enter Your Email Here to Get Access for Free:

Go check your email!