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
How to create backup in LAN
Hi,
Im actually doing a daily ETL from our different properties (company) and store the data on a single server.
The thing is that my backup size has increase inormously and i would prefer to do a selective backup. that is specified which tables or views to be backup instead of doing a backup of the whole database.
The reason behind is that we are actually doing a daily backup at property level and don’t want to backup the same database again.
Regards,
Arvind
I am trying to do a restore and it seems like it its taking quite a while. How long should it take for a 3 MB file?
Thanks.
3 MB should take only few seconds
Check whether the RAM is used by some other processes
Hello sir,
I am using sqlserver 2000. act i have finished in database backup. i need database restore., what i need mean which place i stored my database backup files that is restored .
i want network system code. which system store my backup that is restore the database
help me
regards
bala guru
how to take the backup in SQL SERVER 2005 in SQL Script format
pls give the steps…..
vijay
How to find the mydb.mdf file in my machine
exec sp_helpdb mydb
Hi All,
can anyone tell me, how can i get the restored backup of old date. ie, i have lost a backup of date:12/01/2008, but i am sure that this backup is in my SQL 2000 Server i need to get the only backup of same date. is there any procedure to get it?
plz help meeeeeee…..
Thanx in advance…
Hi Pinal,
I have rather complex problem, consulted a few other DBAs with no luck so far, I am putting it here, may be you can sort it out…
I have database backup which I want to restore on my hoster’s server (shared hosting on LunarPages), but a database with same name already exists there. When i go on to restore backup, i always get an error. I am restoring this backup to a different database name that already exists there.
I renamed my database (using sp_renamedb), then took its backup and tried to restore but no luck again. It says Log file is being used by other database owner.
Is there anyway i can play with LDF files to sort this one ? or how is it possible then ?
Thanks – Waiting
Hi Kaliem,
Could you please provide me with the following information:
1. Have u already tried WITH REPLACE clause in your RESTORE DATABASE command? If not, then use it. If you still get errors, provide me with the following information:
2. The output of the following command:
RESTORE FILELISTONLY FROM YourDBBackupFile.BAK
3. From the Target Server (on which you want to restore the database), send me the output of the following commands:
USE DBName — (Existing target database name)
GO
SELECT name, filename FROM SYSFILES
NB: I want to know the locations of the target database files
3. Is the existing target database a copy of the database whose backup you want to restore now or is it some different database altogether?
Waiting for your reply.
Regards,
Munshi Verma
Hi Madhu,
This is regarding your following request:
on December 6, 2007 at 6:20 pm32 madhu
hi
SQLServer2005 backupdatabase in need to move that database to SQLServer2000.
it giving me …in compatable error ….hw can i move that database to SQLServer 2000
plz help me pout
————————————————————————-
You made this request way back in December. Did you get the solution to your query? If not and you still seek help in this regard, then let me know, I can help you.
Regards,
Munshi Verma
Hi Pinal Dave,
I am an Software Engineer. Now I have one query Regarding to take daily Backup with 2500 tables in MS SQL Server 2005 Enterprise edition. how to create a script file using Task Schedule.
Thanks & Regards,
V.Sathish.
Create a procedure with the following code
backup database db_name to disk='path'
Schedule it as a job to run daily
hello mr.pinal……
iam a junior DBA..i have a doubts regarding retrival of data tables from the Remote server to the local host using sql server2005..what kind of procedures ,i have to take for this?
Regards,
v.vijayakumar
Read about sp_addLinkedServer in SQL Server help file
Hi, can i made different restore db from server X (taken over X.x backup) to another server Y whit same database (i.e. name, design, …)?
Hello,
I have a question how to restore sql server 2000 files without extension into sql server 2005?
I need ur urgent reply…
Hello Pinal
I want to take backup of my database from one server to another.
so i am using following command
BACKUP DATABASE TO DISK = ‘//servername/sharedfoldername/filenam.bak’
This work fine if i use this command on servers having XP operating system .But if i use this command on the machine having windows 2000 or 2003 server then it gives error.
Error .
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘\\iguru\PMS_BackUP\test.bak’. Operating system error 1326
Please help me to solve this problem as early as possible.
Waiting for reply.Thanks in advance
Mostly likely there is no permission for that shared folder
HI dude,
g8 doing.
i m getting this message while restoring the test.bkp file
Too many backup devices specified for backup or restore; only 64 are allowed.
please help me…
It is becuase you were trying to restore backup of 2005 version to 2000 version. Thats not possible
Hi pinal,
can you please help to create a stored procedure to do backup
with two input parameteres,one the name of database
and other the type of backup.filname must have date, time in hour, min and second
This post may help you
But you need to change it according to your need
Hi ,
I have a backup,and i want to restore it on to a different database on a different server.now the DB onw hich i want to restore my backup is replicated .the type of replication is transation.
the normal restore wont work since replication wont allow the DB to be dropped,the other way is to first drop the replication restore the DB and then set up replication again.
Can any body tell me if there is any way to restore without dropping the replication.
hi,
i am trying to restore a db backup from sql2005 to sql2000. i have carefully follwed these steps:
-Change database compatible level to 80.
-Restore the database in sql server 2000 server. Some features of the SQL Server 2005 may not function properly.
now when i tried to run this
RESTORE FILELISTONLY
FROM DISK = ‘C:\demo_2k5.bak’
GO
it returned following error:
Server: Msg 3205, Level 16, State 2, Line 1
Too many backup devices specified for backup or restore; only 64 are allowed.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
can you please help…
thanks in advance…
Hi Mary,
I think it is not possible to restore a Sql server 2005 backup to Sql server 2000. So restore the backup to Sql server 2005 environment, it will work.
Regards
Praveen