Attach, Detach, Backup, Restore Errors with Solutions

Attach, Detach, Backup, Restore Scenario

In this scenario we can see

·         How to attach a database (adventure works 2012) and errors while attaching the database.

·         Backuping the database and its issues, solution.

·         Detach the database and error while detaching and its solution.

·         Restoring the database and its issues.  

Attach Error 5133:

Error occurred due to specification of incorrect path.

Solution:

Using GUI Specify the correct file location in the mdf file location option (browse) and current file path option (browse button).

USE [master]

GO

CREATE DATABASE [AdventureWorks2012] ON

(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdW2012.mdf’)

 FOR ATTACH

GO

 

Message:

File activation failure. The physical file name "C:\CodePlex Database\AdventureWorks2012_log.ldf" may be incorrect.

New log file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_log.ldf' was created.

 

Backup:
 

Full backup

BACKUP DATABASE [AdventureWorks2012] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

Differential backup

BACKUP DATABASE [AdventureWorks2012] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012diff.bak' WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

Transactional backup

BACKUP LOG [AdventureWorks2012] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012diff.trn' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Transaction Log Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

 

Detach:

Error 952

Normally one operation currently executing and trying to perform another task results an error because of lock on the database.

 USE [master]

GO

ALTER DATABASE [AdventureWorks2012] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

USE [master]

GO

EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2012', @skipchecks = 'false'

GO

 

Message

Msg 5061, Level 16, State 1, Line 1

ALTER DATABASE failed because a lock could not be placed on database 'AdventureWorks2012'. Try again later.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

Msg 952, Level 16, State 1, Line 1

Database 'AdventureWorks2012' is in transition. Try the statement later.

(some other task is performing on database and that task is placing a lock)

Restore database:

USE [master]

RESTORE DATABASE [AdventureWorks2012] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE DATABASE [AdventureWorks2012] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012diff.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [AdventureWorks2012] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012diff.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

GO

 

Message:

 

 

So here overwrite the existing database with replace option.

 

USE [master]

RESTORE DATABASE [AdventureWorks2012] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5

GO

 

Now if you try to recover the database using transactional log backup the following error will occurs.

Msg 4312, Level 16, State 1, Line 1

This log cannot be restored because a gap in the log chain was created. Use more recent data backups to bridge the gap.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

 

So restore the backups which are taken in between full and transactional backup. To solve the problem.

USE [master]

RESTORE DATABASE [AdventureWorks2012] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012diff.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

 

RESTORE LOG [AdventureWorks2012] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012diff.trn' WITH FILE = 1, NOUNLOAD, STATS = 5

 

GO

Errors 3117, 3159:

 

 

Syntax for tail of log backup:

backup log adventureworks2012 to disk ='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.tailbak' with no_truncate