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'"

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)

About these ads

SQL SERVER – Best Practices to Store the SQL Server Backups

Nobody doubts the necessity to create SQL Server backups – I have covered this topic extensively before. The question of where to store the backups however often goes unanswered. 

I will try to compare some of the most popular options for  this task. For demonstration purposes we will use the options that SQLBackupAndFTP provides us with, when we select a destination for storing backups.

SQLBackupAndFTP form to select a backup destination

SQL backup to Local/Network Folder/External HDD

SQLBackupAndFTP Folder Settings form

If you store the backup on the same drive as your database – you won’t have it when the disk fails. If you store it on the same server, you may lose when your server goes down. If you store it on the network, you may lose the backup when the whole office goes down in flames. You may think the chance of it is slim, but these things happen quite often and a smart database administrator should be prepared for that.

So why would you ever choose storing backups on-site and not in the cloud?
Well, if your data backup is over 100GB, a network backup becomes a very attractive option. Because your network speed would always exceed your cloud speed, this is a perfect option for storing large backups, or less important backups, or duplicates of whatever you store in the cloud. And if you backup to external HDD, with the proper policies in place you could take the HDD off-site. However I would prefer to exclude the human factor from the process.

SQL backup to FTP server

SQLBackupAndFTP FTP Settings form

You may be surprised, but old FTP is live and kicking. It is still a viable option for extra cheap storage. If you own an FTP server, the cost of space may be close to 0. The downside is that it is still on the web, so upload speeds will be slower than on your network. Also you should be sufficiently proficient to configure and manage it. And even if you do that, chances are that engineers at Amazon are still better than you in this.

My biggest problem with FTP is that it is terribly insecure. If you use FTP, anyone who eavesdrops  on communication can get your FTP passwords and everything that you send. To overcome this, a better software package like SQLBackupAndFTP allow you to use SFTP or FTPS and encrypt your backup, making this type of communication highly secure again.

SQL backup to Amazon S3

SQLBackupAndFTP Amazon S3 Settings form

Frankly this is my favorite option. It is the most stable of any cloud solution I’ve tested. It is very reliable and fast. If you have the option of storing in Amazon S3 – just use it. Also, if you need ultra-low cost storage and do not mind that retrieving your backups may take several hours, your can use Amazon Glacier  (Just open the AWS console and configure Lifecycle Role for a bucket to add actions Transition to Glacier and Expiration. Find more information in this blog)

SQL backup to Dropbox

SQLBackupAndFTP Dropbox Settings form

SQL Backup to Dropbox is my second favorite option after Amazon S3. Dropbox uses Amazon S3 internally, so you get the similar quality of storage. For large backup sizes Amazon S3 is significantly cheaper though. But if you want to configure a backup for a small client’s database fast, nothing beats a free Dropbox backup plan (limited to 2GB). Your client would be very impressed if you set up the complete backup process in the cloud for practically free in a matter of a few minutes.

SQL backup to Google Drive

SQLBackupAndFTP Google Drive form

At this point the .NET library that Google Drive provides for software developers is in beta. It is not the most stable of products. If your backup size is big or your connection is slow, the Google will drop your upload in 1 hour no matter what. So I would strongly advise you to stay away from it, at least for now.

SQL backup to Box

SQLBackupAndFTP promises to bring this destination to their product. In the meantime the Box (formerly Box.NET) is one of the oldest and still a very strong contender among the cloud storage options.

SQL backup to SkyDrive

You can not avoid mentioning SkyDrive when talking about cloud storage. I can not comment on how stable SkyDrive is. However (I think because of Windows 8 push) Microsoft does not provide .NET API for pre-Windows 8 platforms, so developers of SQL Server software have no way to bring native backups to SkyDrive.


With simple tools like SQLBackupAndFTP  and cheap storage options there never was a better time for storing your backups in the cloud. Keep your database backup on the network and on one of cloud storages. Use Amazon S3 by default and Dropbox for small databases (or budgets). If you already own and FTP server and can configure it to use through FTPS or SFTP – use it. Whatever you choose – there is no excuse not to have backups.

Let us see how to take backup in very quick video:

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

SQL SERVER – Take Database Backup using SSMS – SQL in Sixty Seconds #037 – Video

Whenever I am suggesting something which changes how database works or the existing status of the database, my suggestion along with it is to take the database backup before making such changes. If the changes are in configurations, that can be easily revert but if the changes are such that it will impact the data, I always suggest to take backup. The nature of this blog is such that we have readership from readers with different expertise, some are experts and some are novice.

One of the questions I keep on receiving comments are whenever I give suggestion to take a full database backup is – how to take database backup. It is not possible to explain in each and every blog regarding how to take backup of the database as it will be of no interest to the users who are very much familiar with this concept. To help the beginners who have yet to learn regarding how to take database backup, here is the SQL in Sixty Series episode which explains how to take database backup.

There are three types of the backup – 1) Full Backup 2) Differential Backup and 3) Log Backup. We can take the differential backup as well log backup from the same screen where we take full backup from. I have explained the same also in later parts of the video.

If you are using SQL Server Management Studio – here is the path which will bring you to the screen where you can take various kinds of backup.

Right Click on Database >> Tasks >> Back up… 

Let us see how to take backup in this SQL in Sixty Seconds:

Please note that if your database is large, it will take long time to take backup. We will focus on the various steps to initiate the backup in this video.

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

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

SQL SERVER – Restoring 2012 Database to 2008 or 2005 Version and 2 other Most Asked Questions

Some questions never get old. Let me list a few of them. As the year 2012 is about to end, today I will talk about three of the most asked questions to me in an email.

Q 1: What is AdventureWorks and where can I get it?
A 1: AdventureWorks is sample database build by Microsoft for running sample/demonstration queries for the user. You can get that from CodePlex. I have written an article about this subject earlier and have created video as well.

Q 2: Can I restore database backup taken from 64-bit server to 32-bit server and vice-versa?
A 2: Absolutely, You can do it as backups are just a file. Just like any other files (JPG, docs, etc) you can use them in 32 bit and 64 bit application server without any issue. The issue related to the platform should only arise with the application (like SQL Server, Office etc.).

Q 3: Can I restore a SQL Server 2012 database to earlier versions like 2008 or 2005? Or How to Downgrade my Database to earlier versions?
A 3: No, you can only move forward. This means, you can restore the database from 2005 or 2008 to 2012 version but can not do it in reverse order. The next most popular question I receive is if we can’t restore a SQL Server 2012 database to earlier version what is the next best option? I have personally faced this issue once before and I had manually created T-SQL script using Script and Data Generator Wizard in SQL Server 2012  (Watch Video over here) and rebuild my database in SQL Server 2008R2. If you have many different databases which you want to address, you can use SSIS to automate the script generation.

Here is one question, I always ask back to my users – why do you want downgrade your database? I have hardly gotten valid detailed answer on this subject. Did you face similar situation ever in your future? If yes, I would like to know more about it.

And one more thing…

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

SQL SERVER – Select the Most Optimal Backup Methods for Server

Backup and Restore are very interesting concepts and one should be very much with the concept if you are dealing with production database. One never knows when a natural disaster or user error will surface and the first thing everybody wants is to get back on point in time when things were all fine. Well, in this article I have attempted to answer a few of the common questions related to Backup methodology.

How to Select a SQL Server Backup Type

In order to select a proper SQL Server backup type, a SQL Server administrator needs to understand the difference between the major backup types clearly. Since a picture is worth a thousand words, let me offer it to you below.

Select a Recovery Model First

The very first question that you should ask yourself is: Can I afford to lose at least a little (15 min, 1 hour, 1 day) worth of data? Resist the temptation to save it all as it comes with the overhead – majority of businesses outside finances can actually afford to lose a bit of data.
If your answer is YES, I can afford to lose some data – select a SIMPLE (default) recovery model in the properties of your database, otherwise you need to select a FULL recovery model.
The additional advantage of the Full recovery model is that it allows you to restore the data to a specific point in time vs to only last backup time in the Simple recovery model, but it exceeds the scope of this article

Backups in SIMPLE Recovery Model

In SIMPLE recovery model you can select to do just Full backups or Full + Differential.

Full Backup

This is the simplest type of backup that contains all information needed to restore the database and should be your first choice. It is often sufficient for small databases, but note that it makes a big impact on the performance of your database

Full + Differential Backup

After Full, Differential backup picks up all of the changes since the last Full backup. This means if you made Full, Diff, Diff backup – the last Diff backup contains all of the changes and you don’t need the previous Differential backup. Differential backup is obviously smaller and carries less performance overhead

Backups in FULL Recovery Model

In FULL recovery model you can select Full + Transaction Log or Full + Differential + Transaction Log backup. You have to create Transaction Log backup, because at that time the log is being truncated. Otherwise your Transaction Log will grow uncontrollably.

Full + Transaction Log Backup

You would always need to perform a Full backup first. Then a series of Transaction log backup. Note that (in contrast to Differential) you need ALL transactions to log since the last Full of Diff backup to properly restore. Transaction log backups have the smallest performance overhead and can be performed often.

Full + Differential + Transaction Log Backup

If you want to ease the performance overhead on your server, you can replace some of the Full backup in the previous scenario with Differential. You restore scenario would start from Full, then the Last Differential, then all of the remaining transactions log backups

Typical backup Scenarios

You may say “Well, it is all nice – give me the examples now”. As you may already know, my favorite SQL backup software is SQLBackupAndFTP. If you go to Advanced Backup Schedule form in this program and click “Load a typical backup plan…” link, it will give you these scenarios that I think are quite common – see the image below.

The Simplest Way to Schedule SQL Backups

