How to backup and restore database on Microsoft SQL Server 2005

Backup and restore database on Microsoft SQL Server 2005

This post will shows a step-by-step guide to backup and restore a database between two Microsoft SQL Server 2005 instances. By using backup, you can backup a database without interrupt any transactions on the database.

In the example below, I will try to backup a database from SQL Server 2005 and restore the database to another SQL Server 2005 Express Edition.

Backup a database

Now I will backup AdventureWorks database on BKKSQL2005 which runs Microsoft SQL Server 2005 to a file.

  1. Connect to source server. Open Microsoft SQL Server Management Studio and connect to BKKSQL2005.
  2. Right-click on the AdventureWorks database. Select Tasks -> Backup…
    Backup a SQL Server database
  3. On Back Up Database window, you can configure about backup information. If you’re not familiar these configurations, you can leave default values. Here are some short descriptions.
    1. Database – a database that you want to backup.
    2. Backup type – you can select 2 options: Full and Differential. If this is the first time you backup the database, you must select Full.
    3. Name – Name of this backup, you can name anything as you want.
    4. Destination – the file that will be backup to. You can leave as default. Default will backup to “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”.
    5. Click OK to proceed backup.

    Select source and destination to backup

  4. Wait for a while and you’ll see a pop-up message when backup is finished.
    Backup success pop-up message
  5. Browse to the destination, you’ll see a backup file (.bak format) which you can copy to other server for restore in the next step. Default backup directory is “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”.
    The backup file from SQL Server 2005 Server

Restore the database

Next, I will restore the AdventureWorks database from a file that I’ve created above to BK01BIZ001 which runs Microsoft SQL Server Express Edition.

  1. Copy the backup file from source server to destination server. I’ve copied into the same directory as source server.
    The backup file
  2. Connect to destination server. Open Microsoft SQL Server Management Studio Express and connect to BK01BIZ001.
  3. Right-click on Databases. Select Restore Database…
    Restore the database
  4. Restore Database window appears. On Source for restore, select From device and click [...] buttton to browse file.Select source device
  5. On Specify Backup, ensure that Backup media is “File” and click Add.
    Select the backup media to restore
  6. On Locate Backup File, select the backup file. This is the backup file that was created in Backup a database section and was copied to this server. Click OK. OK.
    Select the backup file to restore
  7. Back to Restore Database window.
    1. On Destination for restore, select “AdventureWorks”.
      Note: If you haven’t added the backup file on Source before (step 4-6), you won’t see the database name on Destination.
    2. On Source for restore, check the box in front of the backup name (in Restore column).
    3. Click OK.

    Select a destination database to restore

  8. Wait until restore finish and there’ll be a pop-up message notify.
    Restore success pop-up message
  9. Now you’ll see the restored database on the destination SQL Server.
    The restored database on SQL Server 2005

