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
I have doubt can we restore sql server 2008 database backup into sql server 2005.
Thanks in advance
With regards
Bharath kumar
Am trying to restore a database named TestFulla.Bak to a database Test_2 with the following command
RESTORE DATABASE Test_2
FROM DISK = ‘D:\Ravindran\SQL Bakup\TestFulla.bak’
WITH MOVE ‘Test_Data’ TO ‘D:\Ravindran\SQL Bakup\Test_Data.mdf’,
MOVE ‘Test_Log’ TO ‘D:\Ravindran\SQL Bakup\Test_Log.ldf’;
but am getting this error message
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing ‘Test_2’ database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Help me..!!
The restore process cannot restore the database from the backup file because there is already a database called Test_2 present on your SQL 2005 server. I tried deleting the Test_2 database then i restored it successfully…!!!
Hi!
I m using LTO Tape for backup my database. I m using maintenance schedule plan to take backup of my database everyday. so, i hv nos. of backup on my LTO TAPe media, now problem which I suffer is HOw can I Restore particular days database from LTO TAPE.
LTO tape having nos. of backup which were executed by maintenance plan.
Pls. give command qry for the same.
Thx.
Normally in IT world we have a naming method . Especially for Backup files . Through programmes when we take backup files with name like 04072009PRODDB.
So when you try to restore the back up files you can select the DB files from this list.
In your case you did it through ur MP.
How did you did it ?
Used any Custom Script ?
How will use restore cmd without knowing the filename ?
@NK Menon.
The way you name your backup, the same way you can also read name of that backup file and use that name in restore command.
You said, backups are named in a specific fashion, I am sure some one must have written a T-SQL to get backup name in that required format.
Use the same logic, to read backup file names when restoring databases.
In Short, you need to use dynamic SQL, in which name of the backup file changes as per the logic (same logic, with which you name your backup files).
~ IM.
RESTORE FILELISTONLY
FROM DISK = ‘D:BackUpYourBaackUpFile.bak’
GO
Hello pin,
i try to restore the sql server database and getting a lot of error .
actually the location of bak file is totally different to my system location
RESTORE DATABASE [AdventureWorksDW]
FROM DISK = ‘D:AdventureWorksDW.bak’
WITH MOVE ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAAdventureWorksDW.mdf’ TO
‘D:Microsoft SQL ServerMSSQL.1MSSQLDataAdventureWorksDW_Data.mdf’,
MOVE ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAAdventureWorksDW_Log.ldf’ TO ‘D:Microsoft SQL ServerMSSQL.1MSSQLDataAdventureWorksDW_Log.ldf’
and i get error-
Msg 3154, Level 16, State 4, Line 3
The backup set holds a backup of a database other than the existing ‘AdventureWorksDW’ database.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
So please give the some good suggestion regarding to this error .my lot of work is pending .
Thanks And regards
Pratyush singh
Dear Sir,
We have 2 servers named Webserver2 & Erpserver hosting the same ERP Application using SQL 2000 sp4. Webserver2 runs on SBS 2000 whereas Erpserver runs on SBS 2003.
My Problem is that at a given time we use only one server.
or eg. the path to access the data is http://webserver2/sleeknew/master/login.aspx.
Each time the Webserver2 has a technical snag or virus problem (virus problem is very frequent, malwares are regularly found on the server, we use AVG Network edition with anti syware & malware), the server goes off the network or stop responding.
Now the only option left is to backup the database & restore it on the other server (erpserver) & start working again. The webserver2 will now have to be formated & all the reqd. software need to be installed again.
So, to save on this long procedure, if we are using the appl. http://webserver2/sleeknew/master/login.aspx. on webserver2 using the database hosted on webserver2. Can the database on erpserver also be simultaneously updated on erpserver while using the appl on webserver2. In this manner the shift from one server to another can be instant.
So, pls. help me out of this situation. Your suggestions will of great help.
Thanks & Regards
Jay
Hello Sir,
Please help me with my question. I have server-A and server-B and I’ve backup both their database servers. I used database backup from server-B to restore database of server-A. Since database of server-A has different set of user accounts, is it possible to restore these user accounts using the database backups that I’ve created earlier in server-A? Also, please suggest a better process than what I’ve done.
Thank you.
@Vlad.
Seems there is small confusion. You need be very specific about Users and Logins.
Users exists at database level and Logins exists at instance /server level.
Login is to enter into SQL Server, and user is to enter into database.
Logins information is stored in master DB. and users information is stored in individual databases.
When you restore a database backup of a database from Server B to Server A, you will still have all those users from Database on Server B, in new database that you just created on server A. Because users are defined inside database.
Only thing you will see is users will be orphaned, either because there does not exists any login for those users in Server A, or SID for logins of few users does not match with SID of logins on Server A.
I would suggest, Script out all your logins From Server B first and then execute that script on Server A.
Or
Exec Sp_Change_Users_Login ‘Auto_Fix’ /’update_one’
TO fix orphaned users.
~ IM.
Dear Imran Mohammed..
One of your reply to chirs help me to restore my Database which was in .dat file.
After searching many sites I got the exact answer posted by you….
Good explanation of the location funda….
Thanks & Regards,
Mohammed Pasha.l
I am responsible for an productional SQL database which is
located 2000 miles away. I can login into the Windows 2003
server remotely with a local administrator account, but I do
not have a domain account there.
I wish to build a copy of the database locally and do
development work.
I cannot copy the SQL database, which will deattach it,
put it in Single User mode, copy the database and reattach it.
I was thinking of restoring the database on my development
server locally by using backup files only and then figuring
out how to fix the broken logins.
Can I restore an SQL database to a new SQL Server where it
does not yet exist?
Rick
I forgot to mention above that the vesion is SQL Server 2000.
The 2 SQL Server 2000s involved do not have any domain
network connections, but access by copying files across
mapped folders.
This seems to eliminate some of the nice Wizards, which
require direct domain connections.
Thank you for any help.
Rick
Hi!, I heard about you in a coversation of my friends, now I have a question to ask you :-) , I have been developed a project in vb6 with sql server 2000 database, make backups but, now, I have to restore it!!, my problem is the version of sql server, I mean, in my computer have an O.S. in english, so, c:\program files\… but I need to restore backup generated from an O.S. in spanish, c:\archivos de programa, the error is :
the fisical name c:\archivos de programa\……Base.mdf maybe incorrect…
Help me please!!!!… sorry for the orthography errors… I hope you understand what I mean… thanks!!
thx for the script.
i want to share script for backup too
BACKUP DATABASE [database] TO DISK = ‘[file location]’ WITH INIT, NOUNLOAD, NAME [backup name], NOSKIP, STATS = 10, NO FORMAT
You are awesome!!! Worked flawlessly and saved me a bunch of time.
hii Mr.Dave
hi had tryed u r code
restore FILELISTONLY
FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\gohil.mdf’
but it give me this error
Cannot open backup device ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\gohil.mdf’. Device error or device off-line.
RESTORE FILELIST is terminating abnormally.
after this i also tryed next to make db in single user tht was successfully compiled.
and next code
RESTORE DATABASE gohil
FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\gohil.mdf’
WITH MOVE ‘E:\gohil.mdf’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\gohil.mdf’,
MOVE ‘E:\TC\gohil.ldf’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\gohil.mdf’
which on efor the db restore further it gives me error
—Cannot open backup device ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\gohil.mdf’. Device error or device off-line.
so wht i have to do??? now
please any one help me for the db restore
can any one help me for db backup using query??
Hi their,
Upon doing the said script for restoring database i got the following error :(
Msg 3102, Level 16, State 1, Line 10
RESTORE cannot process database ” 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 10
RESTORE DATABASE is terminating abnormally.
any idea?
thanks