SQL SERVER – Restore Database Backup using SQL Script (T-SQL)

In this blog post we are going to learn how to restore database backup using T-SQL script. We have already database which we will use to take a backup first and right after that we will use it to restore to the server. Taking backup is an easy thing, but I have seen many times when a user tries to restore the database, it throws an error.

SQL SERVER - Restore Database Backup using SQL Script (T-SQL) 44-800x450

Step 1: Retrive the Logical file name of the database from backup.

Solarwinds
RESTORE FILELISTONLY
FROM DISK = 'D:\BackUpYourBaackUpFile.bak'
GO

Step 2: Use the values in the LogicalName Column in following Step.

----Make Database to single user Mode
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE YourDB
FROM DISK = 'D:\BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:\DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:\DataYourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser
mode.
If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER
GO

Watch a 60 second video on this subject

Let me know what you think of this blog post and if you use the T-SQL scripts displayed in this blog post, just let me know if it requires any improvement.

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

Solarwinds
, , , ,
Next Post
SQL SERVER – Interesting Observation – Using sqlcmd From SSMS Query Editor

Related Posts

535 Comments. Leave new

  • Hi Imran and Tejas,

    Thanks for your replies.

    I tried to restore the db like this:

    RESTORE DATABASE
    FROM DISK = ‘N\\\\dbbackup.bak’

    WITH MOVE ‘dbname.mdf’ TO ‘E:\\dbname.mdf’,

    MOVE ‘dbanme_log.ldf’ TO ‘E:\\dbname_log.ldf’

    With Replace

    When I give ‘with replace’ it gives error on that replace statement. When I remove the ‘with replace’ command it says:

    Exclusive access could not be obtained because the database is in use.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    I tried giving : exec sp_who

    It gives only my pc’s name. Can someone help me out with this please? I had been trying to restore this for the past 2 days..

    Then I tried to backup the db from the local instance in the syntax which Imran gave me. But then it says, incorrect syntax near ”.

    I had been trying to fix this for quite sometime now.. please help..

    Thanks

    Reply
  • Ok, I had a problem with giving the database name with dot inbetween. Thats why it said that error. Now after enclosing the db name within quotes, Now I tried giving what Imran said. It gives the message :

    Cannot open backup device. Operating system error 53(The network path could not be found).

    I hv verified the network path.

    Can you confirm where I have to give the $ symbol. Right after the drive? Like this: \\\E$\\backupname

    Thanks.

    Reply
  • @ Priya.

    I apologize for giving you wrong advice, I think I understood your question completely wrong, script that I gave you takes backup of any database on network drive.

    But in your question you asked to restore database backup from network drive.

    Look at this webpage once :

    One important note : backup you are trying to restore from different machine, should be in Shared folder. ( folder marked with a hand symbol), Only then SQL Server can access that folder. If your backup is not in a shared folder, then SQL Server cannot restore backup.

    Error1 : Exclusive access could not be obtained because the database is in use.

    Explanation:
    Generally you will see this message when you are trying to restore a database that is online. Or if you are trying to over write .mdf file on Operating System that are being used by database, this usually happens when you are trying to restore.

    You need to use with replace with Restore command to point it to different location.

    Backup will have some information in it, it remembers from which location it was taken backup. And when you try to restore it will try to restore database in the same location.

    Since your backup is residing in other servers, file location of the two servers might not match, so you need to use WITH REPLACE command with , WITH RESTORE command

    The Shared folder path is generally like this,

    \ServernameFoldernamefilename

    Servername – Name of the Server
    Foldername – Name of the folder ( this folder must be shared folder , folder will have a hand symbol )
    filename – name of the backup file.

    Regards
    IM.

    Reply
  • Hi Imran,

    Thanks for ur reply.

    I did try out this :

    RESTORE DATABASE
    FROM DISK =N’\servername\C$\sharefolder\dbbackup.bak’

    WITH MOVE ‘dbname.mdf’ TO ‘E:\\dbname.mdf’,

    MOVE ‘dbanme_log.ldf’ TO ‘E:\\dbname_log.ldf’,

    Replace

    But I still keep getting this error:

    Cannot open backup device. Operating system error 53(The network path could not be found).

    Thanks in advance..

    Reply
  • I was able to find a solution. Thought I would share it with you all:

    First had to download the Microsoft SQL Server Database Publishing Wizard 1.1 from the following location:

    http://www.microsoft.com/en-us/download/details.aspx?id=5498

    I was able to take a backup of my local db through the above wizard and stored it in my local pc. It saves it as a .sql file.

    Then, login to sql express management studio with the server login credentials–click on File->open and open the file which you have just saved thru the publishing wizard. Execute the query. The db was restored successfully.

    This may not be the easiest way to restore on network from local pc. But then I had to do it only this time and not often. So, it worked for me!

    Regards,
    Priya.

    Reply
  • hello

    plz let me know, under which situation might you restore the database backup…

    Reply
  • hi all. help me.

    Example:

    select * from invoice

    surname Lastname
    ………………………………..
    Michael Bolton
    George Michael
    Stiven Harry

    How insert new field? how do insert ordinal number to new field?

    id Surname Lastname
    ………………………………………
    1 Michael Bolton
    2 George Michael
    3 Stiven Harry
    . … …
    . … …
    . … …
    +1

    HOW DO IT?????????

    Reply
  • may i know the architecture of the sql server2005. please any one give me the details of it.

    Reply
  • Hi Dave,

    You are simply great. Thank for all your help. Quick question. I was following your TSQL restore steps for restoring Adventure works database and got this error
    “The media set 2 media families but only 1 are provided. all members must be provided.

    what does it mean? Please advice.

    Thanks
    JG

    Reply
  • Imran Mohammed
    January 23, 2009 8:55 am

    @JG

    Error : “The media set has 2 media families but only 1 are provided. all members must be provided.”

    Explanation: When you take backup using SQL Server Management Studio Interfaces ( Right Click Database Name, AllTask , Backup ) , You see a section called on interface called Destination , this is where you backup file will be stored. In this section you need to give Path where you want to store backup file and name of the backup file itself.

    Most of the times you will see a location by default in this Destination Section and if you click Add and add other location and file name. You will see two location and two file names in Destination Section if you then take backup (Click Ok ).What really happens is your backup has now been split into two parts which is now at two different locations saved in two different file names.

    When you try to restore database from using that backup copy, you need to mention two backup file located at two different locations. If you give any one backup file when restoring then you will see this message,

    “Error : The media set has 2 media families but only 1 are provided. all members must be provided.”

    Which says, there are two backup files for this backup copy or this family of backup contains two backup files, when restoring those backups give both the file names and location where they exists.

    How to avoid this error: When you take backup from SQL Server Management Studio using interfaces, make a habit to delete old location, and add a new file location and file name and then take backup Or else , use scripts and just give one location with a file name.

    Hope this helps,
    IM.

    Reply
  • How to restore a Database?I got some error while restore.
    that is
    The media set has 2 media families but only 1 are provided. error.Is there any method to rectify that problem?

    Reply
  • Imran Mohammed
    February 4, 2009 8:46 pm

    @Nirmala,

    Read reply above your reply, I have explained this issue in detail,

    what error says, when you took backup, you have taken in two different files, and when you restore you have to give both the files, but error says that you are using one of the two backup files.

    Solution: Use all backup files to restore database.

    Regards,
    IM

    Reply
  • Can i take backup of database using query analyzer.

    Reply
  • Dave,

    In transactional replication , Our team member deleted all records in one table accidentally from publisher. Same commands also replicated to subscriber before we notice. We recovered most of the data using backup. Is it possible to recover from LOG file. Publisher DB in full recovery model.

    Please help on this issue.

    Many Thanks
    Renu

    Reply
  • @Renu,

    Yes It is possible, if you know the time when delete command was executed.

    You can do a point in time restore using transactional log back up ONLY IF You already have/had taken a full backup of the database before executing delete statement.

    What you need to do now.

    1. Take transactional log backup of the database NOW.

    2. You should have Fullbackup of the database that was taken prior to executing delete statement. Restore this full backup with NO_RECOVERY with a new name (just to reduce errors).

    3. Restore recently taken (only one) differential backup with NO_RECOVERY ( if any) and all transactional log backup’s that were taken between FULL Backup (If no Differential backup was taken) /Differential backup and recently taken Logbackup (if any). DO not RESTORE last Transactional backup (at this time, we will apply it in step4) , apply all other transactional log backup’s in sequence with NO_RECOVERY.

    4. Now apply the recently taken transactional log backup with RECOVERY using STOP AT Function, and specify a time prior to the time of executing delete statement.

    Instead of doing step1 – 4 using scripts, it is easy to do these steps using interfaces.

    Try this on your Test Server.

    Regards,
    IM.

    Reply
  • I Restored a Production Backup File to my dev environment DB. Now we got to know know that our client is working on forms in this DB. Can i restore the DB to the point previous point of time. we are not having Backup file of Dev environment

    Reply
  • Question:
    My Boss is convinced that a network file server back up is good enough to backup SQL server databases. How can I convince him back that Database Back up is different from file server back up

    Reply
  • @Mac,

    When sql server is running all its .mdf and .ldf files will be locked by sql server and no application can copy or take backup of those files, when you schedule a file system backup I am sure it cannot take backup for these .mdf and .ldf files on operating system.

    For most of the products this is true, that they cannot taken backup of .mdf files and .ldf files when sql server is using those files.

    Best way to use your file system backups with SQL Server backup is,

    First take SQL Server backup’s on operating system and then take backup of whole server using file system, that way you are taking backup of sql server native backups too which can be used when restoring,even though your .mdf and .ldf might be currupted, you still will have a good sql server backup.

    Just for the sake of your Boss, ask Backup team to restore whole server, and see if you can have your sql server databases up.

    Regards,
    IM.

    Reply
  • Hi Pinal,
    Thanks for this wonderful blog. I am referring to this over a period of time. I have a question for your help as:
    I have a large database with 50Gigs. I have setup full backup, differential backup and transaction log back up.

    The data base is uptodate and in running status. I am looking for an old copy of the database from full+diff backups to get some changes in the schema/stored procs etc.

    The point is that I want to restore this database with a different db name on the same server without disturbing the current running original database. I could restore the full backup. And now when I try to implement the differental backup [backup was done with a different db name], to the newly created db. that fails mentioning that NoRecovery is not specified.
    What may be wrong with me here. Thanks in advance
    Pawan Bansal

    Reply
  • Pawan,
    Are you restoring the full backup with norecovery
    then restoring the differential with recovery?
    You need to restore the full with norecovery then the diff with recovery.

    Reply

Leave a Reply

Menu