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 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 touch 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.

Watch a 60 second video on this subject

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

38 thoughts on “SQL SERVER – Backup and Restore Database Using Command Prompt – SQLCMD

  1. Receive error: Sqlcmd: Error MSSQL Native Client 11.0: Name Pipes Provider: Could not open a connection to SQL Server

    Named pipes is enabled and I am a SA on the box.

  2. hi Pinal,

    C:\Users\Ajay Reddy>SQLCMD -E -S touch -Q “BACKUP DATABASE Honey
    HResult 0×35, Level 16, State 1
    Named Pipes Provider: Could not open a connection to SQL Server [53].
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in
    stance-specific error has occurred while establishing a connection to SQL Server
    . Server is not found or not accessible. Check if instance name is correct and i
    f SQL Server is configured to allow remote connections. For more information see
    SQL Server Books Online..
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.
    please give me some suggestion on this …..

  3. Pingback: SQL SERVER – Restore SQL Database using SSMS – SQL in Sixty Seconds #044 – Video « SQL Server Journey with SQL Authority

  4. This is great, but how would I take this one step further? I need to create a backup on production and restore a copy on development, which are two separate servers. I tried using dot notation in the database name but it doesn’t like the period. I guess I could create backup scripts on the prod box and run separate restore scripts on the dev box, but that’s not as smooth a solution. Any recommendations?

  5. hi ,
    my database is located in other system. am connecting other user mode and take that db back up … but its showing error .. pls help me …
    BACKUP DATABASE db TO DISK=’d:\adw.bak’

  6. PowerShell + SQL Job is better option in this case.
    Here are two scripts. One for backup, second for maintenance of backup files (for sure you don’t want to run out of storage space)

    #————————————————-
    #Backup Job script

    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null
    $s = new-object (“Microsoft.SqlServer.Management.Smo.Server”) $instance

    #Define the folder path as a variable
    $bkdir = “\\BackupServer\Share”
    $dbs = $s.Databases
    foreach ($db in $dbs)
    {
    $status=$db.Status

    #Don’t want to backup the tempdb database
    if(($db.Name -ne “tempdb”) -and ($status -eq “Normal”))
    {
    $dbname = $db.Name

    #Create a file name based on the timestamp
    $dt = get-date -format yyyy-MM-dd_HHmm

    $dbBackup = new-object (“Microsoft.SqlServer.Management.Smo.Backup”)
    $dbBackup.Action = “Database”
    $dbBackup.Database = $dbname
    $dbBackup.Devices.AddDevice($bkdir + “\” + $dbname + “_db_” + $dt + “.bak”, “File”)
    $dbBackup.SqlBackup($s)
    }
    }

    #—————————————————–
    #Backup directory maintenance job script

    # Backup destination path
    $Path = “\\BackupServer\Share”

    # Days for retention (-7 = 7 days kept)
    $Daysback = “-7″

    $CurrentDate = Get-Date
    $DatetoDelete = $CurrentDate.AddDays($Daysback)
    Get-ChildItem $Path | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | Remove-Item

  7. Hi this is Vivek,

    Thanks Pinal But I Wanted To Take a Backup/Restore Database From From One Server to Another Server using SQL Script in SQL server 2008 .
    I have tried many things But I didn’t Got The Proper solution So Please Do Let me know about Proper Solution…

  8. Hi Pinal,
    When I try to execute the command which is given by you, it’s not restoring and error msg displaying
    like mdf and ldf files

  9. Hi Pinal,

    I need to be able to backup a Database but change it’s name for example if it is called Nuneaton and i want to change it to the software name ie Topconnect.

    Then i need to restore it to the name Topconnect. I need to be able to do this without having to connect to SQL Management studio express and would like to do it all using scripts for any version of SQL.

    Any ideas that you may be able to suggest please?

  10. I am not able to restore the database. It gives error as .mdf and .ldf file cannot be overwritten.
    I need to restore the database programatically.
    Any idea, how this error can be resolved please?

  11. For those of you who are struggling with the Named Pipes Error issue, please try the following (I ran into the same issue but realized my mistake)

    (1) First of all please ensure that the Names Pipes are enabled under “Client protocols” as well “Protocols for SQLEXPRESS” in “SQL Server Configuration Manager” [I believe some of you already enabled them - if yes, then move to step 2 below]
    (2) Make sure that you are not using “localhost” or IP address for servername. Please use the fully qualified server name the way you use in SQL Server Management Studio to connect to the DB server for e.g. [ComputerName\\SQLExpress.

    My Named Pipes issues got fixed by doing the above 2 steps. Hope it works for you all too. Good Luck.

    Thanks,
    Jeet

  12. I have issues where restoring the same .bak via the GUI vs. command line restores different versions of the databse, as crazy as that sounds. Anyone else have that issue?

  13. I got a job by saying this answer in my last interview. thanks for awesome help.
    I got more idea about oracle from Besant Technologies. If anyone wants to get oracleTraining in Chennai visit Besant Technologies.

  14. Ajay Reddy, maybe you’re using “touch” as your instance name, if so, you’ll need to change to your correct instance name (in example, “localhost”)

  15. Hi,

    Last week i attended IBM interview. one interviewer asked one critical question but i cant and guess the answer. Please help me.

    question : last night your colleague took the sql Database backup but unfortunately at the time your default back path drive is full . so he took the backup some different place. next day morning your friend is left from office . he gone to his native his mobile is also switch off. morning you came to office as usual you stars your activity suddenly your server database has been corrupted. so you HAVE to restore your full backup that time you checked in default path but backup is not there . two option is there

    1 ) is you want to ask your friend where you took the backup, ask the path
    2) another one is some command base we can find

    first option is already fail because friend mobile is switched off

    what is second option ? how you can find.

    please help me how to find the bACKUP .

    • There are some system tables available to capture the backup activity. You can find those list tables in msdb database. Table names are starting like msdb.dbo.backup… Also you can check the below script to get the backup file details

      SELECT S.database_name, S.backup_start_date, S.backup_finish_date
      , F.logical_name, F.physical_drive, F.physical_name
      FROM msdb.dbo.backupset S
      INNER JOIN msdb.dbo.backupmediaset MS
      ON S.media_set_id = MS.media_set_id
      INNER JOIN msdb.dbo.backupfile F
      ON F.backup_set_id = S.backup_set_id

  16. i use this it gives an error —-
    Msg 3201, Level 16, State 1, Line 1
    Cannot open backup device ‘E:\adw.bak’. Operating system error 5(Access is denied.)

  17. Hi,

    I’ve been trying to restore a database but it seems the log is far too large and the server is running out of memory! Is there any way to just extract the data file from the .bak or to tell RESTORE to ignore the log file?

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s