I hate to repeat myself, but backup scheduling in SQL agent leaves a lot to be desired. I do not know the simple way to schedule your SQL server backups than in SQLBackupAndFTP – see the image below. The whole backup scheduling with compression, encryption and upload to a Network Folder / HDD / NAS Drive / FTP / Dropbox / Google Drive / Amazon S3 takes just a few minutes – see my previous post for the review.

Final Words

This post offered an explanation for major backup types only. For more complicated scenarios or to research other options as usually go to MSDN.

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

SQL SERVER – Sends backups to a Network Folder, FTP Server, Dropbox, Google Drive or Amazon S3

Let me tell you about one of the most useful SQL tools that every DBA should use – it is SQLBackupAndFTP. I have been using this tool since 2009 – and it is the first program I install on a SQL server. Download a free version, 1 minute configuration and your daily backups are safe in the cloud.
In summary, SQLBackupAndFTP

  • Creates SQL Server database and file backups on schedule
  • Compresses and encrypts the backups
  • Sends backups to a network folder, FTP Server, Dropbox, Google Drive or Amazon S3
  • Sends email notifications of job’s success or failure

SQLBackupAndFTP comes in Free and Paid versions (starting from $29) – see version comparison. Free version is fully functional for unlimited ad hoc backups or for scheduled backups of up to two databases – it will be sufficient for many small customers.

What has impressed me from the beginning – is that I understood how it works and was able to configure the job from a single form (see Image 1 – Main form above)

  1. Connect to you SQL server and select databases to be backed up
  2. Click “Add backup destination” to configure where backups should go to (network, FTP Server, Dropbox, Google Drive or Amazon S3)
  3. Enter your email to receive email confirmations
  4. Set the time to start daily full backups (or go to Settings if you need Differential or  Transaction Log backups on a flexible schedule)
  5. Press “Run Now” button to test

You can get to this form if you click “Settings” buttons in the “Schedule section”. Select what types of backups and how often you want to run them and you will see the scheduled backups in the “Estimated backup plan” list

A detailed tutorial is available on the developer’s website.

Along with SQLBackupAndFTP setup gives you the option to install “One-Click SQL Restore” (you can install it stand-alone too) – a basic tool for restoring just Full backups.

However basic, you can drag-and-drop on it the zip file created by SQLBackupAndFTP, it unzips the BAK file if necessary, connects to the SQL server on the start, selects the right database, it is smart enough to restart the server to drop open connections if necessary – very handy for developers who need to restore databases often.

You may ask why is this tool is better than maintenance tasks available in SQL Server? While maintenance tasks are easy to set up, SQLBackupAndFTP is still way easier and integrates solution for compression, encryption, FTP, cloud storage and email which make it superior to maintenance tasks in every aspect.

On a flip side SQLBackupAndFTP is not the fanciest tool to manage backups or check their health. It only works reliably on local SQL Server instances. In other words it has to be installed on the SQL server itself. For remote servers it uses scripting which is less reliable. This limitations is actually inherent in SQL server itself as BACKUP DATABASE command  creates backup not on the client, but on the server itself.

This tool is compatible with almost all the known SQL Server versions. It works with SQL Server 2008 (all versions) and many of the previous versions. It is especially useful for SQL Server Express 2005 and SQL Server Express 2008, as they lack built in tools for backup.

I strongly recommend this tool to all the DBAs. They must absolutely try it as it is free and does exactly what it promises. You can download your free copy of the tool from here.

Please share your experience about using this tool. I am eager to receive your feedback regarding this article.

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

SQL SERVER – Move Database Files MDF and LDF to Another Location

When a novice DBA or Developer create a database they use SQL Server Management Studio to create new database. Additionally, the T-SQL script to create a database is very easy as well. You can just write CREATE DATABASE DatabaseName and it will create new database for you. The point to remember here is that it will create the database at the default location specified for SQL Server Instance (this default instance can be changed and we will see that in future blog posts). Now, once the database goes in production it will start to grow.

It is not common to keep the Database on the same location where OS is installed. Usually Database files are on SAN, Separate Disk Array or on SSDs. This is done usually for performance reason and manageability perspective. Now the challenges comes up when database which was installed at not preferred default location and needs to move to a different location. Here is the quick tutorial how you can do it.

Let us assume we have two folders loc1 and loc2. We want to move database files from loc1 to loc2.

-- Take database in single user mode -- if you are facing errors
-- This may terminate your active transactions for database
-- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname = N'TestDB'

Now move the files from loc1 to loc2. You can now reattach the files with new locations.

-- Move MDF File from Loc1 to Loc 2
-- Re-Attached DB
( FILENAME = N'F:\loc2\TestDB.mdf' ),
FILENAME = N'F:\loc2\TestDB_log.ldf' )

Well, we are done. There is little warning here for you: If you do ROLLBACK IMMEDIATE you may terminate your active transactions so do not use it randomly. Do it if you are confident that they are not needed or due to any reason there is a connection to the database which you are not able to kill manually after review.

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