Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Moving Database Files in SQL Server Expand / Collapse
Author
Message
Posted Monday, February 18, 2013 5:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 1:55 PM
Points: 5, Visits: 48
Hello, all!!! I liked the post. A lot. And I have a question regarding filestream. How do I move a filestream datafile ? Can I move only one datafile instead of all of them al once ?

Thanks in advance from Argentina.

Ariel.
Post #1421140
Posted Monday, February 18, 2013 8:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 6,318, Visits: 13,623
you need to move the folder contents under the folder path that contains the file stream data. Check books online for more info on the alter database command

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1421236
Posted Tuesday, February 19, 2013 9:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 20, 2014 9:30 PM
Points: 10, Visits: 80
Hi,
How would you handle moving a big database(say 200GB) to another drive with minimal time on a production server??

--
Regards,
Karthik
Post #1421707
Posted Tuesday, February 19, 2013 11:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 6,318, Visits: 13,623
Whatever happens you will need to release the sql server handles on the disk file(s), what capacity do you have to copy 200gb of database files across the drives? You need to factor this time into the equation.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1421781
Posted Wednesday, February 20, 2013 12:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 20, 2014 9:30 PM
Points: 10, Visits: 80
Just a thought... not sure if this would work..
but is there any software that can create another copy of these db files irrespective of the handles present on it.. so that once it copies (say upto 95%), we can use the process you mentioned in the post, and copying the left over 5% shouldn't take much time.

Can we do something like this?

Am just curious if this can be done.
[http://lockhunter.com/ : This completely unlocks and deletes the files]

Is there anything which would copy the file even when there is a lock on it?

--
Regards,
Karthik
Post #1421941
Posted Friday, May 17, 2013 8:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 28, 2014 7:10 AM
Points: 8, Visits: 71
Nice article Pezza.

I went through a process just recently of renaming and moving a DB and used the old method of detach/attach, next time round I'll give this way a go.

Good work on another fine article.

Johnny
Post #1454049
Posted Friday, May 17, 2013 10:50 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:26 AM
Points: 311, Visits: 487
john.knight-557608 (5/17/2013)
Nice article Pezza.

I went through a process just recently of renaming and moving a DB and used the old method of detach/attach, next time round I'll give this way a go.

Good work on another fine article.

Johnny


Be careful when you use this method. With a "simple" detach/attach you lose the Transaction log. Better to do a backup and restore.....
Post #1454223
Posted Friday, May 17, 2013 10:55 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:26 AM
Points: 311, Visits: 487
karthiks.840 (2/19/2013)
Hi,
How would you handle moving a big database(say 200GB) to another drive with minimal time on a production server??

--
Regards,
Karthik


An option, should the SQL Server Versions be the same, is to mirror the database across to the new Server and once it is running as it should be, copy the users, Jobs, maintenance plans etc; across to the mirror from the principal. Once prepared, perform a switchover to the new machine and the move is complete with the very Minimum of downtime, measured in seconds. Users will be disconnected for the period of the switchover but that is easily planned for.

Of course, in order to do this you Need to be using the Enterprise Version of the Software.....
Post #1454224
Posted Friday, May 17, 2013 11:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 6,318, Visits: 13,623
kevaburg (5/17/2013)
Better to do a backup and restore.....

Hmm, no, as my article shows the best and supported way to move your data files is to use the alter database command. Doing this, the database is not detached from the server at all.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1454229
Posted Friday, May 17, 2013 11:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 6,318, Visits: 13,623
kevaburg (5/17/2013)
Of course, in order to do this you Need to be using the Enterprise Version of the Software.....

On a slightly different plain to my article as this is moving a database across instances. The synchronous mirroring in standard edition will achieve the same results.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1454230
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse