Options
Proper steps to restore a filegroup
Osolage
Posts: 15
Greetings,
I have a database that is nearly 500 GB at this point. Due to our ETL process, I find it necessary to restore the production database to ETL database server, run the ETL on ETL database server and then restore the ETL database back to production.
The challenge I have is that we allow users to continue data processing in production while new data is being loaded into the ETL database. So by the time that I've finished restoring the ETL database to production, the data that was already in production has changed since the ETL database was last restored from production.
Until recently, the amount of transactional data was small enough to use SQL Data Compare to bring the ETL database back in sync with production. However, there are 10+ GB of transactional data now. SQL Data Compare just isn't efficient enough for the sync process anymore. And using SSIS to move the data is more cumbersome than I had hoped.
My thought is to put the transactional data on a different file or filegroup and backup/restore that after the ETL has completed. Seems like a reasonable process, yes?
So I set out to test that theory. I have created a small database, named 'FG_Test', with two tables (dbo.Pronouns and dbo.Verbs). Verbs table is on the PRIMARY filegroup. Pronouns table is on a filegroup named 'Project_Data'. I added some records to both tables, did a full backup on the database and then restored the backup as database named 'FG_Test2'. I added some extra records to the FG_Test2.dbo.Pronouns table just to have a difference between the restored copy of the database and the original.
Next, I backed up the 'Project_Data' filegroup of the original 'FG_Test' database and attempted to restore the filegroup to the copy which had extra records. My expectation was that the copy would no longer have the 2 extra records in the Pronouns table. However, I can't access the 'FG_Test2' database now. Here's what I showed up in the e-mail that I received from SQL Backup after the filegroup restore was complete:
"This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point."
What am I supposed to do at this point? Should I have also done a transaction log backup/restore? Is there a good guide to read concerning doing filegroup backups/restores with SQL Backup?
Thanks in advance,
Ra Osolage
Listening to: Zhané - Hey Mr. D.J.
via FoxyTunes
I have a database that is nearly 500 GB at this point. Due to our ETL process, I find it necessary to restore the production database to ETL database server, run the ETL on ETL database server and then restore the ETL database back to production.
The challenge I have is that we allow users to continue data processing in production while new data is being loaded into the ETL database. So by the time that I've finished restoring the ETL database to production, the data that was already in production has changed since the ETL database was last restored from production.
Until recently, the amount of transactional data was small enough to use SQL Data Compare to bring the ETL database back in sync with production. However, there are 10+ GB of transactional data now. SQL Data Compare just isn't efficient enough for the sync process anymore. And using SSIS to move the data is more cumbersome than I had hoped.
My thought is to put the transactional data on a different file or filegroup and backup/restore that after the ETL has completed. Seems like a reasonable process, yes?
So I set out to test that theory. I have created a small database, named 'FG_Test', with two tables (dbo.Pronouns and dbo.Verbs). Verbs table is on the PRIMARY filegroup. Pronouns table is on a filegroup named 'Project_Data'. I added some records to both tables, did a full backup on the database and then restored the backup as database named 'FG_Test2'. I added some extra records to the FG_Test2.dbo.Pronouns table just to have a difference between the restored copy of the database and the original.
Next, I backed up the 'Project_Data' filegroup of the original 'FG_Test' database and attempted to restore the filegroup to the copy which had extra records. My expectation was that the copy would no longer have the 2 extra records in the Pronouns table. However, I can't access the 'FG_Test2' database now. Here's what I showed up in the e-mail that I received from SQL Backup after the filegroup restore was complete:
"This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point."
What am I supposed to do at this point? Should I have also done a transaction log backup/restore? Is there a good guide to read concerning doing filegroup backups/restores with SQL Backup?
Thanks in advance,
Ra Osolage
Listening to: Zhané - Hey Mr. D.J.
via FoxyTunes
Thanks,
Ra Osolage
Ra Osolage
Comments
My suggestion would be to first start off using standard SQL Server syntax. Once you have the bckup and recovery steps in place, convert the syntax to SQL Backup syntax. In most cases, this requires only that EXEC master..sqlbackup '-sql " be appended to the command, replacing all single quotes with escaped single quotes, and ending the command with "'.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8