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.
Watch a 60 second video on this subject
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
65 Comments. Leave new
awesome!!!
Thanks Pinal. Its very useful to me on daily basis. Can u pls update the sample script for server authentication.
Thanks Pinal. One question though, why would you use a script to do the backup when SQL Server has the ability to do this for you using maintenance plans and jobs?
It is just another option to use. We don’t need to rely on GUI
SQL express doesn’t have maintenance plan options.
I have users who would like to run a quick back up before or after posting some transactions. Would prefer that they would not have to log into (or even see) Management Studio
nice
This is great and I love how simple it is. Thank you.
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.
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 …..
ur articals very easy and intrusting.i request u post some questions+ans about preparation MCSA SQL 2012.
sort way for backup and restore. Vary helpful command.
Thanks Pinal
Great help.
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?
Thank you so much :)
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’
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
how to close existing connection using restore command
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…
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
The media family on device ‘D:\BackUP\LW_CTEF_Staging\LW_CTEF_Staging.Bak’ is incorrectly formed
Thanks for this post, i was trying to backup and restore using Command prompt and I found it here.
Thanks Author.
hi pinal ,
is it possible to get the .bak in to anather server using link server