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 probs in restoring my sql server 2005 bak file to 2003 sql server.
The error says that
The media family on device ‘D:\dolfaq\dolfaq.BAK’ is
incorrectly formed.SQL Server cannot process this media family.
RESTORE DATABASE is terminating abnormally
is there any other way to crack this
plz help
thanks
regards
N.Balaji
hello dear
Is it is possible to restore sql server 2005 complete backup
in sql server 2000.
if yes….. pl tell me the procedure to resotre it assp.
Thanks.
Dhananjay
Software Developer
i have script of database.
my database deleted mistakenly.
before delete i generate script.
can i retrieve tae database.
please help me!!!!!!!!!!!!!! immidiately
my database is in sql server 2000
and where to run this code as you suggested
//////////////
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
//////////////
please help i am newer to sql server.
Hi,
please let me know How to restore .MDB file using t-sql?
Is there any option to restore database from “.MDF” file only? I do not have “.LDF” file.
How it will restore ? Is this possible or not?
Thanks in Adv
Haque
Read about sp_attach_db in SQL Server help file
System.data.Sqlclient.sqlerror
Hi Everyone,
I have .bak files which getting copied from a source server and brought into the target server. However, I have 2-3 .bak files for each database with different time-stamps. like:
webdata_backup_200810311030.bak
I want to load the database with the latest backup file (latest time-stamp). How can i acheive this?
this is the script i am using:
use master
go
Restore database WebData from
disk=’J:\Microsoft SQL Server\MSSQL.1\MSSQL\backup\WebData\*.bak’
with Replace,
MOVE ‘WebData’ TO ‘E:\SQL Server 2005 Data Files\MSSQL.1\MSSQL\Data\WebData.mdf’,
MOVE ‘WebDatalog’ TO ‘E:\SQL Server 2005 Data Files\MSSQL.1\MSSQL\Data\WebData_log.ldf’
go
Thanks for your help!
Hi,
I generated bak file using this query from DATABASE Test ,
BACKUP DATABASE Test TO DISK = ‘Test.bak’ WITH INIT
For restoring I gave another DATABASE restoreDB
RESTORE DATABASE [restoreDB]
FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Test.bak’
WITH REPLACE,
MOVE ‘restoreDB_Data’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\restoreDB.mdf’,
MOVE ‘restoreDB_Log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\restoreDB.ldf’
then I got this error.
Logical file ‘restoreDB_Data’ is not part of database ‘restoreDB’. Use RESTORE FILELISTONLY to list the logical file names.
RESTORE DATABASE is terminating abnormally.
The main aim is to copy entire DATABASE Test to another DATABASE restoreDB.
Thanks
hi, please help me, how do I revert the “BACKUP LOG WITH TRUNCATE_ONLY” command?
Thanks
— Restore database from file
—————————————————————–
use master
go
declare @backupFileName varchar(100), @restoreDirectory varchar(100),
@databaseDataFilename varchar(100), @databaseLogFilename varchar(100),
@databaseDataFile varchar(100), @databaseLogFile varchar(100),
@databaseName varchar(100), @execSql nvarchar(1000)
— Set the name of the database to restore
set @databaseName = ‘poc’
— Set the path to the directory containing the database backup
set @restoreDirectory = ‘D:libssqlPRACTICE_DB_INSTALLER’ — such as ‘c:temp’
— Create the backup file name based on the restore directory, the database name and todays date
set @backupFileName = @restoreDirectory + @databaseName + ‘-‘ + replace(convert(varchar, getdate(), 110), ‘-‘, ‘.’) + ‘.bak’
— set @backupFileName = ‘D:DATABACKUPSserver.poc_test_fbu_20081016.bak’
— Get the data file and its path
select @databaseDataFile = rtrim([Name]),
@databaseDataFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files
inner join
master.dbo.sysfilegroups as groups
on
files.groupID = groups.groupID
where DBID = (
select dbid
from master.dbo.sysdatabases
where [Name] = @databaseName
)
— Get the log file and its path
select @databaseLogFile = rtrim([Name]),
@databaseLogFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files
where DBID = (
select dbid
from master.dbo.sysdatabases
where [Name] = @databaseName
)
and
groupID = 0
print ‘Killing active connections to the “‘ + @databaseName + ‘” database’
— Create the sql to kill the active database connections
set @execSql = ”
select @execSql = @execSql + ‘kill ‘ + convert(char(10), spid) + ‘ ‘
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
and
DBID 0
and
spid @@spid
exec (@execSql)
print ‘Restoring “‘ + @databaseName + ‘” database from “‘ + @backupFileName + ‘” with ‘
print ‘ data file “‘ + @databaseDataFile + ‘” located at “‘ + @databaseDataFilename + ‘”‘
print ‘ log file “‘ + @databaseLogFile + ‘” located at “‘ + @databaseLogFilename + ‘”‘
set @execSql = ‘
restore database [‘ + @databaseName + ‘]
from disk = ”’ + @backupFileName + ”’
with
file = 1,
move ”’ + @databaseDataFile + ”’ to ‘ + ”” + @databaseDataFilename + ”’,
move ”’ + @databaseLogFile + ”’ to ‘ + ”” + @databaseLogFilename + ”’,
norewind,
nounload,
replace’
exec sp_executesql @execSql
exec(‘use ‘ + @databaseName)
go
— If needed, restore the database user associated with the database
/*
exec sp_revokedbaccess ‘myDBUser’
go
exec sp_grantdbaccess ‘myDBUser’, ‘myDBUser’
go
exec sp_addrolemember ‘db_owner’, ‘myDBUser’
go
use master
go
*/
I am trying to run your script in sql server 2008 enironment and i am getting the errors when procedure trying to kill active database connection i am attaching error
Msg 103, Level 15, State 4, Line 1
The identifier that starts with ‘select @execSql = @execSql + ‘kill’ + convert(char(10), spid) + ‘ ‘
from master.dbo.sysprocesses
where db_name(dbid) = @databa’ is too long. Maximum length is 128.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ‘select @execSql = @execSql + ‘kill’ + convert(char(10), spid) + ‘ ‘
from master.dbo.sysprocesses
where db_name(dbid) = @databas’.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘select @execSql = @execSql + ‘kill’ + convert(char(10), spid) + ‘ ‘
from master.dbo.sysprocesses
where db_name(dbid) = @databa’.
Could you please helpme out
‘D:BackUpYourBaackUpFile.bak’
and
WITH MOVE ‘YourMDFLogicalName’ TO ‘D:DataYourMDFFile.mdf’,
MOVE ‘YourLDFLogicalName’ TO ‘D:DataYourLDFFile.mdf’
——————————–
BackUpYourBaackUpFile, YourMDFLogicalName, YourLDFLogicalName, DataYourLDFFile
what do all this imply …. can someone give any example…
Hello, I have a corrupt VMWare session. withinit an SQL Server 2000 Database. There are jobs in it that a nead. How can i extract thos jobs. I can acces the volumes, so i can see the database files, but where to find the jobs i have created.
Thanks in advance.
Eric
Eric, it’s in msdb database.
Hi,
Can someone help me out with this please:
I am trying to restore a db (backupcopy from my pc) on a network server.
When I try to select the filelist name from the file option on restore, I get only the server folders/files. How do I pick up the .bak file from my pc?
Thanks in advance,
Priya.
@Priya
You cannot do it through GUI (Interface)
But you can do it through scripts…
BACKUP DATABASE Foo TO DISK = ‘\myservermysharefoo.bak’ WITH INIT
Reference :
Regards
IM
Hi Priya,
To restore your back up on Network server, you need to copy your .bak file on the server OR map Server drive to your PC.
Server will not allow you to select file from Network for Restore.
Thanks,
Tejas
Hi Imran and Tejas,
Thanks for your replies.
I tried to restore the db like this:
RESTORE DATABASE
FROM DISK = ‘N\\\\dbbackup.bak’
WITH MOVE ‘dbname.mdf’ TO ‘E:\\dbname.mdf’,
MOVE ‘dbanme_log.ldf’ TO ‘E:\\dbname_log.ldf’
With Replace
When I give ‘with replace’ it gives error on that replace statement. When I remove the ‘with replace’ command it says:
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I tried giving : exec sp_who
It gives only my pc’s name. Can someone help me out with this please? I had been trying to restore this for the past 2 days..
Then I tried to backup the db from the local instance in the syntax which Imran gave me. But then it says, incorrect syntax near ”.
I had been trying to fix this for quite sometime now.. please help..
Thanks
Ok, I had a problem with giving the database name with dot inbetween. Thats why it said that error. Now after enclosing the db name within quotes, Now I tried giving what Imran said. It gives the message :
Cannot open backup device. Operating system error 53(The network path could not be found).
I hv verified the network path.
Can you confirm where I have to give the $ symbol. Right after the drive? Like this: \\\E$\\backupname
Thanks.
@ Priya.
I apologize for giving you wrong advice, I think I understood your question completely wrong, script that I gave you takes backup of any database on network drive.
But in your question you asked to restore database backup from network drive.
Look at this webpage once :
One important note : backup you are trying to restore from different machine, should be in Shared folder. ( folder marked with a hand symbol), Only then SQL Server can access that folder. If your backup is not in a shared folder, then SQL Server cannot restore backup.
Error1 : Exclusive access could not be obtained because the database is in use.
Explanation:
Generally you will see this message when you are trying to restore a database that is online. Or if you are trying to over write .mdf file on Operating System that are being used by database, this usually happens when you are trying to restore.
You need to use with replace with Restore command to point it to different location.
Backup will have some information in it, it remembers from which location it was taken backup. And when you try to restore it will try to restore database in the same location.
Since your backup is residing in other servers, file location of the two servers might not match, so you need to use WITH REPLACE command with , WITH RESTORE command
The Shared folder path is generally like this,
\ServernameFoldernamefilename
Servername – Name of the Server
Foldername – Name of the folder ( this folder must be shared folder , folder will have a hand symbol )
filename – name of the backup file.
Regards
IM.