August 30, 2012 at 12:33 pm
Can we do a restore of a filegroup on to a different database on the same server?
August 30, 2012 at 12:37 pm
Not that I am aware of, don't think that is possible.
August 30, 2012 at 12:57 pm
This makes my life more complicated.
I am trying to move a database with filegroups to a different drive. I don't have the option of taking the db offline and working on it. What's the best way you suggest ? I tried merge and split option and try to do it with switch which didn't worked. Is there any way to perform this task.
August 30, 2012 at 1:48 pm
Without any downtime at all, exceedingly difficult. Add new files to each filegroup on the new location, shrinkfile with the emptyfile option the files that are in the wrong location, then drop them. Can't do that with the MDF though, just secondary filegroups. Add a new log file, wait until the entire active log is in the new file, see if you can drop the old log (I have a feeling that the first log file also can't be dropped)
Can't you even get a couple min downtime? How big's the DB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2012 at 5:06 pm
GilaMonster (8/30/2012)
Without any downtime at all, exceedingly difficult. Add new files to each filegroup on the new location, shrinkfile with the emptyfile option the files that are in the wrong location, then drop them. Can't do that with the MDF though, just secondary filegroups. Add a new log file, wait until the entire active log is in the new file, see if you can drop the old log (I have a feeling that the first log file also can't be dropped)Can't you even get a couple min downtime? How big's the DB?
By shrinking the original files as small as possible, and perfect scripting to checkpoint, set db off line, copy files, alter DB, set online; you may be able to get the downtime to under 10 seconds... maybe...
August 30, 2012 at 5:50 pm
GilaMonster (8/30/2012)
Without any downtime at all, exceedingly difficult. Add new files to each filegroup on the new location, shrinkfile with the emptyfile option the files that are in the wrong location, then drop them. Can't do that with the MDF though, just secondary filegroups. Add a new log file, wait until the entire active log is in the new file, see if you can drop the old log (I have a feeling that the first log file also can't be dropped)Can't you even get a couple min downtime? How big's the DB?
The size of the database is 1.8 TB. There is just one table in the database. Can we restore the backup and change the db names?? The partitioned column is on date. We have 36 filegroups each containing one datafile.
August 31, 2012 at 12:07 am
muthyala_51 (8/30/2012)
Can we restore the backup and change the db names??
Sure, but to avoid losing data you'll need to take the backup WITH NORECOVERY so that the DB goes offline after the backup completes and then the DB is down until the restore completes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 4, 2012 at 12:05 pm
GilaMonster (8/31/2012)
muthyala_51 (8/30/2012)
Can we restore the backup and change the db names??Sure, but to avoid losing data you'll need to take the backup WITH NORECOVERY so that the DB goes offline after the backup completes and then the DB is down until the restore completes.
As the table is partitioned on the date(getdate), I am planning to insert all the new rows to both the databases and rename the databases when they are in sync.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply