Search

Wednesday, September 5, 2012

Errors related to restore database

Error Restore backup on same server


Sometime when you try to restore the backup of the same database on the same server with a different name using query like this: 

RESTORE DATABASE DemoData FROM DISK = 'c:\Temp\DempDB.bak’ 

You may get the following error:


Msg 1834, Level 16, State 1, Line 1 
The file 'C:\Program Files\Microsoft SQL 
Server\MSSQL10.SQLSERVER2008SE\MSSQL\DATA\DemoData.mdf' cannot be overwritten.  It is being used by database 'DemoDB'. 
Msg 3156, Level 16, State 4, Line 1 
File 'Vinay' cannot be restored to 'C:\Program Files\Microsoft SQL 
Server\MSSQL10.SQLSERVER2008SE\MSSQL\DATA\DemoData.mdf'. Use WITH MOVE to identify a valid location for the file. 
Msg 1834, Level 16, State 1, Line 1 
The file 'C:\Program Files\Microsoft SQL 
Server\MSSQL10.SQLSERVER2008SE\MSSQL\DATA\ DemoData _log.LDF' cannot be overwritten.  It is being used by database '  DemoDB'. 
Msg 3156, Level 16, State 4, Line 1 
File 'Vinay_log' cannot be restored to 'C:\Program Files\Microsoft SQL 
Server\MSSQL10.SQLSERVER2008SE\MSSQL\DATA\ DemoData _log.LDF'. Use WITH MOVE to identify a valid location for the file. 
Msg 3119, Level 16, State 1, Line 1 
Problems were identified while planning for the RESTORE statement. Previous messages provide 
details. 
Msg 3013, Level 16, State 1, Line 1 
RESTORE DATABASE is terminating abnormally. 



The reason for this is that when you restore the database without explicitly define the data and log file information it will take the same information which was there from BACKUP database, but when you restore on the same database where the original database already have the data and log file exists, restore will fail with above error.


This error will not occur when you restore the database from “GUI” where it takes the new 
database name as file name for mdf and ldf files.

Alternatively you may provide the file name explicitly when you restore in script.

First check the file information with Restore Headeronly command. and run restore including file (mdf and ldf) path.

Restore failed if target database is in use


You will get below error when you try to restore the database if it is already in use by other 
processes(user) 


Msg 3102, Level 16, State 1, Line 1 
RESTORE cannot process database 'a' because it is in use by this session. It is recommended that the master database be used when performing this operation. 
Msg 3013, Level 16, State 1, Line 1 
RESTORE DATABASE is terminating abnormally.


Always check if any active session exists for the database which you are going to restore, or the restoring database name already exits. Restore require exclusive (X) permission on database.

Restore failed when insufficient free space on destination path


Msg 3257, Level 16, State 1, Line 1 
There is insufficient free space on disk volume 'C:\' to create the database. The database requires 521461696 additional free bytes, while only 518231616 bytes are available. 
Msg 3119, Level 16, State 4, Line 1 
Problems were identified while planning for the RESTORE statement. Previous messages provide details. 
Msg 3013, Level 16, State 1, Line 1 
RESTORE DATABASE is terminating abnormally.


If the destination database size is higher than the free space available on the drive where we are restoring, we will get this error, check the database size required  with Restore FileListOnly command. Restore will create the same size of mdf and ldf of the state when you backed up the source database, you cannot shrink any file once you backed up. Restore will make exact same state of files and database where you backed up.

When try to restore Newer version of sql server to older version gives error


Server: Msg 602, Level 21, State 50, Line 1 
Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

It is not possible to restore the database from newer version to older version.









7 comments:

  1. Well in such harmful condition you must try SQL recovery software and recover SQL database without any trouble
    Go through this :- http://www.sqlrecoverysoftware.net/sql-server-mdf-file/

    ReplyDelete
  2. Error: 18272, Severity: 16, State: 3. Please.

    ReplyDelete
    Replies
    1. Check below links :
      http://www.sqlservercentral.com/Forums/Topic118797-24-1.aspx#bm307316

      http://support.microsoft.com/kb/2022254

      https://forums.red-gate.com/viewtopic.php?t=13715

      http://serverfault.com/questions/256480/i-o-error-in-database-restore

      Delete
  3. The SQL database file save with the extension .mdf in the system’s hard disk. The MDF files also called as the master data files which are the primary data files stored in MS SQL database. As MDF files are the base files of all the primary data, any type of damage to these files may result in the fall down of the whole SQL database.With this tool easily recovers the database from corrupt SQL ,MDF & NDF files and saves them in SQL Server. The MDF file can be easily viewed using Microsoft SQL Server.

    For More Info about software visit:- http://www.filesrepairtool.com/sql-database-repair.html

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. SQL Recovery Software remove errors from SQL server database and allow recover your all database of SQL Server 2012 or below versions. The software support with all over version of SQL server. http://www.mannatsoftware.com/stellar-phoenix-sql-server-recovery.html

    ReplyDelete