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 DATABASE AdventureWorks2012 TO DISK='d:\adw.bak'


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

Here is the basic script for Backup and Restore using SQLCMD


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


C:\Users\pinaldave>SQLCMD -E -S touch -Q "RESTORE DATABASE AdventureWorks2012 FROM DISK='d:\adw.bak'"
sqlcmd backup restore SQL SERVER   Backup and Restore Database Using Command Prompt   SQLCMD

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)

50 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 0x35, 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)

    #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”)

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



  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.

    Liked by 1 person

  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?



  18. Hello Sir,
    How can we restore it at specified location, my c: drive is about to full and i restore it in another drive, how can i do that using query?????????????????????


  19. how to backup of database from other server (ex:A server) and restore to other server (ex: B server) using scripts Note:this entire operation should perform from other server(ex:C server)


  20. when i am using the above command it gives me the error can’t open backup device. How can i short out this problem . Any help will be appreciated. Please reply via email its urgent


  21. Here’s script/stored procedure i wrote for backing up all databases in the current instance. Actually I wrote it for use in the express edition, because no database maintanance is available.

    It will create one backup statement for every database except tempdb and execute it.
    The proc will check, if the backup path is available. It is designed to create a subfolder for every day of the week in which a backup is executed and overwrite it one week later.

    What do you think of it?

    USE [master]
    /****** Object: StoredProcedure [dbo].[BACKUP_ALL_DB] Script Date: 05/29/2015 11:49:23 ******/
    — ===========================================================================================
    — Author: NSc
    — Create date: 28. – 29.05.2015
    — Description: dynamic backcup script for current instance, backup all DB except TEMPDB
    — Backuppath MUST be available, missing folders for days of week will be created
    — ===========================================================================================


    — Add the parameters for the stored procedure here
    @BackupPath nvarchar(255) = ”, –if paramater is not provided, proc will read it from the registry
    @NoOfBackupDays int = 5, — max. 7 days
    @FirstBackupDay int = 1 — first day + No of days 7
    set @NoOfBackupDays = 7

    — sum first day + no of days 8
    set @FirstBackupDay = 8 – @NoOfBackupDays

    — check if backup path is provided
    if (@BackupPath = ” or @BackupPath is NULL)
    — build regkey for backup path of current Instance
    declare @RegKey nvarchar (100)
    set @RegKey = ( select ‘SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL’ + substring(cast(serverproperty(‘ProductVersion’) as nvarchar (255)),1,2) + ‘.’ + @@SERVICENAME +’\\MSSQLServer’)

    — read registry value BackupDirectory
    DECLARE @returnValue NVARCHAR(255)
    EXEC master.dbo.xp_regread
    @rootkey = N’HKEY_LOCAL_MACHINE’,
    @key = @RegKey,
    @value_name = N’BackupDirectory’,
    @value = @returnValue output
    SET @BackupPath = ( @returnValue + ‘\’)

    — Parameters are correct now

    — check directorys and create if neccessary

    — create table for directory information
    Create Table #Dir (
    [ENTRY] nvarchar (255) null
    ,[File Exists] int NULL
    ,[File is a Directory] int null
    ,[Parent Directory Exists] int null

    — is backup path available in OS?
    declare @DirFlag int = 0
    insert into #Dir ([File Exists], [File is a Directory], [Parent Directory Exists]) EXEC master..xp_fileexist @BackupPath

    set @DirFlag = (select TOP 1 [FILE is a Directory] from #Dir)

    — if BackupPfad is not avialable in OS => End
    if @DirFlag = 0 goto finish
    set @DirFlag = 0

    — check for subfolders for days of week and create if missing

    — make temp table #Dir empty and reset identity column
    delete #Dir
    dbcc checkident (#Dir, reseed,1)

    — fill temp table with subdirectory info of backup path
    insert into #Dir (ENTRY) EXEC master..xp_subdirs @BackupPath

    — loop for subfolder check: all all necessary subfolders for days of week available ?
    declare @i int = @FirstBackupDay
    declare @DirectoryCommand nvarchar (255)

    while @i < (@NoOfBackupDays + @FirstBackupDay)

    — is subfolder available?
    set @DirFlag = (select COUNT(*) from #Dir where [ENTRY]= CAST (@i as nvarchar (2)))

    — if not, create
    if @DirFlag = 0
    set @DirectoryCommand = (select 'md ' + char(34)+ @BackupPath + CAST (@i as nvarchar (2)) + CHAR (34))
    — select @DirectoryCommand
    exec master..xp_cmdshell @DirectoryCommand
    set @i = @i + 1

    — subfolders are available now

    — execute backup

    — empty destination folder
    declare @DelCommand nvarchar (255)
    set @DelCommand= (select 'del "' + @BackupPath + CAST(datepart(dw, sysdatetime()) as nvarchar(1)) + '\*.*" /Q /F')
    — select @DelCommand
    EXEC master..xp_cmdshell @DelCommand

    –Temp table for backup statements
    create table #SQLSTATEMENTS(
    [SQLTEXT] [nvarchar](1000) NULL
    — fill temp table with statemants
    insert into #SQLSTATEMENTS (SQLTEXT) select 'Backup Database ' + char(91) + name + char(93) + ' to Disk=' + char(39) + @BackupPath + cast(DATEPART(DW, sysdatetime()) as varchar (1)) + '\' + replace(name,'\','') + '.bak' + char(39) from sys.databases where name ‘tempdb’

    — execute staemants
    set @i = 1

    declare @sql nvarchar (1000)

    while @i <= (select MAX(id) from #SQLSTATEMENTS)
    set @sql= (select sqltext from #SQLSTATEMENTS where #SQLSTATEMENTS.ID=@i)
    exec sp_executesql @sql
    set @i=@i+1
    return @i — number of backup files

    — Backup executed

    — error handling: backup path provided ist not available: return value -1

    return -1



  22. Hello Pinal,I have a big SQL file (almost 1GB) from a database, SSMS does not open it due to storage issues. I want to use SQLCMD to execute it on another database,how can I go about it?I used the -d option but it still says databse does not exist.


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