SQL SERVER – Backup and Restore Database Using Command Prompt – SQLCMD

Backup and Restore is one of the core tasks for DBAs. They often do this task more times than they would have ideally loved to do so. One thing I noticed in my career that every successful DBA knows how to automate their tasks and spend their time either playing games on a computer or learning something new!

Let us see how a DBA can automate their task about Backup and Restore using SQLCMD. I am demonstrating a very simple example in this blog post. Please adapt the script based on your environment and needs.

Here is the basic script for Backup and Restore

Note: name of my database server is touching and I am connecting it with windows authentication.

Backup

BACKUP DATABASE AdventureWorks2012 TO DISK='d:\adw.bak'

Restore

RESTORE DATABASE AdventureWorks2012 FROM DISK='d:\adw.bak'

Here is the basic script for Backup and Restore using SQLCMD

Backup

C:\Users\pinaldave>SQLCMD -E -S touch -Q 
"BACKUP DATABASE AdventureWorks2012 TO DISK='d:\adw.bak'"

Restore

C:\Users\pinaldave>SQLCMD -E -S touch -Q 
"RESTORE DATABASE AdventureWorks2012 FROM DISK='d:\adw.bak'"

Please leave a comment if you are facing any issue. As mentioned earlier the scripts are very basic scripts, you may have to adapt them based on your environment. For example, if you are facing error there are chances that database files are already open or exists on the drive. You you should also have necessary permissions to do backup and restore as well file operations in your server.

SQL SERVER - Backup and Restore Database Using Command Prompt - SQLCMD sqlcmd-backup-restore

Watch a 60 second video on this subject

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , , ,
Previous Post
SQL SERVER – Generate Random Values – SQL in Sixty Seconds #042 – Video
Next Post
SQL SERVER – Get SQL Server Version and Edition Information – SQL in Sixty Seconds #043 – Video

Related Posts

64 Comments. Leave new

  • how to automate backup from one server and restore it in another server? pls explain using sql cmd as well as sql query if possible.

    Reply
  • Dear Pinal,

    i am trying to backup using cmd prompt, following error was encouter
    ——————————————————————————-
    HResult 0x35, Level 16, State 1
    Named Pipes Provider: Could not open a connection to SQL Server [53].
    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not
    allow remote connections..
    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
    ——————————————————————————–
    Kindly solve my issue

    Thanks in advance

    Reply
  • Man, you are great! So Simple and so usefull! Congrats, my friend!

    Reply
  • None of these work get incorrect syntax on the backup command every time! I am using the first example you show for backup.

    Reply
  • Your posts are very helpful to me. I have referred to several over the past few months while I take a DBM certification course.

    Reply
  • is it possible to use sqlcmd and backup a database without the log files? I assume I should be doing a Differential backup?
    Thanks

    Reply
  • yes, its possible.

    Reply
  • Hi,

    I am trying to take the backup using Sqlcmd but nothing happens. I dont get the backup file in the specified folder nor i get any error message.
    C:\Users\ahmad>sqlcmd -S win8Secondary\SOLARWINDS_ORION
    1> BACKUP DATABASE [SolarWindsOrion] TO DISK=’C:\DB-Backup\db-back.bak
    2~
    3~ BACKUP DATABASE SolarWindsOrion TO DISK=’C:\DB-Backup\db-back.bak
    4>
    5>
    6>
    7> BACKUP DATABASE SolarWindsOrion TO DISK=’C:\DB-Backup\db-back.bak
    8~
    9~ BACKUP DATABASE SolarWindsOrion TO DISK=’C:\DB-Backup\db-back.bak
    10>
    11>

    Your help with this will be much appreciated.

    Reply
    • Please use “GO” as batch separator.. your command is not sent to SQL unless you terminate batch using GO

      Reply
  • Restore-SqlDatabase : System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the
    existing ‘test_restore’ database.
    I am getting above error

    Reply
  • syed mujammil ahmed
    February 13, 2018 11:35 am

    msg 3201, level 16, state 1, server server_name, Line 1 cannot open backup device ‘d:\adw.bak. Operating System error 5.
    msg 3013, level 16, state 1, server server_name Line 1 backup database terminating abnormally

    Reply
    • System Error 5 = Access Denied.

      Generally I avoid keeping files on root of the drive like you have. I use “Run As Administrator” and keep file on some folder where I have permission.

      Reply
  • Hi
    Pinal dave,

    i am able to restore my database from .bak file. my question is , i had taken backup with date and time so i have multiple backup with date and time so how to restore latest one by batch file?

    Reply
  • How do I incorporate a date stamp into the backup name? for example: testmmddyy.bak instead of test.bak

    Reply

Leave a Reply

Menu