September 26, 2011 at 3:09 am
Hi
We are shifting from one server to another.
The new plattform will be sql 2008.
I have several large databases that I have to move. I can not take the system offline while I do this, so I therefore want to do a backup and restore.
My first database is our catalog database. The backup is approx 1 TB. I have moved this to my new server and started restoring.
In our previous enviroment we had 3 data drives and 2 log drives for this database, each on 300 GB, Now we have one TB drive for data and 1 TB for log.
In advanced options I have altered the loaction so that the catalog_1, catalog_2 and catalog_3 now resides on the same drive, K, while the two log files now reside on L drive.
Is there any way to reduce the usage of files so instead of having 3 data files I would get one?
Hope to hear from you.
Regards
Dan
September 26, 2011 at 4:57 am
Not during a restore operation. You'll have to restore the database first, then drop the number of files.
By the way, I'd suggest leaving a few file groups, even if you're posting to a single large SAN. Separating storage and access is still a benefit that you'll see if you have multiple filegroups, but one you can't get with a single filegroup.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 26, 2011 at 8:28 am
Dan-Ketil Jakobsen (9/26/2011)
and 1 TB for log.
wow!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 26, 2011 at 10:35 am
definitely get rid of one of the log files after the restore, having two is of no use at all.
And as perry said, 1 terrabyte log, wow!
And you are going to need a little bit of downtime somewhere along the way.
---------------------------------------------------------------------
September 26, 2011 at 11:42 am
george sibbald (9/26/2011)
definitely get rid of one of the log files after the restore, having two is of no use at all.
I concur
If the database is in simple recovery when deleting the empty log file it will just disappear. If the database is in Full recovery you'll notice that after removing the file it still shows if you perform
Select name, physical_name from sys.master_files where database_id = db_id('mydb')
Just run a transaction log backup and it will disappear Houdini style 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 26, 2011 at 11:55 am
george sibbald (9/26/2011)
definitely get rid of one of the log files after the restore, having two is of no use at all.And as perry said, 1 terrabyte log, wow!
And you are going to need a little bit of downtime somewhere along the way.
Of course he didn't say that the log itself was a tb, just the drive 🙂
Jared
Jared
CE - Microsoft
September 26, 2011 at 11:58 am
jared-709193 (9/26/2011)
Of course he didn't say that the log itself was a tb, just the drive 🙂Jared
exactly, a TB for log space, WOW 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 29, 2011 at 3:18 am
Hi
Well I am still working on this.
I have managed to attach my catalog database. I kept all three datafiles. Two are stored on one drive and the third pluss the log file is stored on antoher drive. The two other databases are comming, and they will be handled the same way. I hope.
As many of you have commented, we have a huge space set for log. We use Simple mode and most of our log are approx 500 GB of size. For some odd reason.
Ideally I belive that we should be able to run just one log drive for all 3 databases, but since they do grow abit out of control from time to time I have been forecd to do it like this.
September 29, 2011 at 3:58 am
do they by any chance go out of control when you reindex your database?
---------------------------------------------------------------------
September 29, 2011 at 4:11 am
How will you manage the move without downtime? Won't the data be obsolete by the time you have backed-up, moved around and restored the 1TB Backup to the new server? Or you can set the application in some read-only mode? Or you plan to put in place transaction log shipping between the two platforms?
September 29, 2011 at 4:14 am
Fabrizio Faleni (9/29/2011)
How will you manage the move without downtime? Won't the data be obsolete by the time you have backed-uo, moved arount and restored the 1TB Backup to the new server? Or you can set the application in some read-only mode?
by using log restores after the full restore.
will still be some downtime though.
---------------------------------------------------------------------
October 14, 2011 at 12:33 am
Hi all. Thanks for your reply.
We now have the entire system up running. More or less.
And I did it without downtime at all.
With regards to the log drive setup, I still have set 1 TB of drive fore each log, but will reduce it til 1 TB for all three log files shortly.
Dan
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply