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,
We have migrated database from sql server 2000 to sql server 2005.
Now we have the database ready in sql server 2005.
Is there a way to use the (latest mdf and ldf files) from today from sql server 2005, and have the newest data in sql server 2005.
Basically, we dont want to touch stored procedures, views etc, only need to newest data in sql server 2005.
Can we detach the database(sql server 2005) and then reattach with newest mdf and ldf files.
I am in urgent need and any help is appreciated.
Thanks,
Ashima
H Ashima,
You can move a database from one server to another by detaching & reattaching the .mdf, .ldf files. But this will move the whole database with all objects.
Even to move database the recommonded method is backup-restore.
To get only the differences in tables on two databases, you can use TABLEDIFF utility. This utility has option to generate the script of all changes.
Regards,
Pinal Dave
Pinal, great blog. I was wondering I am getting backup files that I want to attach automaticly but I don’t know the filenumber is there a way to do this
RESTORE DATABASE [MYDB]
FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MYDB.bak’
WITH FILE = 1,
MOVE N’COREREP_3_Data’ TO N’C:\Data\MYDB.mdf’, MOVE N’COREREP_3_Log’ TO N’C:\Data\MYDB_1.ldf’, NOUNLOAD, STATS = 10
But replace the File= 1 with File = MAXSomething
GO
I am a little closer but how do I get the position of the last record?
RESTORE HEADERONLY FROM DISK = ‘C:\data\myDB.bak’
I need to restore a sqlser 2000 database from disk. When I do a filelistsonly restore, there are only 3 file types listed, an .mdk and 2 ndfs. Is a full database restore possible from this situation? I tried a myriad of combinations of tsql with no success as yet
I need to restore a sqlser 2000 database from disk. When I do a filelistsonly restore, there are only 3 file types listed, an .mdk and 2 ndfs. Is a full database restore possible from this situation? I tried a myriad of combinations of tsql with no success as yet
hi
i’m Prakash
i’m a trainee in a software consultancy. i develop a software with SQL server as backend. i need the roll back query while insert or update data.
You need to start the transaction before any Insert or updated
Begin Transaction
your insert / update statements
commit or rollback transaction
Regards
good day sir!!!my question is on how to backup sql server 2005 using Visual basic 6.0 pls help me with my problem1!!im a beginner using a SQL Server 2005 integrated on vb 6,thanks God Bless!!!
dear sir
i have 10 database some delete one of my database how to find who is delete my database from server …
Regards
Rajesh
what is the difference between replication & mirroring..?
&&&&&&&&&&&&&&&&&&&&&&
what is the difference between Logshiping & replication ..?
Hello Sir,
The following query is working good on the server pc.
BACKUP DATABASE Genius
TO DISK = ‘D:\Genius1.bak’
But when i try to take backup on the location of another PC on the LAN, i.e. my PC, Nikie,
BACKUP DATABASE Genius
TO DISK = ‘\\Nikie\D:\Genius1.bak’
It is showing following error:
Cannot open Backup device. Operating System Error 5 (Access is denied).
Please help. I want to take backup on any client PC on the LAN.
Thanks in advance.
Regards,
Nikie.
Try to copy the backup file to the local disk first. Then try restoring it. If I remember correctly I had some issues like this some years ago. I built couple of automated scripts for the backuping, file copying and restoring.
Anybody can tell me. How can I restore one table or entity from full database backuup
Hello Shahriar,
Table is not a unit for restore. You would have to restore the full database than get the data table from that database.
If the table is in a separate Filegroup then you can restore that particular filegroup only.
Regards,
Pinal Dave
hi
i have seen that every one on web tells the simple backup query like
BACKUP DATABASE DBNAM TO DISK = ‘BACKUP_PATH’;
i have a peoblem with this query. i am trying to backup my full Sql Server 2008 enterprise with a small script similar to above
——————-
DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) — path for backup files
DECLARE @fileName VARCHAR(256) — filename for backup
DECLARE @fileDate VARCHAR(20) — used for file name
SET @path = ‘D:\AutoDBBackups\’
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
————–
this script crashes when a database with an unusual name comes. like “SharePoint_AdminContent_0067564c-c101-460f-bf42-208ce4f9ae10”
simply if you have any database with a “-” in name backup query will not recognize that as a database
simple solution is
BACKUP DATABASE “SharePoint_AdminContent_0067564c-c101-460f-bf42-208ce4f9ae10” TO DISK = ‘D:\AutoDBBackups\as.bak’;
just wraped the database name with double quots and done.
but when it comes to script there is no way. or i could not find any way.
like this small script
DECLARE @name VARCHAR(50)
SET @name=’SharePoint_AdminContent_0067564c-c101-460f-bf42-208ce4f9ae10′
BACKUP DATABASE @name TO DISK = ‘D:\AutoDBBackups\as.bak’;
i tried with everything i can think of
like
SET @name='[SharePoint_AdminContent_0067564c-c101-460f-bf42-208ce4f9ae10]’
or
SET @name='”SharePoint_AdminContent_0067564c-c101-460f-bf42-208ce4f9ae10″‘
and many other.. no use
can anyone just try with that.. and explain what to do
thanks
Ali
@Ali
Replace these two lines with your two lines in your script,
SET @fileName = ””+@path + @name + ‘_’ + @fileDate + ‘.BAK”’
BACKUP DATABASE Quotename(@name) TO DISK = @fileName
Should work, if it does not works please let us know what error message you get.
~ IM.
try the declare cursor statement as like below:
DECLARE db_cursor CURSOR FOR
SELECT ‘[‘ + name + ‘]’
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)
and set @fileName as below:
SET @fileName = @path + replace(@name, ‘-‘,’_’) + ‘_’ + @fileDate + ‘.BAK’
In same way, replace all other special character that are not allows in file name with underscore.
Regards,
Pinal Dave
Hi Pinal,
I am just working on SQL database restore and transaction logs backups in SQL 2005.
I am restoring complete backup with recovery.
Now, if i am trying to restore Transactional logs, it saying an error “The log or differential backup cannot be restored because no files are ready to rollforward.”
Any suggestions PLZ.
Hello Vaibhav,
restore the full backup with NORECOVERY option and then restore the transaction log backup.
Regards,
Pinal Dave
Thanks Pinal,
Ya Actuly, Database in not readable if restored with NORECOVERY option.
So we want to restore with RECOVERY only to make it readable.
Hi Pinal,
Actually, it seems we cant use STAND BY option in SQL server 2005.
We want to restore the Transactional Logs in SQL 2005. Its not happening if restore is done with RECOVERY.
And we are not able to use NORECOVERY as it will leave the database non-readable.
Can you give any suggestions here….
Hi Pinal,
I wanted to back up a database and restore it in a different name using T-SQL Script. I tried the above script. But it is not is not working.
CREATE DATABASE NEW_DB
RESTORE FILELISTONLY
FROM DISK = ‘c:\Backup\OLD_DB.BAK’
ALTER DATABASE NEW_DB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE NEW_DB
FROM DISK = ‘c:\Backup\OLD_DB.BAK’
WITH MOVE ‘OLD_DB’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL$VSDOTNET\Data\NEW_DB.MDF’,
MOVE ‘OLD_DB_Log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL$VSDOTNET\Data\NEW_DB_log.LDF’
ALTER DATABASE NEW_DB SET MULTI_USER
GO
The error was
Reissue the statement using the WITH REPLACE option to overwrite the ‘NEW_DB’ database.
I tried with Replace Option before this and that gives the error
The OLD_DB is in use. Use ‘Move’ to mention the location.
(RESTORE DATABASE CM_V4_PO FROM DISK = ‘c:\Backup\OLD_DB.BAK’
WITH REPLACE)
The above replace script works fine in the server.
I m using MSDE. This is urgent. Can anyone tell me what mistake am i commiting here
Ya Actuly, Database in not readable if restored with NORECOVERY option.
So we want to restore with RECOVERY only to make it readable.
when i am restoring from .bak file containg multiple backup sets and when i am restoring the most recent day backup i am getting only first file/date backup but i want to restore last file/date backup from .bak file.
i have used this command also but still not working
RESTORE DATABASE Testing
FROM DISK = ‘C:\Documents and Settings\Administrator\Desktop\New Folder (2)\Snehal.bak’
WITH file = 21 ,NOUNLOAD ,STATS = 10 ,RECOVERY ,REPLACE
help me ! thanks in advance .
ravi kore
Hello Ravi,
What error are you getting?
Please also share the command that you are using to take the backup of database.
Regards,
Pinal Dave