May 4, 2016 at 9:54 am
"Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'DATABASE2' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
"
CREATE DATABASE [DATABASE1]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'DATABASE1', FILENAME = N'E:\SQLDATA\DATABASE1.mdf' , SIZE = 22118400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
FILEGROUP [ARCHIVE]
( NAME = N'ARCHIVE', FILENAME = N'E:\SQLDATA\DATABASE1_ARCHIVE.bak' , SIZE = 5120000KB , MAXSIZE = 5120000KB , FILEGROWTH = 5120000KB )
LOG ON
( NAME = N'DATABASE1_log', FILENAME = N'F:\SQLLOG\DATABASE1_log.ldf' , SIZE = 35942400KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
GO
CREATE DATABASE [DATABASE2]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'DATABASE2', FILENAME = N'E:\SQLDATA\DATABASE2.mdf' , SIZE = 22118400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
FILEGROUP [ARCHIVE]
( NAME = N'ARCHIVE', FILENAME = N'E:\SQLDATA\DATABASE2_ARCHIVE.bak' , SIZE = 5120000KB , MAXSIZE = 5120000KB , FILEGROWTH = 5120000KB )
LOG ON
( NAME = N'DATABASE2_log', FILENAME = N'F:\SQLLOG\DATABASE2_log.ldf' , SIZE = 35942400KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [DATABASE1] MODIFY FILEGROUP ARCHIVE READ_ONLY
ALTER DATABASE [DATABASE2] MODIFY FILEGROUP ARCHIVE READ_ONLY
BACKUP DATABASE DATABASE1
FILE = 'ARCHIVE'
TO DISK = 'E:\SQLDATA\DATABASE1_ARCHIVE_BACKUP.bck';
GO
EXEC sp_addumpdevice 'disk', 'DATABASE1', 'E:\SQLDATA\DATABASE1_ARCHIVE_BACKUP.bck';
RESTORE DATABASE DATABASE2
FILE = 'ARCHIVE',
FILEGROUP = 'ARCHIVE'
FROM DATABASE1
WITH NORECOVERY;
GO
CREATE DATABASE [DATABASE1]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'DATABASE1', FILENAME = N'E:\SQLDATA\DATABASE1.mdf' , SIZE = 22118400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
FILEGROUP [ARCHIVE]
( NAME = N'ARCHIVE', FILENAME = N'E:\SQLDATA\DATABASE1_ARCHIVE.bak' , SIZE = 5120000KB , MAXSIZE = 5120000KB , FILEGROWTH = 5120000KB )
LOG ON
( NAME = N'DATABASE1_log', FILENAME = N'F:\SQLLOG\DATABASE1_log.ldf' , SIZE = 35942400KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
GO
CREATE DATABASE [DATABASE2]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'DATABASE2', FILENAME = N'E:\SQLDATA\DATABASE2.mdf' , SIZE = 22118400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
FILEGROUP [ARCHIVE]
( NAME = N'ARCHIVE', FILENAME = N'E:\SQLDATA\DATABASE2_ARCHIVE.bak' , SIZE = 5120000KB , MAXSIZE = 5120000KB , FILEGROWTH = 5120000KB )
LOG ON
( NAME = N'DATABASE2_log', FILENAME = N'F:\SQLLOG\DATABASE2_log.ldf' , SIZE = 35942400KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [DATABASE1] MODIFY FILEGROUP ARCHIVE READ_ONLY
ALTER DATABASE [DATABASE2] MODIFY FILEGROUP ARCHIVE READ_ONLY
BACKUP DATABASE DATABASE1
FILE = 'ARCHIVE'
TO DISK = 'E:\SQLDATA\DATABASE1_ARCHIVE_BACKUP.bck';
GO
EXEC sp_addumpdevice 'disk', 'DATABASE1', 'E:\SQLDATA\DATABASE1_ARCHIVE_BACKUP.bck';
RESTORE DATABASE DATABASE2
FILE = 'ARCHIVE',
FILEGROUP = 'ARCHIVE'
FROM DATABASE1
WITH NORECOVERY;
GO
May 4, 2016 at 11:32 pm
Delete the device by using the following command to allow SQL Server to perform new backups to the backup device
BACKUP DATABASE mydatabase TO DISK='C:\MyDatabase.bak' with FORMAT
If the error message occurs during a restore operation, it may be possible to retrieve other backup sets from the device by specifying the file number. To determine if multiple backup sets are on a device, run the following code from Query Analyzer
RESTORE HEADERONLY FROM DISK='C:\MyDatabase.bak'
If this doesn't help then I suggest to use a 3rd party tool like: SQL Backup Database Restore[/url]
May 5, 2016 at 1:36 am
1. I dont want to take full backup and Restore.
2 .Particular File Group only I need to backup and Restore the filegroup into another Database.
May 5, 2016 at 2:41 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply