How to backup and restore database on Microsoft SQL Server 2005
MS SQL Server, Windows January 12th, 2008Introduction
This post 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 backup a database from SQL Server 2005 and restore the database to another SQL Server 2005 Express Edition.
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 3: Export Data Wizard Export Data Wizard On Part 1: Introduction, I mentioned about error messages when you try to restore a database from...
- 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...
- [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...
- Automate Backup Database on SQL Server, Part I: Create VB Script Introduction Last year, I wrote an article about backup and restore database on SQL Server with Microsoft SQL Server Management...
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.