92 Responses to “How to backup and restore database on Microsoft SQL Server 2005”

  1. Lance

    You don’t happen to have a step-by-step for an incremental backup and restore by any chance?

    Regards from Indianapolis, IN

  2. linglom

    Sorry, I haven’t plan to write that. But for incremental backup, you can simply change backup type to ‘Differential’ on step 3.2 (Backup a database). When you restore, you’ll see the diffential backup set on step 7.2 (Restore the database).

  3. Mal

    Thanks!
    I have just started studying MS SQL. I had backups of databases that we useon the course and this guide heleped me a lot!
    The only thing I had to change is te .mdf and .ldf “restore as” path.

  4. ranjith

    could you provide me the details of taking file group backup and restoring into another database.

    Thanks in advance
    ranjith

  5. linglom

    Hi, Dfunky
    I’m going to write about that. Next week should be finishes. You can automate backup database operation by create a VB Script. Then, create a task schedule to execute the script at the specific time.

  6. safa

    Hi,
    I have a problem when I do the backup from my computer it is make success but when I move back file to another computer to restore it it give my the restore faild and this massege it appear :
    Restore failde for ‘ps\k’.(Microsoft.SqlServer.Smo)
    Additional information :
    system.Data.SqlClient.SqlError:Directory lookup for the file “C:\Program Files\Microsoft SQL Server\MMSQL\DATA\TEST.mdf” faild with the operating system error 3(error not found).
    (Microsoft.sqlServer.Smo)
    could you please help me to solve this problem and I thank you.

  7. linglom

    Hi, Safa
    I think you’re going to restore to the path that doesn’t existed. You can customize path in Options tab while you’re on Restore Database window – Restore the database file as section.

  8. sandeep

    Hi sir,

    what is the main difference between transaction logs and differential backups ? how the transaction log file grows?
    I m vry much confuse about mdf,ndf,ldf file?

    plz solve my problems.

    Regards

  9. aruna

    Hi,
    I m trying to restore the differential back up. but I got this message System.Data.SqlClient.SqlError: The tail of the log for the database “aruna” has not been backed up. Use BACKUP LOG WITH NORECOVERY to
    backup the log if it contains work you do not want to lose. Use the WITH
    REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite
    the contents of the log. (Microsoft.SqlServer.Express.Smo)

    i searched a lot in net ,but did not get it..
    plz explain how to do this back up.

    thanks

  10. pammi

    Hi

    i have deattach a database with the following command (EXEC sp_detach_db ‘test’, ‘false’) ,test as my db name.. now i don’t know where it locates…how should i attach a database file.. i know the command , but don’t know where it locates.
    EXEC sp_attach_single_file_db @dbname = ‘test’,
    @physname = ”

    plz answer
    thanks

  11. linglom

    Hi, Sandeep
    See this page SQL Server 2000 Backup Types and Recovery Models, even it’s an SQL Server 2000, the concept is the same.


    Hi, Aruna
    Have you read this page – MSSQLSERVER_3159? It explain about your error message and provides solution.

    Or try this solution. In Restore Database window, select Option tab and check Overwrite the existing database. Then, restore again.


    Hi, Pammi
    If you have SQL Server on drive C, the default location for the first instance is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA.

  12. Pankaj

    I have take backup of sql server 2005 successfully, while restoring it to same database or new database it is giving me error.
    Restore failed for Server ‘test’. (Microsoft.SqlServer.Smo)
    System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘tempAppDB’ database. (Microsoft.SqlServer.Smo)

    what could be the reason.

  13. Pankaj

    Restore failed for Server ‘test’. (Microsoft.SqlServer.Smo)

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    The file or filegroup “DomesticHRMS_log” cannot be selected for this operation.
    RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3219)

  14. linglom

    Hi, Pankaj
    In Restore Database window, select Option tab and check Overwrite the existing database. Then, try to restore again.

  15. vinod

    Hi,

    Is it possible to restore a .bak file that was created from SQL server 2008 to SQL server 2005??

  16. RLM

    It’s a good article! I got a question, is there a way to backup&restore all the databases in the server altogether rather than doing it individually? Thanks.

  17. aruna

    Hi Sir,

    Where the passwords and logins are stored in sql server 2005? Someone told me in master database(syslogins).

    But ,I found every system database contains syslogins . Exactly what syslogins stored?

    Thanks

  18. linglom

    Hi, Aruna

    Syslogins stores login account information. For SQL account, the password is stored in an encrypted format which is a one-way encryption so there is no way to decrypt it. For Windows account, there is no password stored (NULL).

  19. Kiran Khan

    Hi

    I have similar problem as Pankaj and I did check option to overwrite the existing database but still it wont let me restore the database , SQLserver 2005

    The file or filegroup “cars2_log” cannot be selected for this operation.
    RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3219)

    please help what I do?

    the backup is of my local host with different user and database name

    will appreciate urgent help

  20. linglom

    Hi, Kiran Khan

    Verify that you have chose “Restore Database” instead of “Restore Files and Filegroups”.

    If it doesn’t solve the problem, try to create an empty database with the appropriate name and then restore the backup with checked the “Overwrite the existing database”.

  21. Sulochana

    I am trying to restore a SQLServer 2005 backup file in SQLServer 2008. I am getting this error “Object cannot be cast from DBNull to Other DB Types”

  22. Marcus

    I hope the person who wrote these procedures is still around. I have a couple of questions.

    1. I created a test table on one SQL server and backed that server up. I then did the restore to SQL Server Express and when I looked for the test table on SQL Server Express after the restore was finished I didn’t see the test table. Why didn’t I see the test table?

    2. How do I set this up to automate or run on a schedule. Do you have instructions on how to do that?

    Thanks

  23. Inkus

    Hi,
    i have a question. I have three instnces on one server and I need to restore db from main instance to the other two, it worked perfectly for one but then for the second one it is saying that the ‘The process cannot access the file becuase it is using by another process’
    Please let me know what to do.

  24. linglom

    Hi, Inkus
    Have you try to restore the backup file at the same time on both instances? The error message tell that the file is in use, may be you are restoring for the first instance at the same time you are trying to restore for the second instance.

    Wait for the first instance to finish the restoration before start the second one.

  25. Inkus

    no i am not doing this at the same time one. it seems like i can only restore the back up once and when i do it another time i have to change the name of the db to something else and then it works. please let me know if you have any other sugestions.
    thank you

  26. M Border

    I am getting this error message. I have also tryed to add a backup to D drive and get the same message. I have followed your instructions but always get the same result. Can you help.

    System.Data.SqlClient.SqlError: Cannot open backup device ‘C:\Documents and Settings\Rental Department\Local Settings\Application Data\Microsoft\CD Burning\New Folder\Sunburst2007a.bak’. Operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Express.Smo)

  27. linglom

    Hi, M Border
    It seems that the SQL server doesn’t has permission on the locations that you have specified. Is the SQL server is on this computer or remote computer? If you want to backup to network path, you need to change the account that SQL server service runs under to a user account with the sufficient rights.

  28. Mary Border

    The SQL server is on this computer. I am very ignorant about sql server so I don’t have a clue about what to do.

    Thanks,

  29. Alex Rigg

    We’re using sql server management studio.. hopefully theres not a huge difference, I’ve backed up, and restored without an interim without issue. However when I try to restore it says

    “the tail of the log for the database has not been backed up.”

    I did a “full” backup. So I kinda expected the restore to work first time.

    Alex

  30. marina

    kindly help to search the manual of archiving or cleansing database in SQL Server 2005,
    i need it urgently,

    thank you

  31. DM Choudhari

    Hi ,
    I want to degrate DB MSSQL 2000 Enterpries Edition to MSSQL 2000 Standard Producation Enviroment,How to do, What is risk ?

    Need Help.

  32. linglom

    Hi, DM Choudhari
    I think there is no wizard help you downgrade on this situation. The best solution is to setup a new SQL Server 2000 Standard and restore databases from the Enterprise to it.

    Why don’t you upgrade to a higher version instead. It would be easier.

  33. ravi kore

    but i have done the same thing with .bak file which contains datewise backups and when i have selected last day/most recent backup to restore from .bak file i am getting following error :

    backup set holds the backup of database ‘testing’ other than existing database .

    please help me any body to resolve this problem

  34. Alex Rigg

    Note: My problem is solved, simply in the options you have to select overwrite existing backup…

  35. Mahesh Tryambake

    hi,

    I have updated table with update query,
    now i want to restore previous data.
    and we dont have database backup,

    can sql server Log file will for this ??????????
    if Yes, then how

    Thanks,
    Mahesh

  36. linglom

    Hi, Madan
    On backup, you can change destination path on step 3.4.
    On restore, see step 7, click on Options tab locate on left side. Then, you will see ‘Restore As’ where you can change destination path.

  37. nandakumar

    Hello ,
    somebody make read of the below problem and fix it.

    TITLE: Microsoft SQL Server Management Studio Express
    ——————————

    Restore failed for Server ‘CLIENT193\SQLEXPRESS’. (Microsoft.SqlServer.Express.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

    ——————————
    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘sa’ database. (Microsoft.SqlServer.Express.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476

    ——————————
    BUTTONS:

    OK
    ——————————

    TITLE: Microsoft SQL Server Management Studio Express
    ——————————

    Restore failed for Server ‘CLIENT193\SQLEXPRESS’. (Microsoft.SqlServer.Express.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

    ——————————
    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: Directory lookup for the file “C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\MedicalStore.mdf” failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Express.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476

    ——————————
    BUTTONS:

    OK
    ——————————

  38. Puneet

    Hi,
    I created a dummy database and then right cilck on dummy database. In source of restore I selected from database and from drop down list I selected Main database.
    But after doing this data was from my Main database was lost. Can anyone help me understanding how it happened and how can I (if possible) restore the lost data.
    Thanks

  39. linglom

    Hi, Puneet
    Can you describe more detail about your problem? Are you trying to backup a database and the database is lost?

    Normally, backup a database shouldn’t make any changes to the database.

  40. fibrecode

    Hi,
    I restored my database from my previous day’s backup and after restoration i found that i only had half the data on my restored database, can anyone explain how is that possible and how can i retrieve the remaining data?
    Below is a success message indicating that my DB was successfully restored:

    Processed 827792 pages for database ‘Test’, file ‘test_Data’ on file 1.
    Processed 2 pages for database ‘Test’, file ‘test_Log’ on file 1.
    RESTORE DATABASE successfully processed 827794 pages in 515.806 seconds (13.146 MB/sec).

  41. linglom

    Hi, Fibrecode

    There is no error on that message. Is it possible that your backup file is not complete at first? Was there any error message when you backup?

  42. fibrecode

    Hi Linglom,

    thanks for your quick response, I think the backup file was complete and there were no error messages, but i’ll double check that today…

  43. fibrecode

    Hi all, I just realized that i had restored a wrong backup tape that didnt have the complete data…I reloaded/restored the right tape and everything was fine…

  44. Janet

    Hi all. Need help in restoring database from sql 2005 down to sql 2000. Can someone give me the steps on how to do it? Thanks!

  45. John

    hii all….here i’m facing a problem, i want to take backup of a sqlserver 2000 database from vb.net(VS 3.5) application.but i’m only able to take backup in the backup folder under MSSQL folder, but that’s not what i want.i want to take the backup to my desired location/folder in the system. Please help me great guys, how do i do this now by vb.net coding? send your replies to my given mail-id. Any help is appriciated in advance, thank u all

  46. Tony

    Hi, on my old server HD room are limited and one of the steps updating my software req. SQL 2005 server prior to move to new 2008 R2 on new server, so my Studio did not install and is any way i can backup my database FULL without studio(comand promt) without srewing up? Thanks

  47. HOSAIN

    HI I have a problem please help me. I have a database that is belonging to my website. it is located on a net.Yesterday I Got a backup and Downloaded it in my PC. I want to restore it in my Pc’s Database But I can not.
    I get This Error:

    TITLE: Microsoft SQL Server Management Studio
    ——————————

    Restore failed for Server ‘SERVER2′. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

    ——————————
    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘alborzdb’ database. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

    ——————————
    BUTTONS:

    OK
    ——————————

    please Help .
    Thanks

  48. Manisha

    I want to take sql backup and restore it is there any free software available for it or I want that software plz give me path

  49. jay

    hi,
    I am trying to restore back up of a database from one instance to other instance as per above steps.
    but in step 6 in my case it didn’t show any back up file in the list.
    I can restore my back up by typing back up file name but i want to know why it is not showing me in list(locate back up file).

    if anyone can address that issue please point it out.

    i m using same version of sql server instance.

  50. jay

    i know i should work fine, but in my case it is facing above issue. so if u know what is causing it or where i might doing something wrong, guide me.

    thank you

  51. ahmed

    hi, through backup process ok button is dimmed , i have to write file name and when backup complete the problem is file without extention (****.bak) and after taht restore not working

    pls help me

  52. anth0ny989

    Eyy…
    after i finished back-up,
    the file extension of backed-up is not .bak
    i recieve file with an extension of .file which is not restorable in mssql server management studio express.

    anybody please help :))

  53. linglom

    Hi, Jay
    Is your backup file has .bak extension? On Locate backup window, it will show only file that has extension as file of types column which is .bak.

    Hi, Ahmed
    Did you mean the OK button on step 3 in my tutorial? If it is dimmed, it means that you may mis-configure on the window.

    Hi, Anth0ny989
    It could be any error while backing up, try to check Application event log to see if there is any error and post it here or Microsoft forum.

  54. Shukla Ajay

    Hi when i want to taking back of my database,i have face so many problem but you have to describe very simple way thanks very much
    thanks

  55. Maninder

    Hi when i want to taking back of my database,i have face ——–

    System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘dbabhidir19′ database. (Microsoft.SqlServer.Express.Smo)
    plz send me the solution of that as soon as possible

  56. Brian

    hi
    when i want to restore backup of my database,showing this error

    Restore failed for Server ‘Brian-BWG-PC’. (Microsoft.SqlServer.Smo)

    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)

    what must be do .
    please send a message to solve that soon.

  57. suhas

    Hi,
    I want restored my database back and after restoration i found that Restore failed for Server ‘HOME-B00DD89814′. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

    ——————————
    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: The media set has 3 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476
    NOTE–I USE SQL 2005 server.(BACK UP IS TAKEN FROM COLLAGE COMPUTER).

    plz send me the solution of that as soon as possible. Yours sincerely.
    suhas
    NOTE == PLZ HELP ME URGENTLY…

  58. linglom

    Hi Suhas,

    When you restore, have you select all backup files?
    If you’re not sure, I suggest you try backup again. And ensure that on step 3, you have only 1 destination file. If you have more than 1 destination file (for example, 2 files), you have use both files when you restore the database.

  59. satish

    this is my first time to loging and restore backup of database on sql server this doc is very helpful for the backup restoration

    many thanks,

    satish

  60. Rupali

    Hi,
    Could someone help me how to restore a database when there is no back up?
    Eagerly waiting for a response.
    Thanks!

  61. Kudeep

    Hi Rupali,
    If you don’t have any back up ,then what thing from database you want to restore?
    Please describe your problem.

  62. Bhosz

    I need to do something like:Set AliasName for dbo.RealNameso sql query lieseelkct * from AliasName.dbo.MyTablewill workI think many people need to do itThank youMassimo Bizzarro

  63. Ramanan

    i done successfully backup but restore process finally occur error
    System.Data.SqlClient.SqlError: The media set has 3 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)
    please give the solution…

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>