0

I had to prepare testing environment with SQL Server 2014 database. On PROD DB has two file group PRIMARY (operational - 50 GB) and STROAGE (archive - 1100 GB).

In TEST environment I have copy of database from 6 months ago, it hasn't got STORAGE filegroup but of course PRIMARY exists.

I need on TEST only PRIMARY filegroup. I've backuped from PROD:

BACKUP  DATABASE [base] FILEGROUP = 'PRIMARY' TO DISK = D:\ WITH COMPRESSION

After recovering on TEST

RESTORE DATABASE KW_GRECOS
   FILE = 'KW_DBASE',
   FILEGROUP = 'PRIMARY'
FROM DISK = 'd:\Backup_2018-01-08 18.50.01.flg'
   WITH noRECOVERY

Data base is still in Recovery mode.

What is the simplest method to copy only one filegroup, I want only actual data in tables.

Peter_K
  • 93
  • 1
  • 10
  • You *did* specify `NORECOVERY`. That's used when you need to restore more backups, eg a differential and log after a full backup, so you don't want the server to start the recovery process until you finish. The last step in the process is to call `RESTORE DATABASE MyDatabase WITH RECOVERY;`. Check [Restore Sequence and Understanding NORECOVERY and RECOVERY](https://blog.sqlauthority.com/2009/07/15/sql-server-restore-sequence-and-understanding-norecovery-and-recovery/). – Panagiotis Kanavos Jan 11 '18 at 10:39
  • With RECOVERY didn't work neither. There was failure. – Peter_K Jan 11 '18 at 10:54
  • What failure? What was the message? Did you try the restore *without* specifying the `NORECOVERY` option? The process required to restore individual files *is* described [in the documentation](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-files-and-filegroups-sql-server). Depending on the recovery model of the database, you may need a log backup too. You can also perform the RESTORE operation from SSMS and even script the commands it would execute. – Panagiotis Kanavos Jan 11 '18 at 11:03

0 Answers0