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 Pinal
I would like to know how to take backup in sql server 2005 from shared server. I have knowledge about ordinary backup and restore but from web server there is no option. Your help would be helpful.
You can take a backup in shared server as you do in the local server
Is there any way to restore the Stored Procedure in SQL 2005 ??
Thanks,
Yash
You can generate script of the procedure and run it in target server
Hi,
How to kill all active connections to DB using SQL script…
Hi All,
I am restoring the database using command line (SQL Express), when the restoration is successful will get the message as restore database successfully processed.
If the restoration fails for any of the reason, it gives the message as restoration completed abnormally and it gives the “state” option/parameter also.. here how do i capture this state option ?
Please help me ..
Thanks,
Shweta
hi All
I want restore file database of sql 2005 express into sql 2005 developer.
But it not execute. How can I use it? Please help me! Thanks
Post the actual code you have used. Also did you get any error?
Ok thanks I have executed it.
hi,
I want restore database of sql 2005 while doing this i got following error:
TITLE: Microsoft SQL Server Management Studio
——————————
Restore failed for Server ‘HOME-B00DD89814’. (Microsoft.SqlServer.Smo)
For help, click:
——————————
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:
It means that the backup file has three related files. You need to specify all these three files to restore it properly
than for reply .
sir i copy data base from my friend laptop.then i try to restore it on desktop.i first copy the data base to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup.
as mention i go throw all restore procedure. according to me we have only one database whose backup i have copied. but for safety i save it on different drives (on my desktop) then after error mas i delete all .kindly plz help me urg.
plz help me.
hi,
thanks for your 1st mail.
sir is it possible that only one database has three related files.
how to check that how much files are related to each other and how to take such database file while restoring.
sir plz reply me early as possible .i am student not having much about this sql server.
Try this code to see the number of files
Restore filelistonly from disk=’backup_path_and_file’
sir ,i execute this specid\fuc query. there i m getting DHW ma database file and its log file havig properties as TYPE: D for database and L for Log .
plz guide me further , waiting for ur Reply..thanks in advance!!
excuse me sir
how to restore multiple backups at once using sql script(t-sql)
Hello SIr,
thanks for your assist,
May you tell me,
How to schedule backup time for database ?
ex:- suppose i would like to backing up my database 2 times per day, at 10 am and 6:00 pm , how should i schedule timing for database backup,
You can do it via SQL Server Agent. Create a new job and schedule it as you wish. The schedule section provides an access to schedule it. Goto section occurs every and select 8 and specify starting and ending time as 10 am and 6 pm respectively
I want to restore/replace the new DB on any other server’s old DB with same name
1 Copy the backup file into new server’s location
2 Use this command in new server
Restore database db_name from disk=’file_path’ with replace
Hi All,
Is possible create a task on SQL 2008 R2 for the DB can be restore automatically?
Any Advice ?
Regards
Yes you can make use of a job and schedule it to run periodically.
Hello, How can I restore 100 + Database with just one script ?
You can have all of them in a folder and write a query to get file names using xp_cmdshell and dynamically build restore database statements and execute them
Hello, I need a little help with a restore process,
I have this script in my system:
RESTORE DATABASE [BasePrincipal] FROM DISK = N’C:\Sistema\backup.bak’ WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
It work perfect in Windows XP, but not in Windows 7…
The message is:
Error al buscar el archivo ‘C:\Archivos de programa\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Basesistema.mdf’ en los directorios, error del sistema operativo: 3(failed to retrieve text for this error. Reason: 15105)
What can I do?
Thanks for your help!!
Hi
I have a Sql server .bak file created in Sql 2005.
I don’t have Sql server 2005 installed. Recently i have installed Sql server 2008
When i am trying to restore the .bak file. I am getting the following error
Restore failed for Database ‘BMTool’ microsoft.sqlserver.smo
–create database permission denied in database ‘master’
Why is it pointing to ‘master’ database when i am trying to create a new database with the bak file.
Please suggest me the steps as i am new to sql server.
Hi,
I am having 9 database under once instance, need to write script to take backup. on network mapped drive, I have limited knowledge of scripting request your help to guide.
Regards
Pagi
Refer this post. It will backup all databases in the folder you specify
hi,
I restoring database and my query is
RESTORE DATABASE Library FROM DISK = ‘d:\data\Backup\Library.lib’ WITH MOVE ‘Library’ TO ‘d:\data\Library_Data.mdf’ , MOVE ‘Library_Log’ TO ‘d:\data\Library_Log.ldf’, REPLACE
it returning me error message i.e.
Msg 3234, Level 16, State 2, Line 1
Logical file ‘Library’ is not part of database ‘Library’. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
please help me what is the issue?
My backup file has been saved with name Library.lib
I have a “monthend” database that needs to be restored every first day of the month from our production db. I perform local backups which look like this:
backup_201112272300.bak
As you can see the name changes every day due to the date. Is there a way to automate this process or am I stuck due to the file name changing?
Check this
hi,
How can I take a back up from database that no one else could restore it?
thanx and best regards.