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)
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
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 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 …..
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
Pingback: SQL SERVER – Restore SQL Database using SSMS – SQL in Sixty Seconds #044 – Video « SQL Server Journey with SQL Authority
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 :)