March 12, 2007 at 9:49 am
Hello,
I have this problem..
I am trying to make a file/filegroup backup on between diffrent SQL server:
In Server A
database1 created and empty, created a full backup to have same structure and same data (the db is created with some data for configuration)
the backup is stored in server B.
Then the db start filling with data for test, I want to move data that belong only to some filegroup, inthis case in server B I have a DB with useful data and not dirty used for test.
Then, I make a file/filegroup backup in server B.
then I make the tail log, and make the restore backup of filegroups,
then, i run restore of the tail, but I this point I got an error about:
File xxxxxxxhas been rolled forward to LSN zzzzzz. This log terminates at LSN yyyyy, which is too early to apply the WITH RECOVERY option. Reissue the RESTORE LOG statement WITH NORECOVERY.
Do you know how to restore a file/filegroup between different server?
Thank
March 12, 2007 at 11:13 am
Any help will very appreciated
thank
March 13, 2007 at 2:30 am
Any help if possible!
March 13, 2007 at 1:31 pm
You can do a partial restore to another server which includes the PRIMARY filegroup and the filegroup(s) you specify, but if you do it over the existing database it will mark all objects in other filegroups offline and inaccessable. I don't know if there is a way to make the entire database usable again.
You could do a partial restore to a new database WITH NORECOVERY, restore the tail log, then copy the restored data to the target database. See 'Partial Database Restore Operations' in Books Online for more details.
March 13, 2007 at 4:15 pm
Before,
you post it I tried with partial but I have the database offline for objects not belong to the others filegroups.
March 13, 2007 at 7:31 pm
That sounds exactly like what I said would happen.
Partial restore into an existing database makes the unrestored parts of the database unusable. You would only do this in extreme cases where the database is corrupt and you want to restore part of the database so critical data can be copied somewhere else before rebuilding the entire database.
Partial restore into a new database obviously only includes the specified filegroups, access to the objects in the missing filegroups is impossible.
I think you want to use partial restore to a new database, copy the data over to your working version, then drop the restored database.
March 14, 2007 at 2:27 am
I 'll do use DTS o other methods to reach the same goals,
Here it is written that is not possible to load data from a filegrup to a new empty database:
http://support.microsoft.com/?scid=kb%3Ben-us%3B281122&x=10&y=13
March 14, 2007 at 10:47 am
Here is what I found in Books Online that said you could do a partial restore to a different location. It doesn't say you'll get a complete working database, it says you'll get a partial database that you can copy data out of.
Application or user errors often affect an isolated portion of the database, such as a table. To support recovery from these events, Microsoft® SQL Server™ provides a mechanism to restore part of the database to another location so that the damaged or missing data can be copied back to the original database. For example, if an application erroneously dropped a table, you may want to restore only the part of the database that contained the table. Restoring log or differential backups can bring the table to a point prior to when the table was dropped. Then the content of the table can be extracted and reloaded into the original database.
Performing a partial restore operation is also useful when you are:
Partial restore operations work with database filegroups. The primary filegroup is always restored, along with the files that you specify and their corresponding filegroups. The result is a subset of the database. Filegroups that are not restored are marked as offline and are not accessible.
Somewhere in the middle of the page you linked to, it seems to agree:
Partial Database Restore Operations (SQL Server 2000)
A new PARTIAL clause has been added to the RESTORE T-SQL statement that provides a mechanism to restore part of the database to another location so that the damaged or missing data can be copied back to the original database. Partial restore operations work with database filegroups. For example, you have a database that consists of a primary filegroup, filegroup A and filegroup B. A table that resides on filegroup B is accidentally deleted. If you have filegroup and transaction log backups available, you can restore only filegroup B along with the primary filegroup to regain the deleted table. The RESTORE statement with the PARTIAL clause allows you to restore the subset to a new database or even to a different server. You can then extract and reload the content of the table into the original database.
March 14, 2007 at 11:22 am
In fact It said about a "the subset" it means only a part of DB, then this is used to reload into original database (ETL).
So there is no way to have a full DB accesible using from a new DB with same structure.
Thank
March 14, 2007 at 12:13 pm
I would be tempted to use DTS, or maybe script a series of BCP OUT and TRUNCATE TABLE/BCP IN commands. I even thought of snapshot replication, where you could create a publication containing only the tables you want copied, and manually start the snapshot agent and distribution agent when you wanted to sync the databases. The partial restore option is interesting but would probably not be my first choice. If speed was a major concern, I would try the following steps. (Some steps are unnecessary if the destination db is set for simple recovery.)
BCP OUT all desired tables
Remove destination db from trans log maintenance plan
Set destination db to simple recovery
Drop foreign keys and indexes on destination tables
Truncate destination tables
BCP IN all tables
Recreate indexes and foreign keys
Set destination db to full recovery
Run a full backup of destination db
Add destination db to trans log maintenance plan
Depending on the size of the data files and the connectivity between the servers, you might want to compress (zip) the data files before transferring them over the network.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply