More moving files, be careful when you re-point

Ok so your in a hurry and you’ve stopped your SQL Service. All relevant datafiles have been moved to a new location and you are ready to roll. SQL Service has been started back up and unfortunately (but you expected this) none of the user databases whose files you moved have come back up. Well thats alright you say, because you are going to repoint them.

Its only at this stage that you realise that you forgot to make a note of all the source locations and you start cursing and hoping that you dont have to move back that 300GB worth of files in order to bring the databases back up to run EXEC sp_helpfile or query the databases’ sysfiles table.

After a biscuit and a cup of tea you remember that this is only after all meta data and should be stored in the master database. Upon further examination you find the sysaltfiles table and are able to reference the filegroup name and original location so that you may now issue the ALTER DATABASE command.
Thankfully this all works a treat and you may now stop panicking.

A couple of points of interest here. Firstly I wondered whether after repointing the database in this situation whether a simple ALTER DATABASE SET ONLINE would be enough or whether you would have to at first OFFLINE. Well as you would hope would happen, all SQL requires is that you issue the ONLINE directive. The other interesting thing that I noticed is that should you mistype the repointing statement, SSMS returns a rather misleading error message which suggests that something not very pleasant may have happened to your datafile, when in fact you have simply entered a wrong statement. More to follow …..

1 thought on “More moving files, be careful when you re-point

  1. retracement Post author

    *Update* Using SQL 2005 and upwards you should use sys.master_files instead of the sysaltfiles compatibility view, since the latter is obviously depreciated.

Comments are closed.