Move a NAV database to another location! It´s safe?

nav_student

Hi guys!

I want to change my database files to another location.

I will try this procedure( but i want to know your opinion - It´s safe to do it on ):

Consider backing up the database before you start, in case anything goes wrong.
Get a list of all the filenames for files currently used in the database. Do this using:
Use MyDatabase
Go
Exec sp_helpfile
This step is essential - you can only successfully reattach the database by specifying each and every file.

Detach the database. This prevents others from using it while the files are being moved. Do this using:
Exec sp_detach_db 'MyDatabase'
You will not be able to detach a database whilst it is in use.

Move the database files to their new location, or rename them, as appropriate to your needs.
Reattach the database, explicitly specifying the full pathname of every file that constitutes the database. This includes any files that were not moved or renamed. For example:
Exec sp_attach_db 'MyDatabase',
'E:\MsSql7\NewHome\MyDatabase_Data.mdf',
'E:\MsSql7\NewHome\MyDatabase_Log.ldf'
Notes:

'sp_attach_db' can only be used with up to 16 files. If the database has more than 16 files then instead use 'Create Database' with the 'For Attach' clause.
If the detached database was enabled for replication and is attached to a DIFFERENT server then 'sp_removedbreplication' should be run to remove replication from the database.
Only members of the 'sysadmin' server role can execute 'sp_detach_db' and 'sp_attach_db'.

netblazer

Here's a simpler version using the GUI.

make sure everybody's out of Nav
put db in single user mode
take full back
run restore to new location in GUI (can be scripted too if you need to reuse)
put restored version into single_user mode
rename old db.
rename new db to old name
run check db to make sure ou have no corruption
put db in multi_user mode

You're good to go.

Using this way you always have your real db ready to go if anything goes wrong. Of course this assumes you have a lot of free space (backup plus restore may take as much as 10 extra GB if the current DB uses 5 GB).

stryk

Hi!

Have in mind that the "Detach/Attach" processing could also be accomplished with "Mangement Studio" (GUI). And yes, it should be pretty save.
The potential risk is (just academic), that the files get a physical damage when copied to the other location - but that would mean your disk-controler is defect or your LAN is screwed; in such a case you have graver problems ...