I have a database with with two filegroups - the primary and one other.
Each filegroup has its own data file. I want to restore the database to a
different database server but restore only the primary filegroup.
When I try to do this, I get the following error for each of the data files
associated with the filegroups that I did not restore - File 'data file name
'
was only partially restored by a database or file restore. The entire file
must be successfully restored before applying the log.
Any ideas?
BACKUP DATABASE test1
FILEGROUP = 'Primary'
TO primfilegroup WITH INIT
BACKUP DATABASE test1
FILEGROUP = 'FG_RW'
TO rwfilegroup WITH INIT
BACKUP LOG test1
TO test1log WITH INIT
--BACKUP LOG test1
-- TO test1log WITH NOINIT
RESTORE DATABASE test2
FILEGROUP = 'Primary'
from primfilegroup WITH NORECOVERY,
MOVE 'test1_Data' TO 'i:\test2_Data.mdf',
MOVE 'test1_Log' TO 'i:\test2_Log.ldf'
GO
--RESTORE DATABASE test2
--FILEGROUP = 'FG_RW'
--from rwfilegroup WITH NORECOVERY,
-- MOVE 'test_RW' TO 'i:\test2_RW_Data.mdf',
-- MOVE 'test1_Log' TO 'i:\test2_Log.ldf'
--GO
--RESTORE LOG test2
-- FROM test1log
-- WITH FILE = 1, NORECOVERY
--go
RESTORE LOG test2
FROM test1log WITH RECOVERY,
MOVE 'test1_Log' TO 'i:\test2_Log.ldf'
GO
ERROR MESSAGE:
Msg 4320, Level 1, State 4320
File 'test_RW' was only partially restored by a database or file restore.
The entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.Hello, spoons
You also need to use the PARTIAL clause in the RESTORE statement. See
"partial restore operation" in Books Online.
Razvan
Showing posts with label filegroups. Show all posts
Showing posts with label filegroups. Show all posts
Wednesday, March 7, 2012
Restoring Files and Filesgroups
I have a database with with two filegroups - the primary and one other.
Each filegroup has its own data file. I want to restore the database to a
different database server but restore only the primary filegroup.
When I try to do this, I get the following error for each of the data files
associated with the filegroups that I did not restore - File 'data file name'
was only partially restored by a database or file restore. The entire file
must be successfully restored before applying the log.
Any ideas?
BACKUP DATABASE test1
FILEGROUP = 'Primary'
TO primfilegroup WITH INIT
BACKUP DATABASE test1
FILEGROUP = 'FG_RW'
TO rwfilegroup WITH INIT
BACKUP LOG test1
TO test1log WITH INIT
--BACKUP LOG test1
-- TO test1log WITH NOINIT
RESTORE DATABASE test2
FILEGROUP = 'Primary'
from primfilegroup WITH NORECOVERY,
MOVE 'test1_Data' TO 'i:\test2_Data.mdf',
MOVE 'test1_Log' TO 'i:\test2_Log.ldf'
GO
--RESTORE DATABASE test2
--FILEGROUP = 'FG_RW'
--from rwfilegroup WITH NORECOVERY,
-- MOVE 'test_RW' TO 'i:\test2_RW_Data.mdf',
-- MOVE 'test1_Log' TO 'i:\test2_Log.ldf'
--GO
--RESTORE LOG test2
-- FROM test1log
-- WITH FILE = 1, NORECOVERY
--go
RESTORE LOG test2
FROM test1log WITH RECOVERY,
MOVE 'test1_Log' TO 'i:\test2_Log.ldf'
GO
ERROR MESSAGE:
Msg 4320, Level 1, State 4320
File 'test_RW' was only partially restored by a database or file restore.
The entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Hello, spoons
You also need to use the PARTIAL clause in the RESTORE statement. See
"partial restore operation" in Books Online.
Razvan
Each filegroup has its own data file. I want to restore the database to a
different database server but restore only the primary filegroup.
When I try to do this, I get the following error for each of the data files
associated with the filegroups that I did not restore - File 'data file name'
was only partially restored by a database or file restore. The entire file
must be successfully restored before applying the log.
Any ideas?
BACKUP DATABASE test1
FILEGROUP = 'Primary'
TO primfilegroup WITH INIT
BACKUP DATABASE test1
FILEGROUP = 'FG_RW'
TO rwfilegroup WITH INIT
BACKUP LOG test1
TO test1log WITH INIT
--BACKUP LOG test1
-- TO test1log WITH NOINIT
RESTORE DATABASE test2
FILEGROUP = 'Primary'
from primfilegroup WITH NORECOVERY,
MOVE 'test1_Data' TO 'i:\test2_Data.mdf',
MOVE 'test1_Log' TO 'i:\test2_Log.ldf'
GO
--RESTORE DATABASE test2
--FILEGROUP = 'FG_RW'
--from rwfilegroup WITH NORECOVERY,
-- MOVE 'test_RW' TO 'i:\test2_RW_Data.mdf',
-- MOVE 'test1_Log' TO 'i:\test2_Log.ldf'
--GO
--RESTORE LOG test2
-- FROM test1log
-- WITH FILE = 1, NORECOVERY
--go
RESTORE LOG test2
FROM test1log WITH RECOVERY,
MOVE 'test1_Log' TO 'i:\test2_Log.ldf'
GO
ERROR MESSAGE:
Msg 4320, Level 1, State 4320
File 'test_RW' was only partially restored by a database or file restore.
The entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Hello, spoons
You also need to use the PARTIAL clause in the RESTORE statement. See
"partial restore operation" in Books Online.
Razvan
Restoring Files and Filesgroups
I have a database with with two filegroups - the primary and one other.
Each filegroup has its own data file. I want to restore the database to a
different database server but restore only the primary filegroup.
When I try to do this, I get the following error for each of the data files
associated with the filegroups that I did not restore - File 'data file name'
was only partially restored by a database or file restore. The entire file
must be successfully restored before applying the log.
Any ideas?
BACKUP DATABASE test1
FILEGROUP = 'Primary'
TO primfilegroup WITH INIT
BACKUP DATABASE test1
FILEGROUP = 'FG_RW'
TO rwfilegroup WITH INIT
BACKUP LOG test1
TO test1log WITH INIT
--BACKUP LOG test1
-- TO test1log WITH NOINIT
RESTORE DATABASE test2
FILEGROUP = 'Primary'
from primfilegroup WITH NORECOVERY,
MOVE 'test1_Data' TO 'i:\test2_Data.mdf',
MOVE 'test1_Log' TO 'i:\test2_Log.ldf'
GO
--RESTORE DATABASE test2
--FILEGROUP = 'FG_RW'
--from rwfilegroup WITH NORECOVERY,
-- MOVE 'test_RW' TO 'i:\test2_RW_Data.mdf',
-- MOVE 'test1_Log' TO 'i:\test2_Log.ldf'
--GO
--RESTORE LOG test2
-- FROM test1log
-- WITH FILE = 1, NORECOVERY
--go
RESTORE LOG test2
FROM test1log WITH RECOVERY,
MOVE 'test1_Log' TO 'i:\test2_Log.ldf'
GO
ERROR MESSAGE:
Msg 4320, Level 1, State 4320
File 'test_RW' was only partially restored by a database or file restore.
The entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.Hello, spoons
You also need to use the PARTIAL clause in the RESTORE statement. See
"partial restore operation" in Books Online.
Razvan
Each filegroup has its own data file. I want to restore the database to a
different database server but restore only the primary filegroup.
When I try to do this, I get the following error for each of the data files
associated with the filegroups that I did not restore - File 'data file name'
was only partially restored by a database or file restore. The entire file
must be successfully restored before applying the log.
Any ideas?
BACKUP DATABASE test1
FILEGROUP = 'Primary'
TO primfilegroup WITH INIT
BACKUP DATABASE test1
FILEGROUP = 'FG_RW'
TO rwfilegroup WITH INIT
BACKUP LOG test1
TO test1log WITH INIT
--BACKUP LOG test1
-- TO test1log WITH NOINIT
RESTORE DATABASE test2
FILEGROUP = 'Primary'
from primfilegroup WITH NORECOVERY,
MOVE 'test1_Data' TO 'i:\test2_Data.mdf',
MOVE 'test1_Log' TO 'i:\test2_Log.ldf'
GO
--RESTORE DATABASE test2
--FILEGROUP = 'FG_RW'
--from rwfilegroup WITH NORECOVERY,
-- MOVE 'test_RW' TO 'i:\test2_RW_Data.mdf',
-- MOVE 'test1_Log' TO 'i:\test2_Log.ldf'
--GO
--RESTORE LOG test2
-- FROM test1log
-- WITH FILE = 1, NORECOVERY
--go
RESTORE LOG test2
FROM test1log WITH RECOVERY,
MOVE 'test1_Log' TO 'i:\test2_Log.ldf'
GO
ERROR MESSAGE:
Msg 4320, Level 1, State 4320
File 'test_RW' was only partially restored by a database or file restore.
The entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.Hello, spoons
You also need to use the PARTIAL clause in the RESTORE statement. See
"partial restore operation" in Books Online.
Razvan
Restoring FileGroups...
We are currently in the process of designing a Consolidated System (data for multiple organizations stored in one database server) using SQL Server 2005. One of the design goal is to refresh individual company data from Production to "User Acceptance Server" without affecting other companies.
So, we thought of implementing this via Partitioning by Org. We need the "Proof of concept" that this can be done with a simple example. Since our DBA's are also new to SQL 2005, we are working together in achieving the goal. Ok, here is what we are trying to achieve:
1. Create a Database (Test1) with two file Groups (FileGroup1, FileGroup2) with two files in each file group.
2. Create two tables on this database, one on each FileGroup
Table 1 on FileGroup1, Table 2 on FileGroup2.
3. Populate data on these tables.
4. Create an exact copy of this database with the above architecture and call it Test2.
5. Populate Test2, with a different set of data.
6. Backup FileGroup2 on Test2 and restore it to Test1 database.
What we did:
1. I did the Full Database Backup of Test2 via SQL Management Studio. Also the log file with:I did the Full Database Backup of Test2 via SQL Management Studio. Also the log file using :
BACKUP LOG PGM TO DISK='D:\Test2_Log.bak' WITH NORECOVERY
2. Restore the database filegroup using:
RESTORE DATABASE Test1
FILEGROUP = 'FileGroup2'
FROM DISK ='D:\Test2.bak' WITH
MOVE 'FileName2_A' TO 'G:\Data\File2_A.ndf',
MOVE 'FileName2_B' TO 'G:\Data\File2_B.ndf'
3. . RESTORE LOG PGM
FROM DISK = 'D:\Test2_Log.bak'
WITH RECOVERY
After restoring the backup filegroup to Test1 database, I'm not able to query the table that resides in the filegroup that was restored. Error returned is:
Msg 8653, Level 16, State 1, Line 2
The query processor is unable to produce a plan for the table or view 'Junk2' because the table resides in a filegroup which is not online.
We know, we are missing some pieces while creating the backup or restoring that is causing this problem. If somebody can point it out, or provide us a "Working Example"...
Thanks for your help.
Take a full backup
BACKUP DATABASE _BIMAL_FG_TEST
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
GO
Or take File group backups
BACKUP DATABASE _BIMAL_FG_TEST
FILEGROUP = 'PRIMARY'
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FG_PRIMARY.bak'
BACKUP DATABASE _BIMAL_FG_TEST
FILEGROUP = 'FileGroup_A'
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FG_A.bak'
BACKUP DATABASE _BIMAL_FG_TEST
FILEGROUP = 'FileGroup_B'
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FG_B.bak'
You must take a log backup
BACKUP LOG _BIMAL_FG_TEST
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_LOG.TRN'
On the target database you have to first restore the Primary FG
RESTORE DATABASE _BIMAL_FG_TEST_COPY
FILEGROUP = 'PRIMARY'
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
WITH MOVE '_BIMAL_FG_TEST' to 'O:\SQLData\_BIMAL_FG_TEST_COPY.mdf'
, MOVE '_BIMAL_FG_TEST_log' to 'P:\SQLLog\_BIMAL_FG_TEST_COPY_log.LDF'
,STATS=20
,NORECOVERY, REPLACE, PARTIAL
Then Restore the FileGroup you want
RESTORE DATABASE _BIMAL_FG_TEST_COPY
FILEGROUP = 'FileGroup_A'
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
WITH MOVE 'BIMAL_FILEGROUP_TEST_1' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_1.ndf'
,MOVE 'BIMAL_FILEGROUP_TEST_2' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_2.ndf'
,STATS=20
,REPLACE
,NORECOVERY
And then restore the Log backup to complete the recovery
RESTORE LOG _BIMAL_FG_TEST_COPY
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_LOG.TRN'
WITH RECOVERY
And if you want to restore another file group just followt the last two steps.
RESTORE DATABASE _BIMAL_FG_TEST_COPY
FILEGROUP = 'FileGroup_B'
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
WITH MOVE 'BIMAL_FILEGROUP_TEST_3' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_3.ndf'
,MOVE 'BIMAL_FILEGROUP_TEST_4' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_4.ndf'
,STATS=20
,NORECOVERY
RESTORE LOG _BIMAL_FG_TEST_COPY
FROM DISK = 'OTest_Backups\_BIMAL_FG_TEST_LOG.TRN'
WITH RECOVERY
This may not solve your problem but this will give you a working example you can play to see whether what you need is possible or not.
Restoring FileGroups...
We are currently in the process of designing a Consolidated System (data for multiple organizations stored in one database server) using SQL Server 2005. One of the design goal is to refresh individual company data from Production to "User Acceptance Server" without affecting other companies.
So, we thought of implementing this via Partitioning by Org. We need the "Proof of concept" that this can be done with a simple example. Since our DBA's are also new to SQL 2005, we are working together in achieving the goal. Ok, here is what we are trying to achieve:
1. Create a Database (Test1) with two file Groups (FileGroup1, FileGroup2) with two files in each file group.
2. Create two tables on this database, one on each FileGroup
Table 1 on FileGroup1, Table 2 on FileGroup2.
3. Populate data on these tables.
4. Create an exact copy of this database with the above architecture and call it Test2.
5. Populate Test2, with a different set of data.
6. Backup FileGroup2 on Test2 and restore it to Test1 database.
What we did:
1. I did the Full Database Backup of Test2 via SQL Management Studio. Also the log file with:I did the Full Database Backup of Test2 via SQL Management Studio. Also the log file using :
BACKUP LOG PGM TO DISK='D:\Test2_Log.bak' WITH NORECOVERY
2. Restore the database filegroup using:
RESTORE DATABASE Test1
FILEGROUP = 'FileGroup2'
FROM DISK ='D:\Test2.bak' WITH
MOVE 'FileName2_A' TO 'G:\Data\File2_A.ndf',
MOVE 'FileName2_B' TO 'G:\Data\File2_B.ndf'
3. . RESTORE LOG PGM
FROM DISK = 'D:\Test2_Log.bak'
WITH RECOVERY
After restoring the backup filegroup to Test1 database, I'm not able to query the table that resides in the filegroup that was restored. Error returned is:
Msg 8653, Level 16, State 1, Line 2
The query processor is unable to produce a plan for the table or view 'Junk2' because the table resides in a filegroup which is not online.
We know, we are missing some pieces while creating the backup or restoring that is causing this problem. If somebody can point it out, or provide us a "Working Example"...
Thanks for your help.
Take a full backup
BACKUP DATABASE _BIMAL_FG_TEST
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
GO
Or take File group backups
BACKUP DATABASE _BIMAL_FG_TEST
FILEGROUP = 'PRIMARY'
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FG_PRIMARY.bak'
BACKUP DATABASE _BIMAL_FG_TEST
FILEGROUP = 'FileGroup_A'
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FG_A.bak'
BACKUP DATABASE _BIMAL_FG_TEST
FILEGROUP = 'FileGroup_B'
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FG_B.bak'
You must take a log backup
BACKUP LOG _BIMAL_FG_TEST
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_LOG.TRN'
On the target database you have to first restore the Primary FG
RESTORE DATABASE _BIMAL_FG_TEST_COPY
FILEGROUP = 'PRIMARY'
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
WITH MOVE '_BIMAL_FG_TEST' to 'O:\SQLData\_BIMAL_FG_TEST_COPY.mdf'
, MOVE '_BIMAL_FG_TEST_log' to 'P:\SQLLog\_BIMAL_FG_TEST_COPY_log.LDF'
,STATS=20
,NORECOVERY, REPLACE, PARTIAL
Then Restore the FileGroup you want
RESTORE DATABASE _BIMAL_FG_TEST_COPY
FILEGROUP = 'FileGroup_A'
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
WITH MOVE 'BIMAL_FILEGROUP_TEST_1' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_1.ndf'
,MOVE 'BIMAL_FILEGROUP_TEST_2' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_2.ndf'
,STATS=20
,REPLACE
,NORECOVERY
And then restore the Log backup to complete the recovery
RESTORE LOG _BIMAL_FG_TEST_COPY
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_LOG.TRN'
WITH RECOVERY
And if you want to restore another file group just followt the last two steps.
RESTORE DATABASE _BIMAL_FG_TEST_COPY
FILEGROUP = 'FileGroup_B'
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
WITH MOVE 'BIMAL_FILEGROUP_TEST_3' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_3.ndf'
,MOVE 'BIMAL_FILEGROUP_TEST_4' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_4.ndf'
,STATS=20
,NORECOVERY
RESTORE LOG _BIMAL_FG_TEST_COPY
FROM DISK = 'OTest_Backups\_BIMAL_FG_TEST_LOG.TRN'
WITH RECOVERY
This may not solve your problem but this will give you a working example you can play to see whether what you need is possible or not.
Restoring filegroups in 2005.
Howdy all. I want to be able to restore just 1 filegroup from a filegroup backup, without having to restore the log afterwords even if the data has changed post filegroup backup. While this would leave the data inconsistant, it may be desired at times. From BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c073049100f0.htm
************************************************** ******
If the files have been modified after the file backup was created, execute the RESTORE LOG statement to apply the transaction log backup, specifying:
The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be restored.
The NORECOVERY clause if you have another transaction log backup to apply after the current one; otherwise, specify the RECOVERY clause.
The transaction log backups, if applied, must cover the time when the files and filegroups were backed up until the end of log (unless ALL database files are restored).
************************************************** ******
But thats just it, I may not want to take this action. Maybe some data got deleted from a set of tables in a filegroup right after I took my last filegroup backup, so I just want to restore that filegroup back to that backup, but leave my other filegroups alone. Restoring the log brings the data back to a consistant state when the log was backed up, which may not be what I want.
Can this be accomplished?
TIA, cfrA very knowledgeable guru clarified that this would not be possible.|||Only way to do this would be to restore the entire database to the desired opint in time, then transfer the data from the one filegroup to the existing database. NOTE: This will likely fail any foreign key relationships among these tables, and could result in a pile of orphaned data.
************************************************** ******
If the files have been modified after the file backup was created, execute the RESTORE LOG statement to apply the transaction log backup, specifying:
The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be restored.
The NORECOVERY clause if you have another transaction log backup to apply after the current one; otherwise, specify the RECOVERY clause.
The transaction log backups, if applied, must cover the time when the files and filegroups were backed up until the end of log (unless ALL database files are restored).
************************************************** ******
But thats just it, I may not want to take this action. Maybe some data got deleted from a set of tables in a filegroup right after I took my last filegroup backup, so I just want to restore that filegroup back to that backup, but leave my other filegroups alone. Restoring the log brings the data back to a consistant state when the log was backed up, which may not be what I want.
Can this be accomplished?
TIA, cfrA very knowledgeable guru clarified that this would not be possible.|||Only way to do this would be to restore the entire database to the desired opint in time, then transfer the data from the one filegroup to the existing database. NOTE: This will likely fail any foreign key relationships among these tables, and could result in a pile of orphaned data.
Restoring filegroups in 2005.
Howdy all. I want to be able to restore just 1 filegroup from a filegroup
backup, without having to restore the log afterwords even if the data has
changed post filegroup backup. While this would leave the data inconsistant,
it may be desired at times. From BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c073049100f0.htm
************************************************** ******
If the files have been modified after the file backup was created, execute
the RESTORE LOG statement to apply the transaction log backup, specifying:
The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be restored.
The NORECOVERY clause if you have another transaction log backup to apply
after the current one; otherwise, specify the RECOVERY clause.
The transaction log backups, if applied, must cover the time when the files
and filegroups were backed up until the end of log (unless ALL database files
are restored).
************************************************** ******
But thats just it, I may not want to take this action. Maybe some data got
deleted from a set of tables in a filegroup right after I took my last
filegroup backup, so I just want to restore that filegroup back to that
backup, but leave my other filegroups alone. Restoring the log brings the
data back to a consistant state when the log was backed up, which may not be
what I want.
Can this be accomplished?
TIA, ChrisR
The short answer is that SQL Server will not assist in providing you with an inconsistent database.
A database would be inconsistent if different parts of it are from different points in time. And
this is indeed what you want to achieve.
Your option is to restore this filegroup backup to a new database (using the PARTIAL option of the
restore command - see BOL for details), and then copy over the relevant data to your production
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:5774CAB8-3FAC-4DF6-BCF8-1FA12E73559E@.microsoft.com...
> Howdy all. I want to be able to restore just 1 filegroup from a filegroup
> backup, without having to restore the log afterwords even if the data has
> changed post filegroup backup. While this would leave the data inconsistant,
> it may be desired at times. From BOL:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c073049100f0.htm
> ************************************************** ******
> If the files have been modified after the file backup was created, execute
> the RESTORE LOG statement to apply the transaction log backup, specifying:
> The name of the database to which the transaction log will be applied.
> The backup device from where the transaction log backup will be restored.
> The NORECOVERY clause if you have another transaction log backup to apply
> after the current one; otherwise, specify the RECOVERY clause.
> The transaction log backups, if applied, must cover the time when the files
> and filegroups were backed up until the end of log (unless ALL database files
> are restored).
> ************************************************** ******
> But thats just it, I may not want to take this action. Maybe some data got
> deleted from a set of tables in a filegroup right after I took my last
> filegroup backup, so I just want to restore that filegroup back to that
> backup, but leave my other filegroups alone. Restoring the log brings the
> data back to a consistant state when the log was backed up, which may not be
> what I want.
> Can this be accomplished?
> TIA, ChrisR
|||That's what I thought.
Thanks Tibor.
"Tibor Karaszi" wrote:
> The short answer is that SQL Server will not assist in providing you with an inconsistent database.
> A database would be inconsistent if different parts of it are from different points in time. And
> this is indeed what you want to achieve.
> Your option is to restore this filegroup backup to a new database (using the PARTIAL option of the
> restore command - see BOL for details), and then copy over the relevant data to your production
> database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:5774CAB8-3FAC-4DF6-BCF8-1FA12E73559E@.microsoft.com...
>
>
backup, without having to restore the log afterwords even if the data has
changed post filegroup backup. While this would leave the data inconsistant,
it may be desired at times. From BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c073049100f0.htm
************************************************** ******
If the files have been modified after the file backup was created, execute
the RESTORE LOG statement to apply the transaction log backup, specifying:
The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be restored.
The NORECOVERY clause if you have another transaction log backup to apply
after the current one; otherwise, specify the RECOVERY clause.
The transaction log backups, if applied, must cover the time when the files
and filegroups were backed up until the end of log (unless ALL database files
are restored).
************************************************** ******
But thats just it, I may not want to take this action. Maybe some data got
deleted from a set of tables in a filegroup right after I took my last
filegroup backup, so I just want to restore that filegroup back to that
backup, but leave my other filegroups alone. Restoring the log brings the
data back to a consistant state when the log was backed up, which may not be
what I want.
Can this be accomplished?
TIA, ChrisR
The short answer is that SQL Server will not assist in providing you with an inconsistent database.
A database would be inconsistent if different parts of it are from different points in time. And
this is indeed what you want to achieve.
Your option is to restore this filegroup backup to a new database (using the PARTIAL option of the
restore command - see BOL for details), and then copy over the relevant data to your production
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:5774CAB8-3FAC-4DF6-BCF8-1FA12E73559E@.microsoft.com...
> Howdy all. I want to be able to restore just 1 filegroup from a filegroup
> backup, without having to restore the log afterwords even if the data has
> changed post filegroup backup. While this would leave the data inconsistant,
> it may be desired at times. From BOL:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c073049100f0.htm
> ************************************************** ******
> If the files have been modified after the file backup was created, execute
> the RESTORE LOG statement to apply the transaction log backup, specifying:
> The name of the database to which the transaction log will be applied.
> The backup device from where the transaction log backup will be restored.
> The NORECOVERY clause if you have another transaction log backup to apply
> after the current one; otherwise, specify the RECOVERY clause.
> The transaction log backups, if applied, must cover the time when the files
> and filegroups were backed up until the end of log (unless ALL database files
> are restored).
> ************************************************** ******
> But thats just it, I may not want to take this action. Maybe some data got
> deleted from a set of tables in a filegroup right after I took my last
> filegroup backup, so I just want to restore that filegroup back to that
> backup, but leave my other filegroups alone. Restoring the log brings the
> data back to a consistant state when the log was backed up, which may not be
> what I want.
> Can this be accomplished?
> TIA, ChrisR
|||That's what I thought.
Thanks Tibor.
"Tibor Karaszi" wrote:
> The short answer is that SQL Server will not assist in providing you with an inconsistent database.
> A database would be inconsistent if different parts of it are from different points in time. And
> this is indeed what you want to achieve.
> Your option is to restore this filegroup backup to a new database (using the PARTIAL option of the
> restore command - see BOL for details), and then copy over the relevant data to your production
> database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:5774CAB8-3FAC-4DF6-BCF8-1FA12E73559E@.microsoft.com...
>
>
Restoring filegroups in 2005.
Howdy all. I want to be able to restore just 1 filegroup from a filegroup
backup, without having to restore the log afterwords even if the data has
changed post filegroup backup. While this would leave the data inconsistant,
it may be desired at times. From BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c073049100f0.htm
************************************************** ******
If the files have been modified after the file backup was created, execute
the RESTORE LOG statement to apply the transaction log backup, specifying:
The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be restored.
The NORECOVERY clause if you have another transaction log backup to apply
after the current one; otherwise, specify the RECOVERY clause.
The transaction log backups, if applied, must cover the time when the files
and filegroups were backed up until the end of log (unless ALL database files
are restored).
************************************************** ******
But thats just it, I may not want to take this action. Maybe some data got
deleted from a set of tables in a filegroup right after I took my last
filegroup backup, so I just want to restore that filegroup back to that
backup, but leave my other filegroups alone. Restoring the log brings the
data back to a consistant state when the log was backed up, which may not be
what I want.
Can this be accomplished?
TIA, ChrisRThe short answer is that SQL Server will not assist in providing you with an inconsistent database.
A database would be inconsistent if different parts of it are from different points in time. And
this is indeed what you want to achieve.
Your option is to restore this filegroup backup to a new database (using the PARTIAL option of the
restore command - see BOL for details), and then copy over the relevant data to your production
database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:5774CAB8-3FAC-4DF6-BCF8-1FA12E73559E@.microsoft.com...
> Howdy all. I want to be able to restore just 1 filegroup from a filegroup
> backup, without having to restore the log afterwords even if the data has
> changed post filegroup backup. While this would leave the data inconsistant,
> it may be desired at times. From BOL:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c073049100f0.htm
> ************************************************** ******
> If the files have been modified after the file backup was created, execute
> the RESTORE LOG statement to apply the transaction log backup, specifying:
> The name of the database to which the transaction log will be applied.
> The backup device from where the transaction log backup will be restored.
> The NORECOVERY clause if you have another transaction log backup to apply
> after the current one; otherwise, specify the RECOVERY clause.
> The transaction log backups, if applied, must cover the time when the files
> and filegroups were backed up until the end of log (unless ALL database files
> are restored).
> ************************************************** ******
> But thats just it, I may not want to take this action. Maybe some data got
> deleted from a set of tables in a filegroup right after I took my last
> filegroup backup, so I just want to restore that filegroup back to that
> backup, but leave my other filegroups alone. Restoring the log brings the
> data back to a consistant state when the log was backed up, which may not be
> what I want.
> Can this be accomplished?
> TIA, ChrisR|||That's what I thought.
Thanks Tibor.
"Tibor Karaszi" wrote:
> The short answer is that SQL Server will not assist in providing you with an inconsistent database.
> A database would be inconsistent if different parts of it are from different points in time. And
> this is indeed what you want to achieve.
> Your option is to restore this filegroup backup to a new database (using the PARTIAL option of the
> restore command - see BOL for details), and then copy over the relevant data to your production
> database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:5774CAB8-3FAC-4DF6-BCF8-1FA12E73559E@.microsoft.com...
> > Howdy all. I want to be able to restore just 1 filegroup from a filegroup
> > backup, without having to restore the log afterwords even if the data has
> > changed post filegroup backup. While this would leave the data inconsistant,
> > it may be desired at times. From BOL:
> > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c073049100f0.htm
> >
> > ************************************************** ******
> > If the files have been modified after the file backup was created, execute
> > the RESTORE LOG statement to apply the transaction log backup, specifying:
> >
> > The name of the database to which the transaction log will be applied.
> > The backup device from where the transaction log backup will be restored.
> > The NORECOVERY clause if you have another transaction log backup to apply
> > after the current one; otherwise, specify the RECOVERY clause.
> > The transaction log backups, if applied, must cover the time when the files
> > and filegroups were backed up until the end of log (unless ALL database files
> > are restored).
> > ************************************************** ******
> >
> > But thats just it, I may not want to take this action. Maybe some data got
> > deleted from a set of tables in a filegroup right after I took my last
> > filegroup backup, so I just want to restore that filegroup back to that
> > backup, but leave my other filegroups alone. Restoring the log brings the
> > data back to a consistant state when the log was backed up, which may not be
> > what I want.
> >
> > Can this be accomplished?
> >
> > TIA, ChrisR
>
>
backup, without having to restore the log afterwords even if the data has
changed post filegroup backup. While this would leave the data inconsistant,
it may be desired at times. From BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c073049100f0.htm
************************************************** ******
If the files have been modified after the file backup was created, execute
the RESTORE LOG statement to apply the transaction log backup, specifying:
The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be restored.
The NORECOVERY clause if you have another transaction log backup to apply
after the current one; otherwise, specify the RECOVERY clause.
The transaction log backups, if applied, must cover the time when the files
and filegroups were backed up until the end of log (unless ALL database files
are restored).
************************************************** ******
But thats just it, I may not want to take this action. Maybe some data got
deleted from a set of tables in a filegroup right after I took my last
filegroup backup, so I just want to restore that filegroup back to that
backup, but leave my other filegroups alone. Restoring the log brings the
data back to a consistant state when the log was backed up, which may not be
what I want.
Can this be accomplished?
TIA, ChrisRThe short answer is that SQL Server will not assist in providing you with an inconsistent database.
A database would be inconsistent if different parts of it are from different points in time. And
this is indeed what you want to achieve.
Your option is to restore this filegroup backup to a new database (using the PARTIAL option of the
restore command - see BOL for details), and then copy over the relevant data to your production
database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:5774CAB8-3FAC-4DF6-BCF8-1FA12E73559E@.microsoft.com...
> Howdy all. I want to be able to restore just 1 filegroup from a filegroup
> backup, without having to restore the log afterwords even if the data has
> changed post filegroup backup. While this would leave the data inconsistant,
> it may be desired at times. From BOL:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c073049100f0.htm
> ************************************************** ******
> If the files have been modified after the file backup was created, execute
> the RESTORE LOG statement to apply the transaction log backup, specifying:
> The name of the database to which the transaction log will be applied.
> The backup device from where the transaction log backup will be restored.
> The NORECOVERY clause if you have another transaction log backup to apply
> after the current one; otherwise, specify the RECOVERY clause.
> The transaction log backups, if applied, must cover the time when the files
> and filegroups were backed up until the end of log (unless ALL database files
> are restored).
> ************************************************** ******
> But thats just it, I may not want to take this action. Maybe some data got
> deleted from a set of tables in a filegroup right after I took my last
> filegroup backup, so I just want to restore that filegroup back to that
> backup, but leave my other filegroups alone. Restoring the log brings the
> data back to a consistant state when the log was backed up, which may not be
> what I want.
> Can this be accomplished?
> TIA, ChrisR|||That's what I thought.
Thanks Tibor.
"Tibor Karaszi" wrote:
> The short answer is that SQL Server will not assist in providing you with an inconsistent database.
> A database would be inconsistent if different parts of it are from different points in time. And
> this is indeed what you want to achieve.
> Your option is to restore this filegroup backup to a new database (using the PARTIAL option of the
> restore command - see BOL for details), and then copy over the relevant data to your production
> database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:5774CAB8-3FAC-4DF6-BCF8-1FA12E73559E@.microsoft.com...
> > Howdy all. I want to be able to restore just 1 filegroup from a filegroup
> > backup, without having to restore the log afterwords even if the data has
> > changed post filegroup backup. While this would leave the data inconsistant,
> > it may be desired at times. From BOL:
> > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c073049100f0.htm
> >
> > ************************************************** ******
> > If the files have been modified after the file backup was created, execute
> > the RESTORE LOG statement to apply the transaction log backup, specifying:
> >
> > The name of the database to which the transaction log will be applied.
> > The backup device from where the transaction log backup will be restored.
> > The NORECOVERY clause if you have another transaction log backup to apply
> > after the current one; otherwise, specify the RECOVERY clause.
> > The transaction log backups, if applied, must cover the time when the files
> > and filegroups were backed up until the end of log (unless ALL database files
> > are restored).
> > ************************************************** ******
> >
> > But thats just it, I may not want to take this action. Maybe some data got
> > deleted from a set of tables in a filegroup right after I took my last
> > filegroup backup, so I just want to restore that filegroup back to that
> > backup, but leave my other filegroups alone. Restoring the log brings the
> > data back to a consistant state when the log was backed up, which may not be
> > what I want.
> >
> > Can this be accomplished?
> >
> > TIA, ChrisR
>
>
Restoring filegroups in 2005.
Howdy all. I want to be able to restore just 1 filegroup from a filegroup
backup, without having to restore the log afterwords even if the data has
changed post filegroup backup. While this would leave the data inconsistant,
it may be desired at times. From BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c073
049100f0.htm
****************************************
********** ******
If the files have been modified after the file backup was created, execute
the RESTORE LOG statement to apply the transaction log backup, specifying:
The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be restored.
The NORECOVERY clause if you have another transaction log backup to apply
after the current one; otherwise, specify the RECOVERY clause.
The transaction log backups, if applied, must cover the time when the files
and filegroups were backed up until the end of log (unless ALL database file
s
are restored).
****************************************
********** ******
But thats just it, I may not want to take this action. Maybe some data got
deleted from a set of tables in a filegroup right after I took my last
filegroup backup, so I just want to restore that filegroup back to that
backup, but leave my other filegroups alone. Restoring the log brings the
data back to a consistant state when the log was backed up, which may not be
what I want.
Can this be accomplished?
TIA, ChrisRThe short answer is that SQL Server will not assist in providing you with an
inconsistent database.
A database would be inconsistent if different parts of it are from different
points in time. And
this is indeed what you want to achieve.
Your option is to restore this filegroup backup to a new database (using the
PARTIAL option of the
restore command - see BOL for details), and then copy over the relevant data
to your production
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:5774CAB8-3FAC-4DF6-BCF8-1FA12E73559E@.microsoft.com...
> Howdy all. I want to be able to restore just 1 filegroup from a filegroup
> backup, without having to restore the log afterwords even if the data has
> changed post filegroup backup. While this would leave the data inconsistan
t,
> it may be desired at times. From BOL:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c0
73049100f0.htm
> ****************************************
********** ******
> If the files have been modified after the file backup was created, execute
> the RESTORE LOG statement to apply the transaction log backup, specifying:
> The name of the database to which the transaction log will be applied.
> The backup device from where the transaction log backup will be restored.
> The NORECOVERY clause if you have another transaction log backup to apply
> after the current one; otherwise, specify the RECOVERY clause.
> The transaction log backups, if applied, must cover the time when the file
s
> and filegroups were backed up until the end of log (unless ALL database fi
les
> are restored).
> ****************************************
********** ******
> But thats just it, I may not want to take this action. Maybe some data got
> deleted from a set of tables in a filegroup right after I took my last
> filegroup backup, so I just want to restore that filegroup back to that
> backup, but leave my other filegroups alone. Restoring the log brings the
> data back to a consistant state when the log was backed up, which may not
be
> what I want.
> Can this be accomplished?
> TIA, ChrisR|||That's what I thought.
Thanks Tibor.
"Tibor Karaszi" wrote:
> The short answer is that SQL Server will not assist in providing you with
an inconsistent database.
> A database would be inconsistent if different parts of it are from differe
nt points in time. And
> this is indeed what you want to achieve.
> Your option is to restore this filegroup backup to a new database (using t
he PARTIAL option of the
> restore command - see BOL for details), and then copy over the relevant da
ta to your production
> database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:5774CAB8-3FAC-4DF6-BCF8-1FA12E73559E@.microsoft.com...
>
>
backup, without having to restore the log afterwords even if the data has
changed post filegroup backup. While this would leave the data inconsistant,
it may be desired at times. From BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c073
049100f0.htm
****************************************
********** ******
If the files have been modified after the file backup was created, execute
the RESTORE LOG statement to apply the transaction log backup, specifying:
The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be restored.
The NORECOVERY clause if you have another transaction log backup to apply
after the current one; otherwise, specify the RECOVERY clause.
The transaction log backups, if applied, must cover the time when the files
and filegroups were backed up until the end of log (unless ALL database file
s
are restored).
****************************************
********** ******
But thats just it, I may not want to take this action. Maybe some data got
deleted from a set of tables in a filegroup right after I took my last
filegroup backup, so I just want to restore that filegroup back to that
backup, but leave my other filegroups alone. Restoring the log brings the
data back to a consistant state when the log was backed up, which may not be
what I want.
Can this be accomplished?
TIA, ChrisRThe short answer is that SQL Server will not assist in providing you with an
inconsistent database.
A database would be inconsistent if different parts of it are from different
points in time. And
this is indeed what you want to achieve.
Your option is to restore this filegroup backup to a new database (using the
PARTIAL option of the
restore command - see BOL for details), and then copy over the relevant data
to your production
database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:5774CAB8-3FAC-4DF6-BCF8-1FA12E73559E@.microsoft.com...
> Howdy all. I want to be able to restore just 1 filegroup from a filegroup
> backup, without having to restore the log afterwords even if the data has
> changed post filegroup backup. While this would leave the data inconsistan
t,
> it may be desired at times. From BOL:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f552700-efb3-48f1-b96d-c0
73049100f0.htm
> ****************************************
********** ******
> If the files have been modified after the file backup was created, execute
> the RESTORE LOG statement to apply the transaction log backup, specifying:
> The name of the database to which the transaction log will be applied.
> The backup device from where the transaction log backup will be restored.
> The NORECOVERY clause if you have another transaction log backup to apply
> after the current one; otherwise, specify the RECOVERY clause.
> The transaction log backups, if applied, must cover the time when the file
s
> and filegroups were backed up until the end of log (unless ALL database fi
les
> are restored).
> ****************************************
********** ******
> But thats just it, I may not want to take this action. Maybe some data got
> deleted from a set of tables in a filegroup right after I took my last
> filegroup backup, so I just want to restore that filegroup back to that
> backup, but leave my other filegroups alone. Restoring the log brings the
> data back to a consistant state when the log was backed up, which may not
be
> what I want.
> Can this be accomplished?
> TIA, ChrisR|||That's what I thought.
Thanks Tibor.
"Tibor Karaszi" wrote:
> The short answer is that SQL Server will not assist in providing you with
an inconsistent database.
> A database would be inconsistent if different parts of it are from differe
nt points in time. And
> this is indeed what you want to achieve.
> Your option is to restore this filegroup backup to a new database (using t
he PARTIAL option of the
> restore command - see BOL for details), and then copy over the relevant da
ta to your production
> database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:5774CAB8-3FAC-4DF6-BCF8-1FA12E73559E@.microsoft.com...
>
>
restoring filegroup from different date than mdf file
Can anyone tell me what the effect would be of restoring a filegroup from an
older date than the other filegroups in the database.
The tables in the older filegroup contain fewer records than the current
one. My main concern is that the sysindexes table will still contain
reference to a greater number of records.
You would not be able to access the database until you have restore all subsequent transaction log
backups for the database until current point in time. This is better explained in Books Online...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:475C96EA-967D-4200-95ED-F1038F4488E0@.microsoft.com...
> Can anyone tell me what the effect would be of restoring a filegroup from an
> older date than the other filegroups in the database.
> The tables in the older filegroup contain fewer records than the current
> one. My main concern is that the sysindexes table will still contain
> reference to a greater number of records.
older date than the other filegroups in the database.
The tables in the older filegroup contain fewer records than the current
one. My main concern is that the sysindexes table will still contain
reference to a greater number of records.
You would not be able to access the database until you have restore all subsequent transaction log
backups for the database until current point in time. This is better explained in Books Online...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:475C96EA-967D-4200-95ED-F1038F4488E0@.microsoft.com...
> Can anyone tell me what the effect would be of restoring a filegroup from an
> older date than the other filegroups in the database.
> The tables in the older filegroup contain fewer records than the current
> one. My main concern is that the sysindexes table will still contain
> reference to a greater number of records.
restoring filegroup from different date than mdf file
Can anyone tell me what the effect would be of restoring a filegroup from an
older date than the other filegroups in the database.
The tables in the older filegroup contain fewer records than the current
one. My main concern is that the sysindexes table will still contain
reference to a greater number of records.You would not be able to access the database until you have restore all subsequent transaction log
backups for the database until current point in time. This is better explained in Books Online...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:475C96EA-967D-4200-95ED-F1038F4488E0@.microsoft.com...
> Can anyone tell me what the effect would be of restoring a filegroup from an
> older date than the other filegroups in the database.
> The tables in the older filegroup contain fewer records than the current
> one. My main concern is that the sysindexes table will still contain
> reference to a greater number of records.
older date than the other filegroups in the database.
The tables in the older filegroup contain fewer records than the current
one. My main concern is that the sysindexes table will still contain
reference to a greater number of records.You would not be able to access the database until you have restore all subsequent transaction log
backups for the database until current point in time. This is better explained in Books Online...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:475C96EA-967D-4200-95ED-F1038F4488E0@.microsoft.com...
> Can anyone tell me what the effect would be of restoring a filegroup from an
> older date than the other filegroups in the database.
> The tables in the older filegroup contain fewer records than the current
> one. My main concern is that the sysindexes table will still contain
> reference to a greater number of records.
Tuesday, February 21, 2012
Restoring Database or Files/Filegroups
Hello:
In SQL 2005 Management Studio, what is the difference between restoring
"Database" and "Files or Filegroups"? When you right-click on a database and
you choose Tasks...Restore, you have a choice between "Database" and "Files
or Filegroups". What's the difference?
Choosing "Database" seems to work better, if I am restoring a backup of one
database into another database. But, I think if I'm restoring a backup of a
database into the same database, I usually choose "Files or Filegroups".
childofthe1980s
My understanding you want to use FileGroup/File backup if you need to do
separate backups on the files in the database.
Lets say you have three file groups:
FileGroupA - Primary HEAVY USAGE
FileGroupB - Read Only
FileGroupC - System Objects
So you can create a backup plan that backups FileGroupA on regular basis and
do a FULL backup once a week so as to speed up backup processes.
But if you have only one file group in databases (most ppl do), I don't
think you notice a difference.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"childofthe1980s" wrote:
> Hello:
> In SQL 2005 Management Studio, what is the difference between restoring
> "Database" and "Files or Filegroups"? When you right-click on a database and
> you choose Tasks...Restore, you have a choice between "Database" and "Files
> or Filegroups". What's the difference?
> Choosing "Database" seems to work better, if I am restoring a backup of one
> database into another database. But, I think if I'm restoring a backup of a
> database into the same database, I usually choose "Files or Filegroups".
> childofthe1980s
|||For more info:
http://msdn2.microsoft.com/en-us/library/ms186858.aspx
Ekrem ?nsoy
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:68C22630-4D03-4B26-BE14-943DC38367F5@.microsoft.com...
> Hello:
> In SQL 2005 Management Studio, what is the difference between restoring
> "Database" and "Files or Filegroups"? When you right-click on a database
> and
> you choose Tasks...Restore, you have a choice between "Database" and
> "Files
> or Filegroups". What's the difference?
> Choosing "Database" seems to work better, if I am restoring a backup of
> one
> database into another database. But, I think if I'm restoring a backup of
> a
> database into the same database, I usually choose "Files or Filegroups".
> childofthe1980s
In SQL 2005 Management Studio, what is the difference between restoring
"Database" and "Files or Filegroups"? When you right-click on a database and
you choose Tasks...Restore, you have a choice between "Database" and "Files
or Filegroups". What's the difference?
Choosing "Database" seems to work better, if I am restoring a backup of one
database into another database. But, I think if I'm restoring a backup of a
database into the same database, I usually choose "Files or Filegroups".
childofthe1980s
My understanding you want to use FileGroup/File backup if you need to do
separate backups on the files in the database.
Lets say you have three file groups:
FileGroupA - Primary HEAVY USAGE
FileGroupB - Read Only
FileGroupC - System Objects
So you can create a backup plan that backups FileGroupA on regular basis and
do a FULL backup once a week so as to speed up backup processes.
But if you have only one file group in databases (most ppl do), I don't
think you notice a difference.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"childofthe1980s" wrote:
> Hello:
> In SQL 2005 Management Studio, what is the difference between restoring
> "Database" and "Files or Filegroups"? When you right-click on a database and
> you choose Tasks...Restore, you have a choice between "Database" and "Files
> or Filegroups". What's the difference?
> Choosing "Database" seems to work better, if I am restoring a backup of one
> database into another database. But, I think if I'm restoring a backup of a
> database into the same database, I usually choose "Files or Filegroups".
> childofthe1980s
|||For more info:
http://msdn2.microsoft.com/en-us/library/ms186858.aspx
Ekrem ?nsoy
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:68C22630-4D03-4B26-BE14-943DC38367F5@.microsoft.com...
> Hello:
> In SQL 2005 Management Studio, what is the difference between restoring
> "Database" and "Files or Filegroups"? When you right-click on a database
> and
> you choose Tasks...Restore, you have a choice between "Database" and
> "Files
> or Filegroups". What's the difference?
> Choosing "Database" seems to work better, if I am restoring a backup of
> one
> database into another database. But, I think if I'm restoring a backup of
> a
> database into the same database, I usually choose "Files or Filegroups".
> childofthe1980s
Restoring Database or Files/Filegroups
Hello:
In SQL 2005 Management Studio, what is the difference between restoring
"Database" and "Files or Filegroups"? When you right-click on a database and
you choose Tasks...Restore, you have a choice between "Database" and "Files
or Filegroups". What's the difference?
Choosing "Database" seems to work better, if I am restoring a backup of one
database into another database. But, I think if I'm restoring a backup of a
database into the same database, I usually choose "Files or Filegroups".
childofthe1980sMy understanding you want to use FileGroup/File backup if you need to do
separate backups on the files in the database.
Lets say you have three file groups:
FileGroupA - Primary HEAVY USAGE
FileGroupB - Read Only
FileGroupC - System Objects
So you can create a backup plan that backups FileGroupA on regular basis and
do a FULL backup once a week so as to speed up backup processes.
But if you have only one file group in databases (most ppl do), I don't
think you notice a difference.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"childofthe1980s" wrote:
> Hello:
> In SQL 2005 Management Studio, what is the difference between restoring
> "Database" and "Files or Filegroups"? When you right-click on a database and
> you choose Tasks...Restore, you have a choice between "Database" and "Files
> or Filegroups". What's the difference?
> Choosing "Database" seems to work better, if I am restoring a backup of one
> database into another database. But, I think if I'm restoring a backup of a
> database into the same database, I usually choose "Files or Filegroups".
> childofthe1980s|||For more info:
http://msdn2.microsoft.com/en-us/library/ms186858.aspx
--
Ekrem Ã?nsoy
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:68C22630-4D03-4B26-BE14-943DC38367F5@.microsoft.com...
> Hello:
> In SQL 2005 Management Studio, what is the difference between restoring
> "Database" and "Files or Filegroups"? When you right-click on a database
> and
> you choose Tasks...Restore, you have a choice between "Database" and
> "Files
> or Filegroups". What's the difference?
> Choosing "Database" seems to work better, if I am restoring a backup of
> one
> database into another database. But, I think if I'm restoring a backup of
> a
> database into the same database, I usually choose "Files or Filegroups".
> childofthe1980s
In SQL 2005 Management Studio, what is the difference between restoring
"Database" and "Files or Filegroups"? When you right-click on a database and
you choose Tasks...Restore, you have a choice between "Database" and "Files
or Filegroups". What's the difference?
Choosing "Database" seems to work better, if I am restoring a backup of one
database into another database. But, I think if I'm restoring a backup of a
database into the same database, I usually choose "Files or Filegroups".
childofthe1980sMy understanding you want to use FileGroup/File backup if you need to do
separate backups on the files in the database.
Lets say you have three file groups:
FileGroupA - Primary HEAVY USAGE
FileGroupB - Read Only
FileGroupC - System Objects
So you can create a backup plan that backups FileGroupA on regular basis and
do a FULL backup once a week so as to speed up backup processes.
But if you have only one file group in databases (most ppl do), I don't
think you notice a difference.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"childofthe1980s" wrote:
> Hello:
> In SQL 2005 Management Studio, what is the difference between restoring
> "Database" and "Files or Filegroups"? When you right-click on a database and
> you choose Tasks...Restore, you have a choice between "Database" and "Files
> or Filegroups". What's the difference?
> Choosing "Database" seems to work better, if I am restoring a backup of one
> database into another database. But, I think if I'm restoring a backup of a
> database into the same database, I usually choose "Files or Filegroups".
> childofthe1980s|||For more info:
http://msdn2.microsoft.com/en-us/library/ms186858.aspx
--
Ekrem Ã?nsoy
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:68C22630-4D03-4B26-BE14-943DC38367F5@.microsoft.com...
> Hello:
> In SQL 2005 Management Studio, what is the difference between restoring
> "Database" and "Files or Filegroups"? When you right-click on a database
> and
> you choose Tasks...Restore, you have a choice between "Database" and
> "Files
> or Filegroups". What's the difference?
> Choosing "Database" seems to work better, if I am restoring a backup of
> one
> database into another database. But, I think if I'm restoring a backup of
> a
> database into the same database, I usually choose "Files or Filegroups".
> childofthe1980s
Restoring Database or Files/Filegroups
Hello:
In SQL 2005 Management Studio, what is the difference between restoring
"Database" and "Files or Filegroups"? When you right-click on a database an
d
you choose Tasks...Restore, you have a choice between "Database" and "Files
or Filegroups". What's the difference?
Choosing "Database" seems to work better, if I am restoring a backup of one
database into another database. But, I think if I'm restoring a backup of a
database into the same database, I usually choose "Files or Filegroups".
childofthe1980sMy understanding you want to use FileGroup/File backup if you need to do
separate backups on the files in the database.
Lets say you have three file groups:
FileGroupA - Primary HEAVY USAGE
FileGroupB - Read Only
FileGroupC - System Objects
So you can create a backup plan that backups FileGroupA on regular basis and
do a FULL backup once a week so as to speed up backup processes.
But if you have only one file group in databases (most ppl do), I don't
think you notice a difference.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"childofthe1980s" wrote:
> Hello:
> In SQL 2005 Management Studio, what is the difference between restoring
> "Database" and "Files or Filegroups"? When you right-click on a database
and
> you choose Tasks...Restore, you have a choice between "Database" and "File
s
> or Filegroups". What's the difference?
> Choosing "Database" seems to work better, if I am restoring a backup of on
e
> database into another database. But, I think if I'm restoring a backup of
a
> database into the same database, I usually choose "Files or Filegroups".
> childofthe1980s|||For more info:
http://msdn2.microsoft.com/en-us/library/ms186858.aspx
Ekrem ?nsoy
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:68C22630-4D03-4B26-BE14-943DC38367F5@.microsoft.com...
> Hello:
> In SQL 2005 Management Studio, what is the difference between restoring
> "Database" and "Files or Filegroups"? When you right-click on a database
> and
> you choose Tasks...Restore, you have a choice between "Database" and
> "Files
> or Filegroups". What's the difference?
> Choosing "Database" seems to work better, if I am restoring a backup of
> one
> database into another database. But, I think if I'm restoring a backup of
> a
> database into the same database, I usually choose "Files or Filegroups".
> childofthe1980s
In SQL 2005 Management Studio, what is the difference between restoring
"Database" and "Files or Filegroups"? When you right-click on a database an
d
you choose Tasks...Restore, you have a choice between "Database" and "Files
or Filegroups". What's the difference?
Choosing "Database" seems to work better, if I am restoring a backup of one
database into another database. But, I think if I'm restoring a backup of a
database into the same database, I usually choose "Files or Filegroups".
childofthe1980sMy understanding you want to use FileGroup/File backup if you need to do
separate backups on the files in the database.
Lets say you have three file groups:
FileGroupA - Primary HEAVY USAGE
FileGroupB - Read Only
FileGroupC - System Objects
So you can create a backup plan that backups FileGroupA on regular basis and
do a FULL backup once a week so as to speed up backup processes.
But if you have only one file group in databases (most ppl do), I don't
think you notice a difference.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"childofthe1980s" wrote:
> Hello:
> In SQL 2005 Management Studio, what is the difference between restoring
> "Database" and "Files or Filegroups"? When you right-click on a database
and
> you choose Tasks...Restore, you have a choice between "Database" and "File
s
> or Filegroups". What's the difference?
> Choosing "Database" seems to work better, if I am restoring a backup of on
e
> database into another database. But, I think if I'm restoring a backup of
a
> database into the same database, I usually choose "Files or Filegroups".
> childofthe1980s|||For more info:
http://msdn2.microsoft.com/en-us/library/ms186858.aspx
Ekrem ?nsoy
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:68C22630-4D03-4B26-BE14-943DC38367F5@.microsoft.com...
> Hello:
> In SQL 2005 Management Studio, what is the difference between restoring
> "Database" and "Files or Filegroups"? When you right-click on a database
> and
> you choose Tasks...Restore, you have a choice between "Database" and
> "Files
> or Filegroups". What's the difference?
> Choosing "Database" seems to work better, if I am restoring a backup of
> one
> database into another database. But, I think if I'm restoring a backup of
> a
> database into the same database, I usually choose "Files or Filegroups".
> childofthe1980s
Subscribe to:
Posts (Atom)