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.
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.
