Day 22: Restore related Errors

Day 22: Restore related Errors

  1. Error Restore backup on same server

When we try to restore the backup of the same database on the same server with a different name using query like this

RESTORE DATABASE a FROM DISK =’c:\vinayx.bak’

 

You will get the following error:

Msg 1834, Level 16, State 1, Line 1

The file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQLSERVER2008SE\MSSQL\DATA\Vinay.mdf’ cannot be overwritten.  It is being used by database ‘Vinay’.

Msg 3156, Level 16, State 4, Line 1

File ‘Vinay’ cannot be restored to ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQLSERVER2008SE\MSSQL\DATA\Vinay.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\Vinay_log.LDF’ cannot be overwritten.  It is being used by database ‘Vinay’.

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\Vinay_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 we 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.

Resolution:

  1. 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
  2. Provide the file name explicitly when you restore in script.

First check the file information

restore filelistonly from disk=’c:\vinayx.bak’

 

run restore command like this.

 

restore database a from disk=’c:\vinayx.bak’ with

move ‘Vinay’ to   ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQLSERVER2008SE\MSSQL\DATA\a.mdf’,

move ‘Vinay_log’ to ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQLSERVER2008SE\MSSQL\DATA\a_log.LDF’

 

  1. 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 is any active session exists for the database which is going to be restored, or the restoring database name already exits. Restore require exclusive (X) on database.

  1. 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 ‘E:\’ 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

Restore filelistonly from disk =’backup file’

Restore will create the same size of mdf and ldf of the state when you backed up the source database, you cannot shink any file once you backed up. Restore will make exact same state of files and database where you backed up.

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

For 2000:

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.

Eg. You cannot restore backup of sql server 2005 to sql server 2000, similarly backup from 2008 to 2005.

http://sqlskills.com/BLOGS/PAUL/post/Msg-602-Level-21-State-50-Line-1.aspx

Advertisements
This entry was posted in Backup and Restore, Troubleshooting and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s