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 Dave,
I have a backup of ‘A’ database which i took it from
SQL Server 2005 Workgroup Edition, somebody deleted few records in the database. Is there any way to identify who deleted and when it was deleted from the log file.?
Actually, I restored the backup into SQL Server 2005 Enterprise Edition and I tried with DBCC LOG(‘A’) and could not see any deleted actions.??? how to identify ???
Regards
Siva
Hi Shiva,
Delete commands are logged opreation and if somebody executed Truncate command which wont be logged.
So for future if you would like to monitor DML commands you can anable C2 auditing or create trigger ,aletrs with DML limitation.
Find out more details in BOL.
Praveen barath
Hi Praveen,
Thanks for your response, Actually only few records were deleted, so they might have not used truncate statement they used only delete statement right..???
Siva
Is it possible to restore database using only the LOG file that is .ldf file.
Hi Pinal Dave,
I want To Import Data From a Table of one Database To A new Table in Other Data Base . Could you help me in or give me a hint to write a single query for this. I”ll be thank full for this
.
Vikas
hi while restoring the database i got below error. what to do????
“System.Data.SqlClient.SqlError: The tail of the log for database databasename has not been backed up.Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log. (Microsoft.SqlServer.Smo)”
please reply as soon as possible.
thanks & Regards
Ritish
I want to ask that data of SQL Server is stored in which format??.mdf or .ldf??
How to take backup of data..
I run above application i get below error
Msg 3176, Level 16, State 1, Line 1
File ‘E:\Program Files\Microsoft SQL Server\MSSQL\Data\SCMS_INV.mdf’ is claimed by ‘SCMS_INV_log'(2) and ‘SCMS_INV'(1). The WITH MOVE clause can be used to relocate one or more files.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Hi
I recently restored a database onto another server and this worked fine however all the views are empty. How can I re-populate the views with data again?
Hi, Pin.
I am using a cursor in my store procedure and it works great but the problem is it uses 100% the processor. is there any other way or method of using the particular procedure with the same process other than cursor? This will do a great favor for me if do give solution for this matter. Tnx in advanced
Other scripts will be appreciated.
This is my sample script:
Declare @curCustomerID integer,
@curCompanyName nvarchar(50),
@curContactName nvarchar(50),
@curAddress nvarchar(100)
Declare curCustomer CURSOR FOR
SELECT CustomerID,
CompanyName,
ContactName,
Address
FROM Customers
OPEN curCustomer
FETCH NEXT FROM curCustomer
INTO @curCustomerID,
@curCompanyName,
@curContactName,
@curAddress
WHILE @@FETCH_STATUS = 0 BEGIN
— TODO Insert record into specified table name
FETCH NEXT FROM curCustomer
INTO @curCustomerID,
@curCompanyName,
@curContactName,
@curAddress
END
CLOSE curCustomer
DEALLOCATE curCustomer
Try this code without a Cursor
INSERT into target_table
(
CustomerID,
CompanyName,
ContactName,
Address
)
SELECT
CustomerID,
CompanyName,
ContactName,
Address
FROM Customers
how to take backup of all the table of a database into another database
Only tables or procedures, views,etc too?
Read about Backup database in SQL Server help file
hi
i want to import data from access to sql server.But acces database updates frequently.i want that updated data in sql server daily.
Is it a good practice to import data daily using export /import program?i dont think so.pls give me any other solution
Hi Pin,
can you tell me
i want to restore sql data but i haven’t Server 2005 Management Studio, i have mydb.bak
how can i restore it without Server 2005 Management Studio
pls help me out
thanks
hi pin,
how can I take a back up of table,instead of the whole database.
1 Script the object using Generate script
2 Copy data to a text file using a bcp
Hello Pinal,
I need a script to take full mssql backup with all databses daily. you have this pls let me know.
Thanks
INdy
Declare @sql varchar(8000)
set @sql=''
select @sql=@sql+
'
Backup database '+name+' to disk=''E:backup'+name+'_'+convert(varchar(8),getdate(),112)+'.bak'''
from master..sysdatabases
EXEC(@sql)
Create a procedure using the above code and schedule it to run periodically
Hi,
I want some help.
I have two different servers. from one i have to take a backup of XYZ DB and restore in another server by different name.
Both this server is having different IP address.
How to specify the server name in script
what i have is this:
BACKUP DATABASE XYZ TO DISK = ‘c:\XYZ.bak’
DROP DATABASE XYZ
RESTORE DATABASE ABC FROM DISK = ‘c:\XYZ.bak’
go
But how to specify the server name
Regards,
Mahesh
please give the information as soon as possible
how to back up the database
Backup database db_name to disk=’file_path’
how can backup database from SQL Server using vb6