How to restore and backup MSSQL database on another instance?

Question

SQL Server

Hello.
I have an ancient MSSQLServer 2005 with a 3 gig database and a 300 gig log file.
The log is full - the database is not working. Shrink is not working.
I tried to detach the database, it won't attach back - it swears at the log file, says it's full. Expand the backup nearby does not allow free space.
I want to deploy a backup on another 2016 EXPRESS server, backup it there and deploy it to 2005.
Will such a scheme work or will there be problems due to other versions?

Answer 1

Shrink is not working.

BACKUP LOG [General] WITH NO_LOG
GO
--
DBCC SHRINKDATABASE ([General])
GO

I want to deploy a backup on another 2016 EXPRESS server, backup it there and deploy it to 2005.

MS SQL is not backwards compatible.
Restore backup to a different version of SQL...
Or truncate the transaction log .
Connect the database file itself (mdf), without the log (FOR ATTACH_REBUILD_LOG)
And the last option is to install SQL Server 2005 Express on a computer with 2016 EXPRESS - it can be done.

Answer 2

I also wanted to write that on the remaining MDF file, which could not be attached back due to an error with an overflowing log, a forced attachment worked using the following command:

USE [master]
GO
CREATE DATABASE [TestDB] ON 
( FILENAME = N'E:\MSSQL\TestDBCopy.mdf' )
 FOR ATTACH_FORCE_REBUILD_LOG  
GO

An error came out that the log was not from this database along the old path, but a new log was created with a size of 512 kb.
The base is complete. That's it.