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
Hello
i unknowingly deleted the main table in my sql server, now my question is how to retrive is it possible?..
please help me in this regard.ASAP
Thanks
Senthil
I need a simple restore command with current date.
Restore database with today backup file only. can any body helps me in this regards.
Raghu
Hi pinal,
When i’m doing restore DB then following error occur,Please help me to resolve this error
The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.1600. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Regards,
Deivendran.A
Seems like you have to update to R2 version of your SQL Server
more on version numbers can be found here:
Hi Pin ,
Thank you.. I received your wonderful reply..
But , I dont have any idea about the MDF and LDF .. May you explain something on this topic…
Kind regards,
Wilson Gunanithi . J
hi thank you very much , but i want to restore mdf and ldf files to my sqlserver2005 ,if you have solution pls tell me
[email removed]
hi Wilson,
In SQL server when ever the database is created. it creates with two file name extensions. i.e) .MDF and .LDF.
.MDF is the Database file
.LDF is the Log file of the database.
Thanks
Vijay
hi wilson ,
This is sanjeev ,
as the above answer that when the database is create then the .mdf and .ldf file is cretaed automatically and if u don’t give the path then that is store as the
Program files->microsoft sql server->mssql.1->MSSQL->data
you just see that particular .MDF and .LDF file
hey if you have mdf and ldf files then you can directly attach these files to your database server…
Hey Dude,
You have done a gr8 job by compiling all the scripts and docs in your blog.
Keep it up
Hi Pin ,
I have received the information about the backup devices in SQL Server 2005 that there are three type of devices as :
Disk / Tape / Pipe — in this, I have some idea about first two.But I dont have any idea about the ‘Pipe’. So reply for the same.
Sorry for the frequent disturbance.
I am awaiting for your reply.
Regards,
Wilson Gunanithi . J
when i try to backup or restore a database, the following error
was arise.
Server: Msg 3101, Level 16, State 2, Line 2
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
please could you help me.
Becuase the database you are trying to restore is currently used by someone. Close the connections and try again
Hi ,
Put the database in single-user mode; in SQL Server Management Studio:
•Right-click on the database name
•Click on Properties context menu item
•Click on Options menu item on the left
•In the grid, scroll down to the bottom
•Change ‘Restrict Access’ to SINGLE_USER
now u can complete the restore.
hello,
i have a question : We have a database and a job which runs a stored procedure on the database periodically. We want to backup our database without management studio or enterprise manager. With TSQL we can backup the database with all tables and stored procedures. But we couldn’t backup the job. Is there any way to backup and restore the jobs in SQL Server 2005, especially without management studio? Or can we create jobs with TSQL via “sqlcmd”? If any, could you give an example, thanks for now..
@Nagaranjan
That Error Is coming because Application is using the database.
so to take the backup or restore the datbase u must logout from the application completely.
Then only YOu will be able to take .
Sapna
It is not true for backup but for restore you need to disconnect from the application
no this is not true dost i think there is some problem in syntax or server
Hi Nagaranjan,
I was struggling with that issue, also. I found doing a server restart worked great. Now, I’m reading just a logout is needed! I guess I’ll check the logout. It has to be faster, but …
Hi Nagaranjan,
Check the database name ur trying to restore might be trying to restore on existind DB which is beging accessed by others.
~Best Regards
~Nag
Hi Wilson,
Pipe means named pipes – u can back up on a network too
~Nag
Hi asoo,
When u backup the database ,Database & DB objects are copied ,Jobs are server level routines – hence are supposed to be scripted & deployed
~Nag
Eg:
— Script generated on 7/17/2007 4:21 AM
— By: sa
— Server: MEPSERVER103
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]’) < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]’
— Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N’CyclicFill_Proc_Job’)
IF (@JobID IS NOT NULL)
BEGIN
— Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id 0)))
BEGIN
— There is, so abort the script
RAISERROR (N’Unable to import job ”CyclicFill_Proc_Job” since there is already a multi-server job with this name.’, 16, 1)
GOTO QuitWithRollback
END
ELSE
— Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N’CyclicFill_Proc_Job’
SELECT @JobID = NULL
END
BEGIN
— Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N’CyclicFill_Proc_Job’, @owner_login_name = N’sa’, @description = N’No description available.’, @category_name = N'[Uncategorized (Local)]’, @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
— Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N’CyclicFill_Proc_Job_Step’, @command = N’Exec CyclicFill
go’, @database_name = N’LTCDEMODB’, @server = N”, @database_user_name = N”, @subsystem = N’TSQL’, @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N”, @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
— Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N’CyclicFill_Proc_Job_Schedule’, @enabled = 1, @freq_type = 4, @active_start_date = 20061201, @active_start_time = 80000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
— Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)’
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Hi Nagaraju,
Does this script enable to loop on jobs ? or Do we need to create the script for each job that we want to copy to another server ?
thanks
Sandrine
Oh i Am sorry now i guess its working ….
Well NAGAARGUN this is for you .keep your database in Single user mode ,after that you will able to do so !!
Keep posted if any further issue you encounter
hi,
i tried to create new database from existing one with different name but its not working so anybody help me to solve this issue…..
What did you mean by its not working?
i need to copy all tables from a database to another database, so if it is possible to solve using any stored procedure…..
Use import/export wizard from Management studio
Dear Concern,
Hello.
This is really nice for those who really to be very much close and knows the more technicallity on SQL Server side.
I would like to know that how we can restore the backup of systems databases. Like Master, Model, Msdb and TempDb. Also How we move TempDb Database in another drive rather than the installed location. If someone can help me out in this regard I would be really greatful for him/her.
With Regards
Anu
I do need to clear few issues.
1. A database size is of 1gb. If tran log backup is scheduled, will that affect the process?
2. One of my friend maintains a complete backup of a database everyday. Data is always appended. What is the logic behind keeping a disk record of all those backups? At point of setback, if the last backup is restored, won’t that suffice?
Pls answer immed.
1 No
2 Better keep latest week’s backup files and delete others
Is it possible to restore database using LOG file that is .ldf files.
Please help me, please !!!
Me too getting the same err:
“Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally.”
I m following the steps below, to restore the Database:
1. Restore the FileListOnly.
2. Alter Database to SINGLE_USER mode.
3. Restore Database with MOVE.
4. Alter Database to MULTI_USER Mode.
First 2 steps are working Fine, but at 3rd step, it’s giving the “Exclusive Access to Database” Error.
Why is it so ?? Where I am making mistake ?? Please if anyone can help me, it’s quite urgent.
Awaiting for reply.
Thanks
Rishi Songara
Sorry, actually my third step goes like this, that is, no double backslash:
3. Restore Database with REPLACE and MOVE.
RESTORE DATABASE ASPNETChat FROM DISK = ‘D:\ASPNETChat.bak’ WITH REPLACE, MOVE ‘ASPNETChat_Data’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\ASPNETChat _Data.mdf’, MOVE ‘ASPNETChat_Log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\ASPNETChat _Data.ldf’
Sorry once again:
3. Restore Database with REPLACE and MOVE.
RESTORE DATABASE ASPNETChat FROM DISK = ‘D:\ASPNETChat.bak’ WITH REPLACE, MOVE ‘ASPNETChat_Data’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\ASPNETChat _Data.mdf’, MOVE ‘ASPNETChat_Log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\ASPNETChat _Log.ldf’