Getting Error when restoring database without bak extension

Soumya Ps

8y1.6k

TITLE: Microsoft SQL Server Management Studio

------------------------------

Restore failed for Server 'DESKTOP-OMAQMEO'. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1846+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The database was backed up on a server running version 12.00.2269. That version is incompatible with this server, which is running version 11.00.2100. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.

RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3169)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=3169&LinkId=20476

------------------------------

BUTTONS:

------------------------------

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah8y ago
Accepted answer
Yes you can Restore from a higher SQL server version to a lower SQL Server version
but I not suggesting it

In this sample i am going to assume that you have SQL2016 and would like to get a backup for a SQL2012
ALTER DATABASE dbname SET SINGLE_USER
GO
--  SQLSever 2008 is   100
--  SQLSever 2012 is   110
--  SQLSever 2014 is   120
--  SQLSever 2016 is   130
sp_dbcmptlevel 'dbname','110'
GO

BACKUP DATABASE dbname TO  DISK = N'C:\SQLServer\SQLBackup\dbname.bak'
WITH NOFORMAT, NOINIT,  NAME = N'dbname-Full Database Backup'
, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

sp_dbcmptlevel 'dbname','130'
GO

ALTER DATABASE dbname SET MULTI_USER
GO
good luck
Shahriar Nikkhah8y ago
Just forgot to get a backup first
check the DB names in the backaup command
BACKUP DATABASE dbname TO  DISK = N'C:\SQLServer\SQLBackup\dbname.bak'
WITH NOFORMAT, NOINIT,  NAME = N'dbname-Full Database Backup'
, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

ALTER DATABASE dbname SET SINGLE_USER
GO
--  SQLSever 2008 is   100
--  SQLSever 2012 is   110
--  SQLSever 2014 is   120
--  SQLSever 2016 is   130
sp_dbcmptlevel 'dbname','110'
GO

BACKUP DATABASE dbname TO  DISK = N'C:\SQLServer\SQLBackup\dbnameSQL2012.bak'
WITH NOFORMAT, NOINIT,  NAME = N'dbname-Full Database Backup'
, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

sp_dbcmptlevel 'dbname','130'
GO

ALTER DATABASE dbname SET MULTI_USER
GO
Soumya Ps8y ago
Thank You...............:)
Rafnas T P8y ago
hi,
restore database from wich version of sql server you have taken, you can not restore into lower version of sql server, for example, you have taken back up from sql server 2014 and you are trying to restore into sql server 2012 which is not possible
Rafnas T P8y ago
hi,
this error is due to in capability,
you have taken backup of database from higher version of sql server, and you are trying to restore into lower version