Archive

Posts Tagged ‘RESTORE DATABASE’

SQL Error – The operation cannot be performed on a database with database snapshots or active DBCC replicas

June 16, 2015 Leave a comment

 
Today while restoring a Database and Dropping it I came across following errors:
 

Error #1. The first error was while I was trying to RESTORE an existing database from a backup file:

Msg 5094, Level 16, State 2, Line 1
The operation cannot be performed on a database with database snapshots or active DBCC replicas.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
 

Error #2. The second error came while I tried to DROP that existing database, to ignore the previous error:

Cannot drop the database while the database snapshot “dbStaging_ss_20160607” refers to it.
Drop that database first. (Microsoft SQL Server, Error: 3709)
 

By both the above errors its obvious that a Database Snapshot is associated with the Database that I’m going to Restore or Drop. So first try to identify and DROP that Snapshot. You can do this by checking in SSMS under Object Explorer –> Databases –> Database Snapshot, or running the following query:

USE [master]
GO

SELECT * 
FROM sys.databases
WHERE source_database_id IS NOT NULL
GO

The above query will list all the Snapshots created for existing Databases. Just check the Snapshot that is associated with the Database you were trying to Restore.
 

Before dropping the respective Snapshot, script out the DDL to create the Database Snapshot back, by Right Clicking on the Database Snapshot –> Script database as –> CREATE To –> Clipboard. The script will look like below DDL Script:

USE [master]
GO

CREATE DATABASE [dbStaging_ss_20160607] 
ON ( 
	NAME = N'dbStagingDB', 
	FILENAME = N'E:\dbStaging\dbStaging_Primary.mdf' 
) 
AS SNAPSHOT OF [dbStagingDB]
GO

 

After you are done by scripting the Database Snapshot DDL, just DROP the Database Snapshot, just like you drop any other Database:

USE [master]
GO

DROP DATABASE [dbStaging_ss_20160607]
GO

 

After you are done with this, try Restoring back the Database that you were trying initially.

Advertisement
Categories: SQL Errors Tags: , ,

SQL Error – Logical file ‘XYZ_Log2’ is not part of database ‘XYZ’. Use RESTORE FILELISTONLY to list the logical file names.

May 12, 2013 Leave a comment

I was restoring Databases as a routine monthly job. Suddenly in between I came across an error which didn’t let me restore a Database. I restored other Databases and left this one to do at the end. Finally I picked it up again and saw that some new kind of error I was facing. The error message says to use “RESTORE FILELISTONLY”, I check the syntax on MSDN and found that this Database had two log files (LDF) earlier, but now configured for only one. And as I was using the same old script to Restore it, I was getting this error.

Let’s try to reproduce it in a standalone box:

–> I took a backup of AdventureWorks2012 Database on my machine.

I created the RESTORE script and added another line of log file at line 8 below:

USE [master]
GO

RESTORE DATABASE [AdventureWorks2012] 
FROM DISK = N'E:\Softwares\MS_bits\AdventureWorks2012.bak' WITH  FILE = 1,  
MOVE N'AdventureWorks2012_Data' TO N'E:\MSSQL11\DATA\AdventureWorks2012_Data.mdf', 
MOVE N'AdventureWorks2012_Log' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log.ldf', 
MOVE N'AdventureWorks2012_Log2' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log2.ldf', 
NOUNLOAD,  REPLACE,  NOUNLOAD,  STATS = 5
GO

On executing I got an error as expected, shown below:

Msg 3234, Level 16, State 2, Line 1
Logical file ‘AdventureWorks2012_Log2’ is not part of database ‘AdventureWorks2012’. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

As suggested in the Error Message, I used the “RESTORE FILELISTONLY” command to check what all files this Database is using:

RESTORE FILELISTONLY from disk=N'E:\Softwares\MS_bits\AdventureWorks2012.bak'

And I could see only two, one MDF & one LDF, as shown below:

LogicalName		PhysicalName					Type	FileGroupName
AdventureWorks2012_Data	E:\MSSQL11\DATA\AdventureWorks2012_Data.mdf	D	PRIMARY
AdventureWorks2012_Log	E:\MSSQL11\Log\AdventureWorks2012_log.ldf	L	NULL

So, I removed the extra LDF log file option from the RESTORE script, as shown below:

RESTORE DATABASE [AdventureWorks2012x] 
FROM  DISK = N'E:\Softwares\MS_bits\AdventureWorks2012.bak' WITH  FILE = 1,  
MOVE N'AdventureWorks2012_Data' TO N'E:\MSSQL11\DATA\AdventureWorks2012_Data.mdf', 
MOVE N'AdventureWorks2012_Log' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log.ldf', 
--MOVE N'AdventureWorks2012_Log2' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log2.ldf', 
NOUNLOAD,  REPLACE,  NOUNLOAD,  STATS = 5
GO

The above statement executed fine and the Database restored successfully.

So, I learnt a new thing here about the “RESTORE FILELISTONLY” command. This command tell us what all Database files (MDF, NDF, LDF) a Backup file contains without actually Restoring the Database.

Categories: DBA Stuff, SQL Errors Tags: ,

SQL Basics – Backup and Restore Database in SQL Server

June 5, 2010 1 comment

–> SQL Script to take Backup of a database:

USE [master]
GO

BACKUP DATABASE [AdventureWorks2014]
	TO  DISK = N'D:\SQL\AdventureWorks2014.bak' WITH NOFORMAT, 
	NOINIT,  
	NAME = N'AdventureWorks2014-Full Database Backup', 
	SKIP, 
	NOREWIND, 
	NOUNLOAD,  
	STATS = 10
GO

 

–> SQL Script to Restore a Backup file:

USE [master]
GO

RESTORE DATABASE [TestManDB2] FROM  DISK = N'D:\SQL\TestManDB.bak' WITH FILE = 1,  
	MOVE N'TestManDB' TO N'D:\MSSQL\DATA\TestManDB2.mdf',  
	MOVE N'TestManDB_log' TO N'D:\MSSQL\DATA\TestManDB2_log.ldf',  
	NOUNLOAD,  
	STATS = 5
GO

 

–> Video on how to backup and restore a database:

Categories: SQL Basics Tags: ,