Okay, for some reason I'm mentally having troubles getting around this
(I blame Monday!). So can you please tell me if my line of thinking
is in the right direction.
I do daily backups on databases on filegroups, not just the entire
database, since some of my database filegroups are static (change
twice a year) and some are dynamic (change pretty much hourly). Also,
I may or may not have an hourly differential file based on how often
the data changes. I would like to create a routine to restore an
entire database (OldDatabase) with a new name (NewDatabase).
Here is the pseudocode of what I want to have happen (all caps is SQL
code that will append a varchar):
For each filegroup:
BEGIN filegroup loop
RESTORE DATABASE NewDatabase FROM 'OldDatabase File'
WITH NORECOVERY,
MOVE 'Old File Name' TO 'New File Name'
(If a log files is backed up:) MOVE 'Old Log File Name' TO 'New Log
File Name'
(If a differential file exists:)
RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential File'
WITH NORECOVERY
(If a differential file exists with a log file:)
RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential Log
File'
WITH NORECOVERY
END fielgroup loop
After looping through each filegroup I then append the following to
wrap it all up:
RESTORE DATABASE NewDatabase WITH RECOVERY
Would that accomplish what I'm trying to pull off?
Thanks!!!
-UtahHi
I'm not sure understood you. Why do you run through filegroups?You should
het a new db with the technique you are using without looping
How many log files fo yo apply ?
1) Restore FULL db
2) Restore last DIFF backup
3) Apply all LOG files backuperd since last DIFF backup
BOL has pretty good examples how to do that
<Utahduck@.hotmail.com> wrote in message
news:1176153244.161025.189040@.q75g2000hsh.googlegroups.com...
> Okay, for some reason I'm mentally having troubles getting around this
> (I blame Monday!). So can you please tell me if my line of thinking
> is in the right direction.
> I do daily backups on databases on filegroups, not just the entire
> database, since some of my database filegroups are static (change
> twice a year) and some are dynamic (change pretty much hourly). Also,
> I may or may not have an hourly differential file based on how often
> the data changes. I would like to create a routine to restore an
> entire database (OldDatabase) with a new name (NewDatabase).
> Here is the pseudocode of what I want to have happen (all caps is SQL
> code that will append a varchar):
> For each filegroup:
> BEGIN filegroup loop
> RESTORE DATABASE NewDatabase FROM 'OldDatabase File'
> WITH NORECOVERY,
> MOVE 'Old File Name' TO 'New File Name'
> (If a log files is backed up:) MOVE 'Old Log File Name' TO 'New Log
> File Name'
> (If a differential file exists:)
> RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential File'
> WITH NORECOVERY
> (If a differential file exists with a log file:)
> RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential Log
> File'
> WITH NORECOVERY
> END fielgroup loop
> After looping through each filegroup I then append the following to
> wrap it all up:
> RESTORE DATABASE NewDatabase WITH RECOVERY
> Would that accomplish what I'm trying to pull off?
> Thanks!!!
> -Utah
>
Showing posts with label restores. Show all posts
Showing posts with label restores. Show all posts
Monday, March 19, 2012
Filegroup Restores
Okay, for some reason I'm mentally having troubles getting around this
(I blame Monday!). So can you please tell me if my line of thinking
is in the right direction.
I do daily backups on databases on filegroups, not just the entire
database, since some of my database filegroups are static (change
twice a year) and some are dynamic (change pretty much hourly). Also,
I may or may not have an hourly differential file based on how often
the data changes. I would like to create a routine to restore an
entire database (OldDatabase) with a new name (NewDatabase).
Here is the pseudocode of what I want to have happen (all caps is SQL
code that will append a varchar):
For each filegroup:
BEGIN filegroup loop
RESTORE DATABASE NewDatabase FROM 'OldDatabase File'
WITH NORECOVERY,
MOVE 'Old File Name' TO 'New File Name'
(If a log files is backed up
MOVE 'Old Log File Name' TO 'New Log
File Name'
(If a differential file exists
RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential File'
WITH NORECOVERY
(If a differential file exists with a log file
RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential Log
File'
WITH NORECOVERY
END fielgroup loop
After looping through each filegroup I then append the following to
wrap it all up:
RESTORE DATABASE NewDatabase WITH RECOVERY
Would that accomplish what I'm trying to pull off?
Thanks!!!
-UtahHi
I'm not sure understood you. Why do you run through filegroups?You should
het a new db with the technique you are using without looping
How many log files fo yo apply ?
1) Restore FULL db
2) Restore last DIFF backup
3) Apply all LOG files backuperd since last DIFF backup
BOL has pretty good examples how to do that
<Utahduck@.hotmail.com> wrote in message
news:1176153244.161025.189040@.q75g2000hsh.googlegroups.com...
> Okay, for some reason I'm mentally having troubles getting around this
> (I blame Monday!). So can you please tell me if my line of thinking
> is in the right direction.
> I do daily backups on databases on filegroups, not just the entire
> database, since some of my database filegroups are static (change
> twice a year) and some are dynamic (change pretty much hourly). Also,
> I may or may not have an hourly differential file based on how often
> the data changes. I would like to create a routine to restore an
> entire database (OldDatabase) with a new name (NewDatabase).
> Here is the pseudocode of what I want to have happen (all caps is SQL
> code that will append a varchar):
> For each filegroup:
> BEGIN filegroup loop
> RESTORE DATABASE NewDatabase FROM 'OldDatabase File'
> WITH NORECOVERY,
> MOVE 'Old File Name' TO 'New File Name'
> (If a log files is backed up MOVE 'Old Log File Name' TO 'New Log
> File Name'
> (If a differential file exists
> RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential File'
> WITH NORECOVERY
> (If a differential file exists with a log file
> RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential Log
> File'
> WITH NORECOVERY
> END fielgroup loop
> After looping through each filegroup I then append the following to
> wrap it all up:
> RESTORE DATABASE NewDatabase WITH RECOVERY
> Would that accomplish what I'm trying to pull off?
> Thanks!!!
> -Utah
>
(I blame Monday!). So can you please tell me if my line of thinking
is in the right direction.
I do daily backups on databases on filegroups, not just the entire
database, since some of my database filegroups are static (change
twice a year) and some are dynamic (change pretty much hourly). Also,
I may or may not have an hourly differential file based on how often
the data changes. I would like to create a routine to restore an
entire database (OldDatabase) with a new name (NewDatabase).
Here is the pseudocode of what I want to have happen (all caps is SQL
code that will append a varchar):
For each filegroup:
BEGIN filegroup loop
RESTORE DATABASE NewDatabase FROM 'OldDatabase File'
WITH NORECOVERY,
MOVE 'Old File Name' TO 'New File Name'
(If a log files is backed up
MOVE 'Old Log File Name' TO 'New LogFile Name'
(If a differential file exists
RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential File'
WITH NORECOVERY
(If a differential file exists with a log file
RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential Log
File'
WITH NORECOVERY
END fielgroup loop
After looping through each filegroup I then append the following to
wrap it all up:
RESTORE DATABASE NewDatabase WITH RECOVERY
Would that accomplish what I'm trying to pull off?
Thanks!!!
-UtahHi
I'm not sure understood you. Why do you run through filegroups?You should
het a new db with the technique you are using without looping
How many log files fo yo apply ?
1) Restore FULL db
2) Restore last DIFF backup
3) Apply all LOG files backuperd since last DIFF backup
BOL has pretty good examples how to do that
<Utahduck@.hotmail.com> wrote in message
news:1176153244.161025.189040@.q75g2000hsh.googlegroups.com...
> Okay, for some reason I'm mentally having troubles getting around this
> (I blame Monday!). So can you please tell me if my line of thinking
> is in the right direction.
> I do daily backups on databases on filegroups, not just the entire
> database, since some of my database filegroups are static (change
> twice a year) and some are dynamic (change pretty much hourly). Also,
> I may or may not have an hourly differential file based on how often
> the data changes. I would like to create a routine to restore an
> entire database (OldDatabase) with a new name (NewDatabase).
> Here is the pseudocode of what I want to have happen (all caps is SQL
> code that will append a varchar):
> For each filegroup:
> BEGIN filegroup loop
> RESTORE DATABASE NewDatabase FROM 'OldDatabase File'
> WITH NORECOVERY,
> MOVE 'Old File Name' TO 'New File Name'
> (If a log files is backed up
MOVE 'Old Log File Name' TO 'New Log> File Name'
> (If a differential file exists
> RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential File'
> WITH NORECOVERY
> (If a differential file exists with a log file
> RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential Log
> File'
> WITH NORECOVERY
> END fielgroup loop
> After looping through each filegroup I then append the following to
> wrap it all up:
> RESTORE DATABASE NewDatabase WITH RECOVERY
> Would that accomplish what I'm trying to pull off?
> Thanks!!!
> -Utah
>
Filegroup Restores
Okay, for some reason I'm mentally having troubles getting around this
(I blame Monday!). So can you please tell me if my line of thinking
is in the right direction.
I do daily backups on databases on filegroups, not just the entire
database, since some of my database filegroups are static (change
twice a year) and some are dynamic (change pretty much hourly). Also,
I may or may not have an hourly differential file based on how often
the data changes. I would like to create a routine to restore an
entire database (OldDatabase) with a new name (NewDatabase).
Here is the pseudocode of what I want to have happen (all caps is SQL
code that will append a varchar):
For each filegroup:
BEGIN filegroup loop
RESTORE DATABASE NewDatabase FROM 'OldDatabase File'
WITH NORECOVERY,
MOVE 'Old File Name' TO 'New File Name'
(If a log files is backed up MOVE 'Old Log File Name' TO 'New Log
File Name'
(If a differential file exists
RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential File'
WITH NORECOVERY
(If a differential file exists with a log file
RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential Log
File'
WITH NORECOVERY
END fielgroup loop
After looping through each filegroup I then append the following to
wrap it all up:
RESTORE DATABASE NewDatabase WITH RECOVERY
Would that accomplish what I'm trying to pull off?
Thanks!!!
-Utah
Hi
I'm not sure understood you. Why do you run through filegroups?You should
het a new db with the technique you are using without looping
How many log files fo yo apply ?
1) Restore FULL db
2) Restore last DIFF backup
3) Apply all LOG files backuperd since last DIFF backup
BOL has pretty good examples how to do that
<Utahduck@.hotmail.com> wrote in message
news:1176153244.161025.189040@.q75g2000hsh.googlegr oups.com...
> Okay, for some reason I'm mentally having troubles getting around this
> (I blame Monday!). So can you please tell me if my line of thinking
> is in the right direction.
> I do daily backups on databases on filegroups, not just the entire
> database, since some of my database filegroups are static (change
> twice a year) and some are dynamic (change pretty much hourly). Also,
> I may or may not have an hourly differential file based on how often
> the data changes. I would like to create a routine to restore an
> entire database (OldDatabase) with a new name (NewDatabase).
> Here is the pseudocode of what I want to have happen (all caps is SQL
> code that will append a varchar):
> For each filegroup:
> BEGIN filegroup loop
> RESTORE DATABASE NewDatabase FROM 'OldDatabase File'
> WITH NORECOVERY,
> MOVE 'Old File Name' TO 'New File Name'
> (If a log files is backed up MOVE 'Old Log File Name' TO 'New Log
> File Name'
> (If a differential file exists
> RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential File'
> WITH NORECOVERY
> (If a differential file exists with a log file
> RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential Log
> File'
> WITH NORECOVERY
> END fielgroup loop
> After looping through each filegroup I then append the following to
> wrap it all up:
> RESTORE DATABASE NewDatabase WITH RECOVERY
> Would that accomplish what I'm trying to pull off?
> Thanks!!!
> -Utah
>
(I blame Monday!). So can you please tell me if my line of thinking
is in the right direction.
I do daily backups on databases on filegroups, not just the entire
database, since some of my database filegroups are static (change
twice a year) and some are dynamic (change pretty much hourly). Also,
I may or may not have an hourly differential file based on how often
the data changes. I would like to create a routine to restore an
entire database (OldDatabase) with a new name (NewDatabase).
Here is the pseudocode of what I want to have happen (all caps is SQL
code that will append a varchar):
For each filegroup:
BEGIN filegroup loop
RESTORE DATABASE NewDatabase FROM 'OldDatabase File'
WITH NORECOVERY,
MOVE 'Old File Name' TO 'New File Name'
(If a log files is backed up
MOVE 'Old Log File Name' TO 'New LogFile Name'
(If a differential file exists
RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential File'
WITH NORECOVERY
(If a differential file exists with a log file
RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential Log
File'
WITH NORECOVERY
END fielgroup loop
After looping through each filegroup I then append the following to
wrap it all up:
RESTORE DATABASE NewDatabase WITH RECOVERY
Would that accomplish what I'm trying to pull off?
Thanks!!!
-Utah
Hi
I'm not sure understood you. Why do you run through filegroups?You should
het a new db with the technique you are using without looping
How many log files fo yo apply ?
1) Restore FULL db
2) Restore last DIFF backup
3) Apply all LOG files backuperd since last DIFF backup
BOL has pretty good examples how to do that
<Utahduck@.hotmail.com> wrote in message
news:1176153244.161025.189040@.q75g2000hsh.googlegr oups.com...
> Okay, for some reason I'm mentally having troubles getting around this
> (I blame Monday!). So can you please tell me if my line of thinking
> is in the right direction.
> I do daily backups on databases on filegroups, not just the entire
> database, since some of my database filegroups are static (change
> twice a year) and some are dynamic (change pretty much hourly). Also,
> I may or may not have an hourly differential file based on how often
> the data changes. I would like to create a routine to restore an
> entire database (OldDatabase) with a new name (NewDatabase).
> Here is the pseudocode of what I want to have happen (all caps is SQL
> code that will append a varchar):
> For each filegroup:
> BEGIN filegroup loop
> RESTORE DATABASE NewDatabase FROM 'OldDatabase File'
> WITH NORECOVERY,
> MOVE 'Old File Name' TO 'New File Name'
> (If a log files is backed up
MOVE 'Old Log File Name' TO 'New Log> File Name'
> (If a differential file exists
> RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential File'
> WITH NORECOVERY
> (If a differential file exists with a log file
> RESTORE DATABASE NewDatabase FROM 'OldDatabase Differential Log
> File'
> WITH NORECOVERY
> END fielgroup loop
> After looping through each filegroup I then append the following to
> wrap it all up:
> RESTORE DATABASE NewDatabase WITH RECOVERY
> Would that accomplish what I'm trying to pull off?
> Thanks!!!
> -Utah
>
Sunday, February 19, 2012
File Group backups and restores
We have a situation where a large DB(150GB) has mostly static data in it. We were considering a filegroup solution that locates the static data in one(or more) files and the dynamic content is stored in another but it seems that any file/filegroup restore requires a transaction log restore to complete the database. We don't have any transactional requirements, in fact they weren't even dumping them till I arrived and restoring them could undo changes to the data. The app is a warehouse where any lost data would be reloaded. The other alternative is separate databases. Anyone have any ideas or suggestions?So what exactly is the question<g>. A lot of people don't bother backing
up DW's if they can easily redo the data from scratch.
--
Andrew J. Kelly
SQL Server MVP
"jeff ericson" <anonymous@.discussions.microsoft.com> wrote in message
news:8C5F4B6A-F8A3-404A-BD26-F9245E061D80@.microsoft.com...
> We have a situation where a large DB(150GB) has mostly static data in it.
We were considering a filegroup solution that locates the static data in
one(or more) files and the dynamic content is stored in another but it seems
that any file/filegroup restore requires a transaction log restore to
complete the database. We don't have any transactional requirements, in
fact they weren't even dumping them till I arrived and restoring them could
undo changes to the data. The app is a warehouse where any lost data would
be reloaded. The other alternative is separate databases. Anyone have any
ideas or suggestions?|||Transaction log backups are required to bring the Filegroup back into sync
with the other filegroups. IF there have been no transactions since the
filegroup was backed up , then logs are not required.
You could just do a transaction log backup after loading new data...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"jeff ericson" <anonymous@.discussions.microsoft.com> wrote in message
news:8C5F4B6A-F8A3-404A-BD26-F9245E061D80@.microsoft.com...
> We have a situation where a large DB(150GB) has mostly static data in it.
We were considering a filegroup solution that locates the static data in
one(or more) files and the dynamic content is stored in another but it seems
that any file/filegroup restore requires a transaction log restore to
complete the database. We don't have any transactional requirements, in
fact they weren't even dumping them till I arrived and restoring them could
undo changes to the data. The app is a warehouse where any lost data would
be reloaded. The other alternative is separate databases. Anyone have any
ideas or suggestions?|||We have a similar situation where I work and I'm not sure I understand
the solution you propose. We have a 120GB database. Some of the data
has become static and we are going to move that data (approx 50GB) to
a new table on a seperate filegroup. Immediately following the move,
we will do a backup of that filegroup, and move it to tape. Now, there
will be no changes to the data on this filegroup. It is old data that
we are only keeping around for research. So, 10 months from now, if
something goes wrong on the database and we have to do a restore, are
you saying that I'll have to apply trans. logs for the past 10 months
in order to get this new filegroup back into sync with the rest of the
database? Surely there must be a better way...
Currently, with everything in one filegroup, we are using simple
recovery model. We run a weekly full backup, with nightly differential
backups. We have no need for up to the minute transaction recovery, so
we aren't currently doing a transaction log backup. We find that this
scenario allows us all the recovery we really need. We would simply
like to move some static data out to a new file, do a one-time backup
of that file, and then continue to backup the primary filegroup much
the same way we have been. Is this not possible? What is the simplest
way to accomplish what we need here?
It would be very helpful if someone would post the steps that should
be taken to maintain this type of backup / restore strategy for
filegroups with static data. I've scoured google groups to no avail.
We are running Windows 2000 Server SP4 and SQL Server 2000 SP3. Thanks
for your posts...
Brandon
bspfantasyleague@.dont.spam.me.hotmail.com
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message news:<OjffuA7zDHA.1688@.TK2MSFTNGP10.phx.gbl>...
> Transaction log backups are required to bring the Filegroup back into sync
> with the other filegroups. IF there have been no transactions since the
> filegroup was backed up , then logs are not required.
> You could just do a transaction log backup after loading new data...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "jeff ericson" <anonymous@.discussions.microsoft.com> wrote in message
> news:8C5F4B6A-F8A3-404A-BD26-F9245E061D80@.microsoft.com...
> > We have a situation where a large DB(150GB) has mostly static data in it.
> We were considering a filegroup solution that locates the static data in
> one(or more) files and the dynamic content is stored in another but it seems
> that any file/filegroup restore requires a transaction log restore to
> complete the database. We don't have any transactional requirements, in
> fact they weren't even dumping them till I arrived and restoring them could
> undo changes to the data. The app is a warehouse where any lost data would
> be reloaded. The other alternative is separate databases. Anyone have any
> ideas or suggestions?|||My problem was that after restoring the file group backup, the database was inaccessible until I applied a transaction log backup. Any attempts to access was met with the message indicating the database is in the middle of a restore. In my case, restoring the transaction logs applied transactions I did not want applied. My question is can you selctivily restore file grouops without having to do a transaction log restore afterwards?
up DW's if they can easily redo the data from scratch.
--
Andrew J. Kelly
SQL Server MVP
"jeff ericson" <anonymous@.discussions.microsoft.com> wrote in message
news:8C5F4B6A-F8A3-404A-BD26-F9245E061D80@.microsoft.com...
> We have a situation where a large DB(150GB) has mostly static data in it.
We were considering a filegroup solution that locates the static data in
one(or more) files and the dynamic content is stored in another but it seems
that any file/filegroup restore requires a transaction log restore to
complete the database. We don't have any transactional requirements, in
fact they weren't even dumping them till I arrived and restoring them could
undo changes to the data. The app is a warehouse where any lost data would
be reloaded. The other alternative is separate databases. Anyone have any
ideas or suggestions?|||Transaction log backups are required to bring the Filegroup back into sync
with the other filegroups. IF there have been no transactions since the
filegroup was backed up , then logs are not required.
You could just do a transaction log backup after loading new data...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"jeff ericson" <anonymous@.discussions.microsoft.com> wrote in message
news:8C5F4B6A-F8A3-404A-BD26-F9245E061D80@.microsoft.com...
> We have a situation where a large DB(150GB) has mostly static data in it.
We were considering a filegroup solution that locates the static data in
one(or more) files and the dynamic content is stored in another but it seems
that any file/filegroup restore requires a transaction log restore to
complete the database. We don't have any transactional requirements, in
fact they weren't even dumping them till I arrived and restoring them could
undo changes to the data. The app is a warehouse where any lost data would
be reloaded. The other alternative is separate databases. Anyone have any
ideas or suggestions?|||We have a similar situation where I work and I'm not sure I understand
the solution you propose. We have a 120GB database. Some of the data
has become static and we are going to move that data (approx 50GB) to
a new table on a seperate filegroup. Immediately following the move,
we will do a backup of that filegroup, and move it to tape. Now, there
will be no changes to the data on this filegroup. It is old data that
we are only keeping around for research. So, 10 months from now, if
something goes wrong on the database and we have to do a restore, are
you saying that I'll have to apply trans. logs for the past 10 months
in order to get this new filegroup back into sync with the rest of the
database? Surely there must be a better way...
Currently, with everything in one filegroup, we are using simple
recovery model. We run a weekly full backup, with nightly differential
backups. We have no need for up to the minute transaction recovery, so
we aren't currently doing a transaction log backup. We find that this
scenario allows us all the recovery we really need. We would simply
like to move some static data out to a new file, do a one-time backup
of that file, and then continue to backup the primary filegroup much
the same way we have been. Is this not possible? What is the simplest
way to accomplish what we need here?
It would be very helpful if someone would post the steps that should
be taken to maintain this type of backup / restore strategy for
filegroups with static data. I've scoured google groups to no avail.
We are running Windows 2000 Server SP4 and SQL Server 2000 SP3. Thanks
for your posts...
Brandon
bspfantasyleague@.dont.spam.me.hotmail.com
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message news:<OjffuA7zDHA.1688@.TK2MSFTNGP10.phx.gbl>...
> Transaction log backups are required to bring the Filegroup back into sync
> with the other filegroups. IF there have been no transactions since the
> filegroup was backed up , then logs are not required.
> You could just do a transaction log backup after loading new data...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "jeff ericson" <anonymous@.discussions.microsoft.com> wrote in message
> news:8C5F4B6A-F8A3-404A-BD26-F9245E061D80@.microsoft.com...
> > We have a situation where a large DB(150GB) has mostly static data in it.
> We were considering a filegroup solution that locates the static data in
> one(or more) files and the dynamic content is stored in another but it seems
> that any file/filegroup restore requires a transaction log restore to
> complete the database. We don't have any transactional requirements, in
> fact they weren't even dumping them till I arrived and restoring them could
> undo changes to the data. The app is a warehouse where any lost data would
> be reloaded. The other alternative is separate databases. Anyone have any
> ideas or suggestions?|||My problem was that after restoring the file group backup, the database was inaccessible until I applied a transaction log backup. Any attempts to access was met with the message indicating the database is in the middle of a restore. In my case, restoring the transaction logs applied transactions I did not want applied. My question is can you selctivily restore file grouops without having to do a transaction log restore afterwards?
File Group backup and restores
I have a database that has multiple data files (.mdf, .ndf), the complete
backup of this database has now exceeded the size of the physical drive that
I used to store the backup file.
We have tried taking FileGroup Backups, placing files of the backup on
separate drives. The Backup is sucessful.
I am having a problem restoring the backup files. It seems that I have to
overwrite the original database file. How can I restore this FileGroup
backup to the same server but to a different database? Is this possible?
Thanks,
Sal
Read about the PARTIAL options of the RESTORE command. Also see below section of Books Online
"Partial Database Restore Operations".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>I have a database that has multiple data files (.mdf, .ndf), the complete
> backup of this database has now exceeded the size of the physical drive that
> I used to store the backup file.
> We have tried taking FileGroup Backups, placing files of the backup on
> separate drives. The Backup is sucessful.
> I am having a problem restoring the backup files. It seems that I have to
> overwrite the original database file. How can I restore this FileGroup
> backup to the same server but to a different database? Is this possible?
> Thanks,
> Sal
|||Let me clarify my question. Can a database be restored as a different
database name on the same server where the original backup was taken if using
a FileGroup Backup?
"Tibor Karaszi" wrote:
> Read about the PARTIAL options of the RESTORE command. Also see below section of Books Online
> "Partial Database Restore Operations".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ssummo" <ssummo@.discussions.microsoft.com> wrote in message
> news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>
|||If you follow the rules for a PARTIAL restore (as documented in Books Online), then the answer is
yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:7489799D-888C-4141-96C4-BC0EC2FCC9BD@.microsoft.com...[vbcol=seagreen]
> Let me clarify my question. Can a database be restored as a different
> database name on the same server where the original backup was taken if using
> a FileGroup Backup?
>
> "Tibor Karaszi" wrote:
backup of this database has now exceeded the size of the physical drive that
I used to store the backup file.
We have tried taking FileGroup Backups, placing files of the backup on
separate drives. The Backup is sucessful.
I am having a problem restoring the backup files. It seems that I have to
overwrite the original database file. How can I restore this FileGroup
backup to the same server but to a different database? Is this possible?
Thanks,
Sal
Read about the PARTIAL options of the RESTORE command. Also see below section of Books Online
"Partial Database Restore Operations".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>I have a database that has multiple data files (.mdf, .ndf), the complete
> backup of this database has now exceeded the size of the physical drive that
> I used to store the backup file.
> We have tried taking FileGroup Backups, placing files of the backup on
> separate drives. The Backup is sucessful.
> I am having a problem restoring the backup files. It seems that I have to
> overwrite the original database file. How can I restore this FileGroup
> backup to the same server but to a different database? Is this possible?
> Thanks,
> Sal
|||Let me clarify my question. Can a database be restored as a different
database name on the same server where the original backup was taken if using
a FileGroup Backup?
"Tibor Karaszi" wrote:
> Read about the PARTIAL options of the RESTORE command. Also see below section of Books Online
> "Partial Database Restore Operations".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ssummo" <ssummo@.discussions.microsoft.com> wrote in message
> news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>
|||If you follow the rules for a PARTIAL restore (as documented in Books Online), then the answer is
yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:7489799D-888C-4141-96C4-BC0EC2FCC9BD@.microsoft.com...[vbcol=seagreen]
> Let me clarify my question. Can a database be restored as a different
> database name on the same server where the original backup was taken if using
> a FileGroup Backup?
>
> "Tibor Karaszi" wrote:
File Group backup and restores
I have a database that has multiple data files (.mdf, .ndf), the complete
backup of this database has now exceeded the size of the physical drive that
I used to store the backup file.
We have tried taking FileGroup Backups, placing files of the backup on
separate drives. The Backup is sucessful.
I am having a problem restoring the backup files. It seems that I have to
overwrite the original database file. How can I restore this FileGroup
backup to the same server but to a different database? Is this possible?
Thanks,
SalRead about the PARTIAL options of the RESTORE command. Also see below sectio
n of Books Online
"Partial Database Restore Operations".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>I have a database that has multiple data files (.mdf, .ndf), the complete
> backup of this database has now exceeded the size of the physical drive th
at
> I used to store the backup file.
> We have tried taking FileGroup Backups, placing files of the backup on
> separate drives. The Backup is sucessful.
> I am having a problem restoring the backup files. It seems that I have to
> overwrite the original database file. How can I restore this FileGroup
> backup to the same server but to a different database? Is this possible?
> Thanks,
> Sal|||Let me clarify my question. Can a database be restored as a different
database name on the same server where the original backup was taken if usin
g
a FileGroup Backup?
"Tibor Karaszi" wrote:
> Read about the PARTIAL options of the RESTORE command. Also see below sect
ion of Books Online
> "Partial Database Restore Operations".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ssummo" <ssummo@.discussions.microsoft.com> wrote in message
> news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>|||If you follow the rules for a PARTIAL restore (as documented in Books Online
), then the answer is
yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:7489799D-888C-4141-96C4-BC0EC2FCC9BD@.microsoft.com...[vbcol=seagreen]
> Let me clarify my question. Can a database be restored as a different
> database name on the same server where the original backup was taken if us
ing
> a FileGroup Backup?
>
> "Tibor Karaszi" wrote:
>
backup of this database has now exceeded the size of the physical drive that
I used to store the backup file.
We have tried taking FileGroup Backups, placing files of the backup on
separate drives. The Backup is sucessful.
I am having a problem restoring the backup files. It seems that I have to
overwrite the original database file. How can I restore this FileGroup
backup to the same server but to a different database? Is this possible?
Thanks,
SalRead about the PARTIAL options of the RESTORE command. Also see below sectio
n of Books Online
"Partial Database Restore Operations".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>I have a database that has multiple data files (.mdf, .ndf), the complete
> backup of this database has now exceeded the size of the physical drive th
at
> I used to store the backup file.
> We have tried taking FileGroup Backups, placing files of the backup on
> separate drives. The Backup is sucessful.
> I am having a problem restoring the backup files. It seems that I have to
> overwrite the original database file. How can I restore this FileGroup
> backup to the same server but to a different database? Is this possible?
> Thanks,
> Sal|||Let me clarify my question. Can a database be restored as a different
database name on the same server where the original backup was taken if usin
g
a FileGroup Backup?
"Tibor Karaszi" wrote:
> Read about the PARTIAL options of the RESTORE command. Also see below sect
ion of Books Online
> "Partial Database Restore Operations".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ssummo" <ssummo@.discussions.microsoft.com> wrote in message
> news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>|||If you follow the rules for a PARTIAL restore (as documented in Books Online
), then the answer is
yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:7489799D-888C-4141-96C4-BC0EC2FCC9BD@.microsoft.com...[vbcol=seagreen]
> Let me clarify my question. Can a database be restored as a different
> database name on the same server where the original backup was taken if us
ing
> a FileGroup Backup?
>
> "Tibor Karaszi" wrote:
>
File Group backup and restores
I have a database that has multiple data files (.mdf, .ndf), the complete
backup of this database has now exceeded the size of the physical drive that
I used to store the backup file.
We have tried taking FileGroup Backups, placing files of the backup on
separate drives. The Backup is sucessful.
I am having a problem restoring the backup files. It seems that I have to
overwrite the original database file. How can I restore this FileGroup
backup to the same server but to a different database? Is this possible?
Thanks,
SalRead about the PARTIAL options of the RESTORE command. Also see below section of Books Online
"Partial Database Restore Operations".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>I have a database that has multiple data files (.mdf, .ndf), the complete
> backup of this database has now exceeded the size of the physical drive that
> I used to store the backup file.
> We have tried taking FileGroup Backups, placing files of the backup on
> separate drives. The Backup is sucessful.
> I am having a problem restoring the backup files. It seems that I have to
> overwrite the original database file. How can I restore this FileGroup
> backup to the same server but to a different database? Is this possible?
> Thanks,
> Sal|||Let me clarify my question. Can a database be restored as a different
database name on the same server where the original backup was taken if using
a FileGroup Backup?
"Tibor Karaszi" wrote:
> Read about the PARTIAL options of the RESTORE command. Also see below section of Books Online
> "Partial Database Restore Operations".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ssummo" <ssummo@.discussions.microsoft.com> wrote in message
> news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
> >I have a database that has multiple data files (.mdf, .ndf), the complete
> > backup of this database has now exceeded the size of the physical drive that
> > I used to store the backup file.
> > We have tried taking FileGroup Backups, placing files of the backup on
> > separate drives. The Backup is sucessful.
> > I am having a problem restoring the backup files. It seems that I have to
> > overwrite the original database file. How can I restore this FileGroup
> > backup to the same server but to a different database? Is this possible?
> > Thanks,
> > Sal
>|||If you follow the rules for a PARTIAL restore (as documented in Books Online), then the answer is
yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:7489799D-888C-4141-96C4-BC0EC2FCC9BD@.microsoft.com...
> Let me clarify my question. Can a database be restored as a different
> database name on the same server where the original backup was taken if using
> a FileGroup Backup?
>
> "Tibor Karaszi" wrote:
>> Read about the PARTIAL options of the RESTORE command. Also see below section of Books Online
>> "Partial Database Restore Operations".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "ssummo" <ssummo@.discussions.microsoft.com> wrote in message
>> news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>> >I have a database that has multiple data files (.mdf, .ndf), the complete
>> > backup of this database has now exceeded the size of the physical drive that
>> > I used to store the backup file.
>> > We have tried taking FileGroup Backups, placing files of the backup on
>> > separate drives. The Backup is sucessful.
>> > I am having a problem restoring the backup files. It seems that I have to
>> > overwrite the original database file. How can I restore this FileGroup
>> > backup to the same server but to a different database? Is this possible?
>> > Thanks,
>> > Sal
>>
backup of this database has now exceeded the size of the physical drive that
I used to store the backup file.
We have tried taking FileGroup Backups, placing files of the backup on
separate drives. The Backup is sucessful.
I am having a problem restoring the backup files. It seems that I have to
overwrite the original database file. How can I restore this FileGroup
backup to the same server but to a different database? Is this possible?
Thanks,
SalRead about the PARTIAL options of the RESTORE command. Also see below section of Books Online
"Partial Database Restore Operations".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>I have a database that has multiple data files (.mdf, .ndf), the complete
> backup of this database has now exceeded the size of the physical drive that
> I used to store the backup file.
> We have tried taking FileGroup Backups, placing files of the backup on
> separate drives. The Backup is sucessful.
> I am having a problem restoring the backup files. It seems that I have to
> overwrite the original database file. How can I restore this FileGroup
> backup to the same server but to a different database? Is this possible?
> Thanks,
> Sal|||Let me clarify my question. Can a database be restored as a different
database name on the same server where the original backup was taken if using
a FileGroup Backup?
"Tibor Karaszi" wrote:
> Read about the PARTIAL options of the RESTORE command. Also see below section of Books Online
> "Partial Database Restore Operations".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ssummo" <ssummo@.discussions.microsoft.com> wrote in message
> news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
> >I have a database that has multiple data files (.mdf, .ndf), the complete
> > backup of this database has now exceeded the size of the physical drive that
> > I used to store the backup file.
> > We have tried taking FileGroup Backups, placing files of the backup on
> > separate drives. The Backup is sucessful.
> > I am having a problem restoring the backup files. It seems that I have to
> > overwrite the original database file. How can I restore this FileGroup
> > backup to the same server but to a different database? Is this possible?
> > Thanks,
> > Sal
>|||If you follow the rules for a PARTIAL restore (as documented in Books Online), then the answer is
yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ssummo" <ssummo@.discussions.microsoft.com> wrote in message
news:7489799D-888C-4141-96C4-BC0EC2FCC9BD@.microsoft.com...
> Let me clarify my question. Can a database be restored as a different
> database name on the same server where the original backup was taken if using
> a FileGroup Backup?
>
> "Tibor Karaszi" wrote:
>> Read about the PARTIAL options of the RESTORE command. Also see below section of Books Online
>> "Partial Database Restore Operations".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "ssummo" <ssummo@.discussions.microsoft.com> wrote in message
>> news:D00E627D-5B14-43DD-9150-180E92339756@.microsoft.com...
>> >I have a database that has multiple data files (.mdf, .ndf), the complete
>> > backup of this database has now exceeded the size of the physical drive that
>> > I used to store the backup file.
>> > We have tried taking FileGroup Backups, placing files of the backup on
>> > separate drives. The Backup is sucessful.
>> > I am having a problem restoring the backup files. It seems that I have to
>> > overwrite the original database file. How can I restore this FileGroup
>> > backup to the same server but to a different database? Is this possible?
>> > Thanks,
>> > Sal
>>
Subscribe to:
Posts (Atom)