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 Rishi,
I believe when you’re trying to restore the DB, another opened instance is there which is resulting an exclusive conflict. first close all the instance, then open only one & try the mentioned procedures, it’ll work
Hi Friend ,
My name is Plahanov ES..
Now i am working as a Database Programmer .
I have to restore a Database backup to SQL Server..
The scenario is , we have two separate Software running On two different system
One software backup is in MS Access and Another is In Sql Server
Two have the same databses tables……
I want to Update the tables in the sql server Db ..with the datas from MS access databse table
So can You suggest a Good Logic or any easy method to do it..
I think of inserting the data to SQL server from msaccess one by one
Is it a Right way to update it…
Please Help me to Overcome this situation
by
Plahanov
plahanov@gmail.com
Please follow the below steps: –
1) Open the MS-Access and select the blank document.
2) Secondly, right click using mouse and select the link tables option.
3) select the files of type option like that “ODBC databases ()”.
4) After that select the Machine data source or file data source depends upon your requirements.
5) Select the database and select the table name which you have required.
6) the last step is that you will see the link table like that “dbo_your table name”.
7) Then open the table and changes or updation according to you.
hi i need to create a job in MSSQL 2005 and schedule it everyday; just accessing some table and fetching the data and store it in to a table and rename that table as with the old table.
i have
sp_add_job
sp_add_jobschedule .. stored procedures, now the problem is where to define the sql quries?
can anyone help me!..
-vivek
Can sql 2005 Enterprise backup more then 1 DB at a time?
It is a sequential process. Second backup starts only after the completion of the first one
Hi,
i have one doubt…. i worte on T-sql for back up database…and here i selected all my database name using one varialble like @dbname through one cursor…. and given the path name….my script is working but one syntax error is coming i.e incorrect syntax ‘-‘ near that line no.. because i have one database name like ‘ad-catagories’ so this type of database name unable to backup so i want solution for this error…plz help me anybody…
thanks and regards,
satheesh kumar
Use [ad-catagories] instead of ad-catagories’
thanks for the comments you are providing so i am hope full to see more assistance matters related to aDBA
Hi ..is there any way to perform a selective merge on two or more sql databases…what i mean is i have different tables and stored procedures spread across multiple database files..i have to merge them to a single database file…
Note: I generated the script file (name.sql) for one of my database ..dunot know how to restore the table structure from it..
Waiting for ur help…
Use import/export wizard from management studio
It will copy structures as well as data
I’ve got an error on restoring DB that have a DDL trigger.
The problem is, the trigger need to insert a log record (username,date,scriptchanges) to different DB in a linkedserver.
When I try to restore the DB on local PC(I don’t have a linked server) I’ve got a message
“Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction”
Is there a way to fix this problem ??
thanks,
ronsi
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
Hello guys,
I am getting a message: DB in use everytime I try to make a DB resore from the database.bak file. Any idea?
As it says you need to close all the connects that are accessing the database which is to be restored
hi,
I want to merge 4 diffrent database server into one database server. can u say me the process or steps.
i mean 4 diffrent Dbase into one Dbase.
Hi, Pinal
Good One .really Helpful
Diw….
I have the backup of the MS sql server 2005 on my local system with the old date entries in it. Now I want to use this database in my website hence i have created the database with all tables using the script generated.
However, I am still struggling to upload all old date entries into it. Any help on how to do it would be very much appreciated.
Cheers,
Amit…
This is really a very good way to load the mind with database concepts and i have got right solutions for my Window applications which require such services.
Thanks a lot,
Gopal Mohan
Ami…do you mean to upload a database doing an import or something like that?
hi pin,
im karthik
can we restore a particular table in a database
awaiting for your reply
thanks
Karthik. P.
No
You have to have data in a text file and use bulk insert
Hi,
We have two mssql db, one resides external and other internal. Which replicates each other, when replicating one the table has wiped off, which had huge file download datas along with images. I need to copy from internal mssql db to external mssql db.
I tried DTS package, which fails after 4 to 5 hours. Data is more than 6 gb, tried restoring from the backup of the internal db, but external server had disk contriant to do this operation.
Further now have replication problem.
Please help with both replication and restore of the database.
Thanks,
Rao
Hi,
I have one query Regarding Restore:
Say I a Job schedule with weekly Full Backup and daily Differential Backup and 1 hour Trnsaction log backup.
Full Backup and Differential Backup backed up on single backup file!
If I restore that backup to other Other what is the syntax:
Pls. remember that bakup file contents both Full Backup and Differential backup
Regards
Rajib
Hello Pinal,
I developed an application (in vb6), for creating scheduled job to periodically backup our database. The Application is gathering all parameters from user and generates job and schedule. Everything is working fine, but size of .bak file is 4 times bigger, than .bak file created manually (without job just backup from enterprise manager)
What is going on? What should I do to decrease size of the .bak file, created through job?
Please, give me advise.
Thanks.
Sincerely,
Luda
i have a tape backup which is having almost 7 different databases(SQLSERVER2000) backups, i have to restore 1 table which is located in DB1. what syntax should i use for this …
its urgent plz….
thanks:)
regards
Aamir..