May 24, 2009 at 2:56 pm
Is there ANY way to move database files without taking a database offline? Im just checking to be sure there are no sneaky tricks im unaware of 🙂
May 24, 2009 at 3:24 pm
Well i guess you could try taking a backup of the online database and restoring the db to a different server or location. Then you wll have another copy of the database files wthout having to take the source database offlne.
sorry I think I may have misunderstood your question...I don't think you can change the location of databases underlying datafiles, i.e changing folders or drives of the data files with taking the database offline
Gethyn Elliswww.gethynellis.com
May 24, 2009 at 4:05 pm
winston Smith (5/24/2009)
Is there ANY way to move database files without taking a database offline?
No. Reason is that to move the files, SQL has to not be using them. Hence the DB must be offline or detached.
Two ways to move a database's files.
1) Detach the DB, move the files, reattach.
2) Run alter database with the MODIFY clause to specify the new location. Take the database offline (ALTER DATABASE ... SET OFFLINE), move the files, bring the DB back online.
I've seen a backup trick done too, but requires 2x disk space and there's still a short offline period.
1) Backup the source DB
2) Restore that with the files where you want them, with a different name and with the NORECOVERY option.
3) Backup the log of the source DB WITH NORECOVERY. This will make the database unusable.
4) Restore the log to the 'new DB' specifying WITH RECOVERY
5) Delete the source DB
6) Rename the new DB.
With this the DB is unavailable from steps 3 to 6. It may be a shorter time than moving the files would take, especially if DB's big, but there is still some downtime.
Edit: Misspelling and incorrect name for statement.
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
May 24, 2009 at 4:42 pm
Are you moving on the same server or to a new server?
In either case, Gail's #2 will have the least downtime. You can keep taking logs and moving them, getting to a smaller and smaller log, and then smaller downtime.
There still will be downtime, but you can minimize it to a few minutes.
May 25, 2009 at 3:51 am
hi,
better move the dbs on offline because u r not going to get any errors,
Goodluck
KingManjunath
May 25, 2009 at 5:54 am
If you are not moving the primary data file you can create another file in the same file group and then use the DBCC SHRINKFILE (filename, EMPTYFILE) command. This will migrate the data from the specified data file to other files within the same file group. Once that's done, you can delete the file specified in the SHRINKFILE command.
May 25, 2009 at 7:41 am
winston
just to re iterate
the MOVE is only used when restoring a backup. When moving a data file location via ALTER DATABASE use MODIFY FILE. You can issue the statement while the database is still online and the path will not change until the database goes offline then back online
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 25, 2009 at 7:50 am
Perry Whittle (5/25/2009)
the MOVE is only used when restoring a backup. When moving a data file location via ALTER DATABASE use MODIFY FILE. You can issue the statement while the database is still online and the path will not change until the database goes offline then back online
Just to note, with a user database, while the DB is offline you must go and move the files. SQL won't move them to the new directory itself. That's only TempDB. If you just modify, take DB offline and then bring it online, you'll get errors saying that the files are not accessible.
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
May 25, 2009 at 8:53 am
GilaMonster (5/25/2009)
you'll get errors saying that the files are not accessible.
you'll also get this if you mistype the file and or path too.
Tip: If you do get this message don't panic and go into the loop of constantly typing and issuing new paths\filenames. Instead open the table sys.sysaltfiles and check the filename column in there for the paths to see what you have set, then you will be able to correct it easily
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply