| Author |
Topic |
|
jaypatel
Starting Member
10 Posts |
 Posted - 2003-04-17 : 10:15:03
|
| Been going through on-line books and I can't see anything about the ability to move files ( not even through alter database ). Do I take it that the only way is through the use of the restore command? |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-04-17 : 11:24:10
|
| Take a look at the section on "Attaching and Detaching" databases.OS |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-17 : 12:18:17
|
| Here is the article from MS that explains in detail what to do:[url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071[/url]Tara |
|
|
|
jaypatel
Starting Member
10 Posts |
Posted - 2003-04-18 : 03:02:28
|
| Thanks very much for that - just one slight question - db_detach will mean in essence taking a db offline - can I move a file by marking it offline and then moving while db is active? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-04-18 : 09:03:15
|
| It's better to just follow the directions given in the proper sequence. Deviating from them can have unforseen consequences, it's not worth taking the risk when your data is at stake. Trying to do any of these actions while the database is online is a surefire way to lose data. |
|
|
|
jaypatel
Starting Member
10 Posts |
Posted - 2003-04-22 : 05:30:44
|
| True - but then how do you manage files within a 24*7 environment. Imagine a data file you know will hit space problems on a physical disk, I can add a second but I'd rather move the file onto a faster and larger disk. I would have to ask for downtime. |
|
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2003-04-22 : 10:10:40
|
| Even 24/7 shops have specific times of the year scheduled for maintenance like hardware upgrades, service packs, and such. If downtime is really such a big deal, do like you said and add another file to the file group on a different disk until you can get it moved.Jeff Banschbach, MCDBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-22 : 12:34:06
|
| 24/7 environments usually means up time is around 99%. 24/7 does not mean that you can not bring down the server/databases for maintenance. If you can't perform maintenance on the system, then your application is going to eventually fail and cause more downtime than all of the scheduled maintenance combined.Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-22 : 13:01:39
|
| Think about what you're asking."Moving a database file to another disk without taking the database offline."You would have to mirror the file to the new disk (without down time) then remove the access to the old file (without down time).Even if that's possible it sounds very risky.You can minimise the downtime by restoring a backup to the new disk then taking and restoring an incremental while the system is down.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-04-22 : 21:21:36
|
quote:
Think about what you're asking."Moving a database file to another disk without taking the database offline."
Another analogy that illustrates Nigel's point further is "I want to change a flat tire on a car while I'm driving it full speed." It's simply not possible. |
|
|
|
jaypatel
Starting Member
10 Posts |
Posted - 2003-04-24 : 06:20:37
|
| Taking another product - Oracle - the architecture allows offlining a tablespace ( similar to a file group ) while the db is in use. This is great if as a DBA you see some problems.For example, you're OS admin guys suddenly realise a specific problem with a disk. What are you going to do - what will you;re dept. do - look at a calendar - select a schedule, raise the relevant change control mechansism to plan an outage - too slow. Moving a file becomes an operational priority which needs to act immediately - to avoid failure and a more length recovery process.Again - may this is more to do with were SQL Server evolved from - small solutions and small dept. where downtime is not an issue - just happens that its now a cheaper and easier option than Oracle - but the business requirements tend to remain fairly static. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-24 : 08:51:37
|
| Thought you were just running out of space.This should be known well before it becomes a problem and treated accordingly.If it's an emergency then get a proper dba.As to your new scenario - I would take the system down, take a backup, check the backup, get rid of the faulty disk, check to see if the database has been damaged then bring back the system.I would not do any of this as an emegency hack but would make sure everyone is informed.If the system needs to keep running this would already have been planned and it would be running on the standby system - which of course you have due to the nature of the system.If not - again get a proper dba.Response to a problem is never reconfigure the system in an unplanned manner - or maybe oracle expets dba's to be cowboys.p.s. I suspect you may be able to do what you suggest in sql server between checkpoints but should never be necessary and not even worth testing.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-04-24 : 12:06:24
|
| Am I missing something here? If you are really in a 24/7 requirement, then surely you are running redundant (clustered?) SQL Servers. In which case, I would think you would be able to take one node down, perform needed maintenance, put it back up, take the another node down, etc., etc.OK, I'm speaking only from theory here because I haven't worked with SQL Server configured that way, but isn't that the way 24/7 requirements are handled?------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-24 : 13:54:03
|
| Good point AjarnMark. JayPatel, if you are running clustering on your SQL Server, then do as AjarnMark mentioned. Otherwise, you are stuck with some downtime (although it should be very minimal if you are just moving files on the same server).Tara |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-04-24 : 14:56:50
|
| What about just adding another file to the primary filegroup, and then removing the existing file? Shouldn't that force SQL server to move the data with no downtime? Heavy load, yes, but the way I see it, that should work.Cheers-b |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-24 : 17:50:17
|
quote:
If the system needs to keep running this would already have been planned and it would be running on the standby system - which of course you have due to the nature of the system.If not - again get a proper dba.
==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-04-25 : 00:52:29
|
| Mark,I don't think clustering solves this problem. The active node has to have control over the shared disk space. So failing over doesn't allow you to move the files and keep the DB up. (Unless I'm missing something).-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
|
Is Restore the only method to move files?