When you have to conduct a restore of a Microsoft SQL database it can be a rather scary time wrought with stress and angry calls from a client or IT manager. Sometimes you just need a quick look at a how to just to make you calm down and realize its not so bad. So to that end, I present you a quick how to on restoring a Microsoft SQL database from a backed up BAK file. The first portion of this how to will cover restoring from a 3X Appliance as that's what we use here at the office, but the second half covers the use of the Restore command. So, if you have no 3X appliance and backed it up some other way you can obviously skip down to the second part.
The Steps:
1. Complete a Restore of your backed up BAK from your 3X Appliance.
To do this we just need to go into the web manager, to 'client management' then click on 'manage clients' and wait for the page to load. Once there we can select the client in question and click 'Restore'. The page just before clicking restore should look like this:
Next we'll want to go through the restore wizard. There are a couple of ways we could go about the restore. For our purposes I suggest we go ahead and do an SCP restore. This will allow us to create a user name and password on the vault with which we can use to download the BAK file. So we'll select Microsoft SQL Server as the restore type, and select SCP under 'Restore To'. Then you'll just select your backup set, and what date you want to go back to, and then which databases you wish to restore. Once complete you'll get this lovely screen.
Note the user name and password from this screen as we'll need it for the SCP session. On your client machine open up any SCP client of your choice. Your login screen should be filled out in a manner similar to this:
For the port number you'll use what ever you have configured the Backup data port to. In my case this is port 5589. Also note that I have selected SCP as the file protocol. Once successful you'll be greeted with a simple file system screen. You will also want to adjust the Server wait timeout under the connections section that appears when you select 'Advanced Options.' I suggest setting it to a value of 999. All you need to do is download your bak file.
Step 2: Restore the BAK to your database using command line. This is the part I imagine a large group of you have skipped to. Now this is actually a fairly simple process but must still be completed with care. While I know that it is possible to conduct a restore using the management studio UI, I prefer to do this using osql and the command interface.
To complete the restore you will use either 'osql', or in the case of 08R2 'sqlcmd' to connect to your database. It is suggestible to go ahead and use the -S and -U flags. Also bare in mind that if you are on a cluster situation that you'll want to use the name for the cluster as opposed to the computer name of the server you are on to resolve the instance.
Once connected we'll be making use of the restore command. This command is actually very simple to use and is pretty self explanatory. The only thing we need to clarify is that since you are restoring from a BAK you'll need to use the 'restore from disk' option. Here is an example:
RESTORE DATABASE ImportantStuff
FROM DISK = 'C:\Users\Administrator\Desktop\ImportantStuff.bak'
Now if the backup solution you are using supports versions and you aren't conducting manual differentials of the database the above should suit you just fine as is. This will conduct the restore with recovery. If you are using a manual dump with differentials you will want to specify which file in the BAK you are restoring from. To get more information on this I suggest reading the msdn article here that covers the restore command in greater detail. That about covers a simple restore.
The Steps:
1. Complete a Restore of your backed up BAK from your 3X Appliance.
To do this we just need to go into the web manager, to 'client management' then click on 'manage clients' and wait for the page to load. Once there we can select the client in question and click 'Restore'. The page just before clicking restore should look like this:
Next we'll want to go through the restore wizard. There are a couple of ways we could go about the restore. For our purposes I suggest we go ahead and do an SCP restore. This will allow us to create a user name and password on the vault with which we can use to download the BAK file. So we'll select Microsoft SQL Server as the restore type, and select SCP under 'Restore To'. Then you'll just select your backup set, and what date you want to go back to, and then which databases you wish to restore. Once complete you'll get this lovely screen.
Note the user name and password from this screen as we'll need it for the SCP session. On your client machine open up any SCP client of your choice. Your login screen should be filled out in a manner similar to this:
For the port number you'll use what ever you have configured the Backup data port to. In my case this is port 5589. Also note that I have selected SCP as the file protocol. Once successful you'll be greeted with a simple file system screen. You will also want to adjust the Server wait timeout under the connections section that appears when you select 'Advanced Options.' I suggest setting it to a value of 999. All you need to do is download your bak file.
Step 2: Restore the BAK to your database using command line. This is the part I imagine a large group of you have skipped to. Now this is actually a fairly simple process but must still be completed with care. While I know that it is possible to conduct a restore using the management studio UI, I prefer to do this using osql and the command interface.
To complete the restore you will use either 'osql', or in the case of 08R2 'sqlcmd' to connect to your database. It is suggestible to go ahead and use the -S and -U flags. Also bare in mind that if you are on a cluster situation that you'll want to use the name for the cluster as opposed to the computer name of the server you are on to resolve the instance.
Once connected we'll be making use of the restore command. This command is actually very simple to use and is pretty self explanatory. The only thing we need to clarify is that since you are restoring from a BAK you'll need to use the 'restore from disk' option. Here is an example:
RESTORE DATABASE ImportantStuff
FROM DISK = 'C:\Users\Administrator\Desktop\ImportantStuff.bak'
Now if the backup solution you are using supports versions and you aren't conducting manual differentials of the database the above should suit you just fine as is. This will conduct the restore with recovery. If you are using a manual dump with differentials you will want to specify which file in the BAK you are restoring from. To get more information on this I suggest reading the msdn article here that covers the restore command in greater detail. That about covers a simple restore.
