How to backup and restore database on Microsoft SQL Server 2005
MS SQL Server, Windows January 12th, 2008Introduction
This post will give you a brief introduction of SQL Server backup. As we know, SQL Server backup software is springing up nowadays. Take EaseUS Todo Backup Advanced Server as an example, it’s an all-in-one SQL server backup software for MS SQL Server 2000/2005/2008, exchange server and server 2000/2003/2008, and provides proven database backup solution for Server administrators.
But in the example below, I will show a step-by-step database backup from SQL Server 2005 and restore the database to another SQL Server 2005 Express Edition using Microsoft SQL Server Management Studio.
Step-by-step
Backup a database.
Now I will backup AdventureWorks database on BKKSQL2005 which runs Microsoft SQL Server 2005 to a file.
- Connect to source server. Open Microsoft SQL Server Management Studio and connect to BKKSQL2005.
- Right-click on the AdventureWorks database. Select Tasks -> Backup…
- 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.
- Database – a database that you want to backup.
- Backup type – you can select 2 options: Full and Differential. If this is the first time you backup the database, you must select Full.
- Name – Name of this backup, you can name anything as you want.
- 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”.
- Click OK to proceed backup.
- Wait for a while and you’ll see a pop-up message when backup is finished.
- 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”.
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.
- Copy the backup file from source server to destination server. I’ve copied into the same directory as source server.
- Connect to destination server. Open Microsoft SQL Server Management Studio Express and connect to BK01BIZ001.
- Right-click on Databases. Select Restore Database…
- Restore Database window appears. On Source for restore, select From device and click [...] buttton to browse file.
- On Specify Backup, ensure that Backup media is “File” and click Add.
- 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.
- Back to Restore Database window.
- 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. - On Source for restore, check the box in front of the backup name (in Restore column).
- Click OK.
- On Destination for restore, select “AdventureWorks”.
- Wait until restore finish and there’ll be a pop-up message notify.
- Now you’ll see the restored database on the destination SQL Server.
Related post
- Restore Database From SQL Server 2008 to SQL Server 2005, Part 2: Generate SQL Server Scripts Wizard Generate SQL Server Scripts Wizard On Part 1: Introduction, I mentioned about error messages when you try to restore a...
- Restore Database From SQL Server 2008 to SQL Server 2005, Part 1: Introduction Problem When you restore or attach a database which is created from SQL Server 2008 to SQL Server 2005 or...
- Restore Database From SQL Server 2008 to SQL Server 2005, Part 3: Export Data Wizard Export Data Wizard On Part 1: Introduction, I mentioned about error messages when you try to restore a database from...
- [Solved] System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘dbName’ database Problem You have backup a database on SQL Server 2005. Then, you try to restore the backup file on an...
- Backup and Restore database on MySQL using phpMyAdmin MySQL is one of the most popular DBMS and widely used by many websites as back-end database for storing content....
Related posts:
January 15th, 2008 at 5:07 am
Is it possible to restore to SQL Express from a SQL backup?
January 15th, 2008 at 9:04 am
Yes. The example above is restored to SQL Express Edition.
January 17th, 2008 at 7:38 pm
hi…… please send me inventory project
January 17th, 2008 at 11:15 pm
Hello, Priya
What do you mean “inventory project”? I don’t get it.
May 28th, 2008 at 10:50 pm
Thanks for the guide!
As an alternative and much more faster backup way I can suggest using LiteSpeed.
After implementing this solution we noted a decrease in server downtime while restoring databases more quickly. The space required for the backup archives were reduced as well because of using high levels of compression. For example, the backup size and the time needed for backup is about several times less than using native tools.
May 29th, 2008 at 3:53 am
You don’t happen to have a step-by-step for an incremental backup and restore by any chance?
Regards from Indianapolis, IN
May 30th, 2008 at 10:05 am
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).
October 20th, 2008 at 10:49 pm
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.
December 24th, 2008 at 2:11 am
could you provide me the details of taking file group backup and restoring into another database.
Thanks in advance
ranjith
December 24th, 2008 at 9:53 am
Hi, ranjith
I think the steps should be similar to the post above. Did you encounter any problem?
January 27th, 2009 at 4:54 am
Hi,
Is there a way this can be automated?
Either via scripting or SQLMGMT Studio?
Thanks
January 27th, 2009 at 10:04 pm
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.
January 28th, 2009 at 4:59 pm
Good article
March 9th, 2009 at 6:04 pm
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.
March 18th, 2009 at 9:24 am
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.
June 6th, 2009 at 12:03 pm
very very helpful site for me.
thanks
June 7th, 2009 at 12:48 pm
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
June 7th, 2009 at 5:34 pm
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
June 8th, 2009 at 12:53 pm
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
June 18th, 2009 at 2:16 pm
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.
June 19th, 2009 at 8:04 pm
Thanks a lot
July 1st, 2009 at 3:52 pm
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.
July 1st, 2009 at 4:00 pm
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)
July 2nd, 2009 at 9:33 am
Hi, Pankaj
In Restore Database window, select Option tab and check Overwrite the existing database. Then, try to restore again.
August 6th, 2009 at 1:39 pm
Hi,
Is it possible to restore a .bak file that was created from SQL server 2008 to SQL server 2005??
August 11th, 2009 at 10:38 am
Hi, Vinod
No, you can’t restore the backup file to an older version of SQL Server. But there are some alternatives, see Restore Database From SQL Server 2008 to SQL Server 2005, Part 1: Introduction
September 10th, 2009 at 8:31 am
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.
September 23rd, 2009 at 2:10 pm
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
September 25th, 2009 at 11:38 am
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).
October 8th, 2009 at 2:52 pm
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
October 13th, 2009 at 11:12 am
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”.
October 22nd, 2009 at 8:49 pm
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”
November 6th, 2009 at 9:53 pm
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
November 9th, 2009 at 11:49 am
Hi, Sulochana
I’m not sure about this error message.
Hi, Marcus
1. Have you refresh it after the restoration? If there is no error message, the restored database should be there.
2. I wrote an article this. See Automate Backup Database on SQL Server, Part I: Create VB Script
November 17th, 2009 at 2:24 am
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.
November 17th, 2009 at 10:02 am
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.
November 17th, 2009 at 9:08 pm
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
December 5th, 2009 at 3:45 pm
hi,
it is very help full to me
January 7th, 2010 at 4:50 am
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)
January 7th, 2010 at 11:32 am
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.
January 7th, 2010 at 10:09 pm
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,
February 6th, 2010 at 12:34 am
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
February 19th, 2010 at 11:17 am
kindly help to search the manual of archiving or cleansing database in SQL Server 2005,
i need it urgently,
thank you
February 23rd, 2010 at 11:35 am
Hi, Mary Border
Have you solve the problem? If not, I want you to change the backup destination to something like C:\Backup\ (Created new one) and give the permission “Full Control” to Everyone. And try to backup again.
Hi, Alex Rigg
For the error message, See [Solved] System.Data.SqlClient.SqlError: The tail of the log for the database “dbName” has not been backed up
February 24th, 2010 at 9:35 pm
Hi ,
I want to degrate DB MSSQL 2000 Enterpries Edition to MSSQL 2000 Standard Producation Enviroment,How to do, What is risk ?
Need Help.
February 26th, 2010 at 9:42 am
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.
March 18th, 2010 at 12:58 pm
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
March 22nd, 2010 at 3:51 pm
Hi, Ravi Kore
See [Solved] System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘dbName’ database
March 22nd, 2010 at 4:21 pm
Note: My problem is solved, simply in the options you have to select overwrite existing backup…
April 20th, 2010 at 9:02 pm
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
April 22nd, 2010 at 4:00 pm
U made me restore and backup database so easy
Thanks mukky
April 30th, 2010 at 9:57 pm
Nice article. try this tool to make backups (also scheduled backups) SQL Backup and FTP (http://sqlbackupandftp.com). It’s easy to use freeware application.
July 3rd, 2010 at 12:58 pm
How to change path backups & restore
July 15th, 2010 at 10:31 am
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.
September 28th, 2010 at 5:17 pm
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
——————————
October 9th, 2010 at 9:14 pm
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
October 11th, 2010 at 9:12 am
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.
October 28th, 2010 at 11:13 pm
Thanks for the helpful post. Worked perfectly!
Matt: http://www.jabbergroups.com
November 11th, 2010 at 9:37 pm
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).
November 12th, 2010 at 9:39 am
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?
November 12th, 2010 at 1:26 pm
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…
November 12th, 2010 at 5:44 pm
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…
November 19th, 2010 at 6:30 pm
hi thank u so much for helping me to take backup and restore of database
November 25th, 2010 at 1:13 pm
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!
December 9th, 2010 at 9:30 am
Agree with Alex – SQLBackupAndFTP is the best
December 11th, 2010 at 3:00 pm
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
December 18th, 2010 at 9:35 am
Hi, Janet
See How to restore SQL 2005 DB to SQL 2000.
Hi, John
Try to apply this script to your application, Automate Backup Database on SQL Server, Part I: Create VB Script.
January 7th, 2011 at 11:58 pm
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
January 14th, 2011 at 7:44 pm
hi,
thanx dear…
really to much useful..
January 27th, 2011 at 12:53 pm
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
January 27th, 2011 at 2:50 pm
Hi
when I posted my problem ,I search other part of restore Database windows and finally i solve my problem.
I will post my solution maybe it helps others
http://www.4shared.com/file/kXoXzfgp/Error.html
thank you
February 15th, 2011 at 2:28 pm
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
February 24th, 2011 at 3:58 pm
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.
March 3rd, 2011 at 2:48 pm
it working nice
thks
March 6th, 2011 at 6:15 pm
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
March 7th, 2011 at 5:10 pm
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
March 17th, 2011 at 5:04 pm
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
)
March 31st, 2011 at 10:52 am
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.
April 13th, 2011 at 2:27 pm
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
April 28th, 2011 at 5:50 pm
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
May 25th, 2011 at 1:14 pm
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.
September 15th, 2011 at 9:38 pm
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…
September 19th, 2011 at 12:48 pm
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.
October 13th, 2011 at 5:48 pm
hai ,
i have a error message during restore.like the database in use.what to do?
January 12th, 2012 at 7:27 am
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