What are the issues you faced in backup and restore process?

Common Issues in Backups:

Issue (1):

Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
BACKUP LOG is terminating abnormally.

Sol:

Change the database into Single user mode and perform the Backup.

Use DBName

GO

Alter database <DBName> Set Single_user

GO

Backup Database DBNAME to disk=’path’

Issue (2): Error 3201 – when performing a backup to a network share

Sol:

Where SQL Server disk access is concerned, everything depends on the rights of the SQL Server service startup account. If you are unable to back up to a network share, check that the service startup account has write rights to that share.

 Error: Cannot open the backup device

Sol: 

Either the specified location is missing or the service account under which the SQL Agent is running does not have the permissions on that folder.

 Common Issues in Restore:

Getting exclusive access to a SQL Server database for restore

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

sol:

https://www.mssqltips.com/sqlservertutorial/82/getting-exclusive-access-to-a-sql-server-database-for-restore/

Error 3205 – Too many backup devices specified for backup or restore;

Sol:

The most common cause for this error is because you are trying to restore a SQL Server 2005 backup on a SQL Server 2000 instance

 Error 4305 – an earlier transaction log backup is required

Sol:

There are one or more transaction log backups that need to be restored before the current transaction log backup. Using LSN number we can identify the prior log backups.

For any more issues : https://www.mssqltips.com/sqlservertip/4110/solve-common-sql-server-restore-issues/

Advertisements