SQL Server 2005
What's the best way to move a half-terabyte database from one drive to
another, while minimizing downtime? Simple backup/restore or detach/
copy/reattach take too much time.

What I'd like to do is:
Take a full backup of the original database, {Data}
Restore it under a different name {Data2}.
Turn off the overlaying application and take a differential of {Data}.
Detach {Data}
ALTER DATABASE {Data2} MODIFY NAME = {Data}
Restore the incremental to the new, relocated {Data}

Will SQL Server let me restore, after doing all that?

Thanks,
Chris

It's hard to know for sure from here if you have all your ducks line
up properly. Perhaps you should do a practice run, without taking
down the production database. I would also suggest scripting out your
plan and executing the script when you do the actual move. You're
less likely to mess up with a script you've previously tested.

I hope that s!

<cruscio@gmail.com> wrote in message
news:bafd2d7e-68dc-406b-a33b-5744bb88cf7a@u36g2000prf.googlegroups.com...
> SQL Server 2005
> What's the best way to move a half-terabyte database from one drive to
> another, while minimizing downtime? Simple backup/restore or detach/
> copy/reattach take too much time.
>
> What I'd like to do is:
> Take a full backup of the original database, {Data}
> Restore it under a different name {Data2}.
> Turn off the overlaying application and take a differential of {Data}.
> Detach {Data}
> ALTER DATABASE {Data2} MODIFY NAME = {Data}
> Restore the incremental to the new, relocated {Data}
>
> Will SQL Server let me restore, after doing all that?
>
> Thanks,
> Chris

Hi Chris

Have you thought about partitioning the data into multiple files of
different discs to take advantage of more spindles?

John

I doubt you will be able to restore the diff backup after renaming the database. Or, to be more
precise, to rename you need to recover the database (do the restore with RECOVERY) and after that
you won't be able to do any more restores.

But why do you want to rename before that last diff restore? Why not do all restore into the temp db
name and after all restore has been performed you switch to the desired name.

Whether to use diff or log backup is also something you want to think about...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


<cruscio@gmail.com> wrote in message
news:bafd2d7e-68dc-406b-a33b-5744bb88cf7a@u36g2000prf.googlegroups.com...
> SQL Server 2005
> What's the best way to move a half-terabyte database from one drive to
> another, while minimizing downtime? Simple backup/restore or detach/
> copy/reattach take too much time.
>
> What I'd like to do is:
> Take a full backup of the original database, {Data}
> Restore it under a different name {Data2}.
> Turn off the overlaying application and take a differential of {Data}.
> Detach {Data}
> ALTER DATABASE {Data2} MODIFY NAME = {Data}
> Restore the incremental to the new, relocated {Data}
>
> Will SQL Server let me restore, after doing all that?
>
> Thanks,
> Chris

On May 17, 12:56 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@hotmail.nomail.co m> wrote:
> I doubt you will be able to restore the diff backup after renaming the database. Or, to be more
> precise, to rename you need to recover the database (do the restore with RECOVERY) and after that
> you won't be able to do any more restores.
>
> But why do you want to rename before that last diff restore? Why not do all restore into the temp db
> name and after all restore has been performed you switch to the desired name.
>
> Whether to use diff or log backup is also something you want to think about...
>
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> <crus...@gmail.com> wrote in message
>
> news:bafd2d7e-68dc-406b-a33b-5744bb88cf7a@u36g2000prf.googlegroups.com...
>
> > SQL Server 2005
> > What's the best way to move a half-terabyte database from one drive to
> > another, while minimizing downtime? Simple backup/restore or detach/
> > copy/reattach take too much time.
>
> > What I'd like to do is:
> > Take a full backup of the original database, {Data}
> > Restore it under a different name {Data2}.
> > Turn off the overlaying application and take a differential of {Data}.
> > Detach {Data}
> > ALTER DATABASE {Data2} MODIFY NAME = {Data}
> > Restore the incremental to the new, relocated {Data}
>
> > Will SQL Server let me restore, after doing all that?
>
> > Thanks,
> > Chris

Thanks Tibor! That was exactly what I was missing. I did a test and
have a script ready for the production run.
John, This move is happening for precisely that reason (more
spindles). The DB lives on an ill configured SAN and is being moved to
a large set of smaller faster drives. We're looking at multiple files
across individual LUNs, but that will be some time out.

Thanks so much!
Chris

<cruscio@gmail.com> wrote in message
news:1bfe7102-af76-486f-b852-c03deb853f34@z16g2000prn.googlegroups.com...
> On May 17, 12:56 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@hotmail.nomail.co m> wrote:
>> I doubt you will be able to restore the diff backup after renaming the
>> database. Or, to be more
>> precise, to rename you need to recover the database (do the restore with
>> RECOVERY) and after that
>> you won't be able to do any more restores.
>>
>> But why do you want to rename before that last diff restore? Why not do
>> all restore into the temp db
>> name and after all restore has been performed you switch to the desired
>> name.
>>
>> Whether to use diff or log backup is also something you want to think
>> about...
>>
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>>
>> <crus...@gmail.com> wrote in message
>>
>> news:bafd2d7e-68dc-406b-a33b-5744bb88cf7a@u36g2000prf.googlegroups.com...
>>
>> > SQL Server 2005
>> > What's the best way to move a half-terabyte database from one drive to
>> > another, while minimizing downtime? Simple backup/restore or detach/
>> > copy/reattach take too much time.
>>
>> > What I'd like to do is:
>> > Take a full backup of the original database, {Data}
>> > Restore it under a different name {Data2}.
>> > Turn off the overlaying application and take a differential of {Data}.
>> > Detach {Data}
>> > ALTER DATABASE {Data2} MODIFY NAME = {Data}
>> > Restore the incremental to the new, relocated {Data}
>>
>> > Will SQL Server let me restore, after doing all that?
>>
>> > Thanks,
>> > Chris
>
> Thanks Tibor! That was exactly what I was missing. I did a test and
> have a script ready for the production run.
> John, This move is happening for precisely that reason (more
> spindles). The DB lives on an ill configured SAN and is being moved to
> a large set of smaller faster drives. We're looking at multiple files
> across individual LUNs, but that will be some time out.
>
> Thanks so much!
> Chris
You may want to talk to the SAN guys to see if there is a quick way to move
the files.

Hardware can only to a certain extent, if you know the access paths for
the data it may give more benefit to have multiple drives over fewer
spindles.

John