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.
Step 1: Retrive the Logical file name of the database from backup.
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)
535 Comments. Leave new
Hi All,
I’ve SQL Server 2000 and I’m trying to restore all production databases (around 50) to UAT environment, I’ve stored proc. where I create a cursor for sysdatabases after filtering out system databases then I iterate thru all databases, find relevant backup set and restore it. It works fine when I execute that stored proc in query analyser but when I execute same from SQL agen it fails after restore 8-10 databases. Both SQL Server And SQL Server Agent running under a domain account which is part of domain admin group. Can any one help with this.
Regards,
Ismail
HI,
I TRY TO RESTORE DATABASE I JUST FLASH ERROR “RESTORE DATABASE is terminating abnormally.”SO WHAT IS SOLUTIONS.
PLS GIVE ME SOLUTIONS .
Thanks for this! Very easy to follow instructions that allowed me to restore database back-ups taken from a hosted environment and restore them locally so I could test SQL Report writing with Crystal Reports.
I had read other blogs and forums but did not find easy to follow instuctions like these. Worked the first time through.
Thanks Again!!
Hi!
I have nos. of full backup for different days of week on LTO Tape. Now, I want to restore one particular backup from this LTO Tape. LTO tape contains 5 nos. of different days backup. How can I restore selected backup from TaPE.
Pls. provide some solution.
hi pin…..
i need a answer……
using this query………
restore database padmin from disk=’C:\manish\padmin.bak’
i got following errors
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name ‘C:\Program Files\Microsoft SQL Server\MSSQL\data\padmin.mdf’ may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File ‘padmin’ cannot be restored to ‘C:\Program Files\Microsoft SQL Server\MSSQL\data\padmin.mdf’. Use WITH MOVE to identify a valid location for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name ‘C:\Program Files\Microsoft SQL Server\MSSQL\data\padmin_log.LDF’ may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File ‘padmin_log’ cannot be restored to ‘C:\Program Files\Microsoft SQL Server\MSSQL\data\padmin_log.LDF’. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
and when i use this one……
restore database padmin from disk=’C:\manish\padmin.bak’
WITH
MOVE ‘padmin’ TO ‘F:\Program Files\Microsoft SQL Server\MSSQL\Data\padmin.mdf’
MOVE ‘padmin_log’ TO ‘F:\Program Files\Microsoft SQL Server\MSSQL\Data\padmin_log.ldf’
i got………
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ‘MOVE’.
what should i do????????????
Hi
i want answer of the following question
Whenever i restore database using the query
RESTORE DATABASE ID FROM DISK =’D:\ID1.0.BAK’
I GOT FOLLOWING ERROR
Msg 3102, Level 16, State 1, Line 1
RESTORE cannot process database ‘ID’ because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
@ Manish Gupta,
Books online is helpful most of the times,
If you see books online, there is a simple example explained, I am copying the same example below,
RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1
WITH NORECOVERY,
MOVE ‘MyAdvWorks’ TO
‘c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf’,
MOVE ‘MyAdvWorksLog1’
TO ‘c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf’
RESTORE LOG MyAdvWorks
FROM MyAdvWorksLog1
WITH RECOVERY
Try using similar script for your database.
You are missing NORECOVERY option for data file and RECOVERY Option for log file.
Recovery option for log file is default option, so you need not specify with Recovery, but I think you do need to specify with NORECOVERY. For more info, please refer to books online.
@Mayur
Looks like you are doing two mistakes. This is what I could say looking at the error message you provided.
1. You are using database ID and you are trying to restore the same database (i.e. Database Name : ID). That is why error message says, use master database instead of ID database and execute your restore script.
2. Even if you use master database and execute that restore script that you provided, you will see second error message, it is saying, Database ID is already in use, there is already a database existing on Sql server with name ID. you cannot restore ( which internally created a new copy of the database).
you can do either one of these two things,
1. Use with Replace option, and this will replace existing files used by the database “ID”, you will loose your present copy of the database.
2. First drop the database, “ID” and execute your restore script.
Important Note : Make sure you know what you are doing, performing any one of the above two tasks will result is data loss, you will loose your current copy of the database.
Make sure you take backup of the database before you execute your scripts.
If you have any question, please post it here. Its always good to ask and learn, instead of doing some silly and feeling guilty later on.
IM.
I have mirror database on separate server. I m using Microsoft 2005 standard edition and mirror database to another server. These two servers are at same location, now I need another server at remote side and want to add remote server in mirror. Is it possible to mirror database in two separate server. There may be possible by log shipping…How can I complete procedure for log shipping or mirroring to secure my database at remote location.
So, Setup wil be like Primary Server -1 ,Seconday ServerA both at current lockation and Secondary ServerB at remore location.
Devang
Hi Pinal Dave
Thanks for all the info on this site. My colleague deleted rows off a table that contained a lot of settings for our finance system. We do daily full backups and daily transaction log backups. Recovery model is full. 30 mins after she deleted the rows both the full backup and transaction log backups ran. I’ve tried doing a point in time restore using Management Studio, but I only seem to get the database restored to a point after her deletes. Is there any way for these changes to be rolled back?
Regards
FJ
Hi Pinal Dave,
My .LDF (file) eats most of the spaces of my drive. How do i BACKUP LOG file? If i do this will it free spaces on my drive?
Thanks in advance.
Regards,
Ching
I have a question for you Pinal.
I make regular full backups of my database on the same server. I want to be able to pick out the most recent backup and restore it to a different database on a different server. Also I want an e-mail alert when the backup was success or failed.
Hi pin,
I backup database with full recovery model, My database have filegroups, I can restore database but restoring filegroup generating errors.
Please help in this regard,
Hi Pinal,
I have a database with 12 secondary files, i alwayes delete the data in the secondary files. I want to delete the secondary files physicaly from the storage. Is it possible to do it maybe to change something in the master or msdb database, did you have any experience like this.
Thanks in advance.
I have a backup (DB_143073.bak) from SQL Server 2005 Enterprise, that I wish to create a new database from in SQL Server Express 2005.
Thing is, after running:
RESTORE FILELISTONLY
FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DB_143073.bak’
It shows I have 3 logical files:
DB_143073
DB_143073_log
sysft_DB_143073
and if I use the Wizard, I get an error, because the directory C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\DB_143073 does not exist.
Some help on this subject would be very appreciated!
@Chris
When SQL Server takes backup of any database, its also stores some header information and location of the backup.
For example,
you took backup of Database A on Server A,
Database A has its .MDF on D:\Program files\MSSQL\Data\Database_A_Data.mdf.
and .LDF file on D:\Program files\MSSQL\Data\Database_A_log.ldf.
Location of these files will be saved in backup.
Now, when you try to restore backup on a different server or same server, SQL Server by default, try to create a database with its log and data file on the same location where it took backup from.
Case 1: If you already have DatabaseA online, and try to restore the backup on the same server where you took backup, then you will get an error.
Error Message : Database files are being used cannot restore backup……
Case 2: If you try to restore backup on different server, and if this location does not exists,
Location : D:\Program files\MSSQL\Data\
Then sql server will give throw another error.
Error Message : location does not exists …..
In you case, you are getting second error message,
Resolution : As posted in this post,
RESTORE DATABASE YourDB
FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,
MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.mdf’
You need to use key word : With Move while restoring,
This will point your backup to restore files at new location, rather than restoring at old location which does not exists in new server.
If you are trying to restore backup, from SQL Server Management tools,
1. Right Click Databases
2. Click Restore Database
3. Write New Database Name.
4. Click From Device
5. Click Browse Button and point to location where you have your backup file saved.
6. Click OK.
7. Check Backup Name. ( Under Select Backup Sets to Restore)
8. Click Options ( Important Step)
9. Under Restore As, Give new file location where you want to create .mdf, .ndf and .ldf files.
You can either use browse button to point out to a location you want to create data and log file, or if you know a location already, just paste into that box.
10.Click OK.
Should work.
~ IM.
This looks like the solution I am after, thank you very much Imran, and thank you too Pinal for such a wonderful source of reference.
Just one question. What is the significance of the ‘sysft_DB_143073’ directory in the BAK file, and should it or can it be ignored? Not really sure what FT is all about, except I know it’s an abbreviation for ‘Full Text’ and appears to have some relevance to conducting a search.
I also highly presume your script will ignore it, unlike the wizard which won’t.
i need to update values of multiple columns with single value, i know the following query
“update ‘tablename’ set column1 = value1 , column2 = value1, column3 = value1 where id = xxxxx ”
but is there any other sql command which will reduce my work of everytime writing the value1 thrice. i need a query which will update all the the three columns with specifying the value1 only once.
Hi pinal,
Unfortunately I Have deleted 1 table from my database.
I have backup for that database before 15 days.
Please help me to restore the data.
@Vijay,
Vijay, if you just want the structure of the table and you think you created that table 15 days ago, then you can always find that table in your 15 days old backup. If you want Table with data in it, then perform the following steps.
If your database is in full Recovery mode /Bulk logged Recovery mode then follow steps 1 – 4, other wise forget about the table.
Step1 :
If your database is in full Recovery mode /Bulk logged Recovery mode, then take Transactional Log Backup Right now.
Step 2: Restore your Full backup which you took before 15 days with NO RECOVERY Option. Give Database a new name. Just to keep the current database running.
Step3: Restore any other transactional log backup that you took after you took this full backup, meaning you said you took full backup before 15 days, if you took any transactional or differential backup, restore all backups in ascending order, one by one, all with NO RECOVERY Option. Do not apply the latest transactional log backup ( that you took just now).
Step4 : Now You apply latest Transactional log backup, with RECOVERY, but you will use point in time recovery. Meaning you will ask Sql Server to stop restoring at specific time. ( This is the time when you dropped table). You need to restore the database untill that time.
Thats it, As soon as restore completes you will have your table in the database. BUT, you will loose all data that was entered in the database after table has been dropped , this is because you asked Sql Server to not restore the rest of the log backup.
If you have any other database which is a copy of this database ( DR Set up through Log Shipping or Database Mirroring or Replication ), you can try if table exists in other database ( There are more chances that it will be deleted in DR also, but still you can try, you could be lucky enough).
~ IM.
Hi
I have doubt can we restore sql server 2008 database backup into sql server 2005.
With regards
Bharath kumar