Database.ca SQL Server Blog

My Blog

Sunday, February 23 2014

Keywords: Move SQL Server Databases, move master, move tempdb, move model, move msdb, move AdventureWorks, move mssqlsystemresource, move system databases, Move ReportServer, SQL 2005, 2008, 2008R2, 2012, 2014

Moving SQL Server System or User Databases to a New Location like a Pro

In this article we will cover:

  • Database Move for SQL Server 2005, 2008, 2008R2, 2012, 2014
  • Definition of a SQL Server Database Move
  • Database Move Types
  • Moving Databases using Detach-Attach method
  • Moving Databases using ALTER DATABASE method
  • Moving System Databases
  • The guarded mssqlsystemresource database
  • Moving databases with more than one data/log file
  • A fully functional MoveDatabase stored procedure with the full source code downloadable here

 

Important pre-requisites:

  • You need at least some level of administrative permissions on the SQL Server, and not just be dbowner of the databases to perform these steps...the easiest way would be to make sure you have sysadmin privileges.
  • On important servers, make 100% sure you do have a recent and reliable backup handy. I have witnessed too many database moves going awry.
  • Make sure no one is using the server or at least the databases in question, at the same time.
  • Retain the correct NTFS file permissions if you perform a manual file move. Make sure SQL Server service user has rights to read/write on the destination path! Software like Robocopy help greatly in this regard but are not a must have.
  • Optional and only if it applies: For system databases as well as some user databases, you might want to stop SQL Server Agent, so that scheduled jobs wouldn’t run at the same time...possibly messing things up.
  • If the old drives are being removed after the move operations…then there are a couple of additional steps you must take:
     
    - In SQL Server Agent, make sure errorlog path is correct…otherwise SQL Agent service will fail to start. If you receive error #15281 when trying to access your Agent's properties...then follow the steps in this article.
     
    - Make sure Default Database Path is correct for all future new databases.
     
  • You can obtain the current data and log file names of each database by doing:
     
    use database_name
    go
    sp_helpfile
    go

 

Move SQL Server Databases, move master, move tempdb, move model, move msdb, move AdventureWorks, move mssqlsystemresource, move system databases,  Move ReportServer, SQL 2005, 2008, 2008R2, 2012, 2014

What do I mean by moving a database?
 
A SQL Server database could have one to many data files, and one to many log files. By default and out of the box it contains one of each.
 
Although SQL Server technically allows you to add multiple log files, you should never do so, as not only there is absolutely no advantages in doing so, but there are many disadvantages to it.
 
Having multiple data files however is a different scenario and in a case-by-case basis could be very profitable or little.
 
You may want to move all the files pertaining to a database in one shot, or you could decide to only move the log files to a new location, or only the data files, or only the secondary data files etc…you are not forced to move all the files belonging to a given database in one shot. You can decide exactly which ones to move.

 

Database move types:

  • Type A) User databases, ReportServer, ResportServerTempDB, the entire AdventureWorks family: you could move these databases by applying either the DETACH/ATTACH method, or the ALTER DATABASE method.
  • Type B) master: this database is special. It is moved in a unique way.
  • Type C) tempdb: this database is also unique and needs to be handled slightly different than others.
  • Type D) mssqlsystemresource: I will describe what it is and how it could be moved.
  • Type E) model and msdb: almost like moving tempdb, but slightly different.

 

Move SQL Server Databases, move master, move tempdb, move model, move msdb, move AdventureWorks, move mssqlsystemresource, move system databases,  Move ReportServer, SQL 2005, 2008, 2008R2, 2012, 2014

Detach-Attach method:
 
You could apply this procedure either through SSMS’s UI, or by running T-SQL code.
 
You could only attach a database that had been successfully detached. So per example you cannot stop SQL Service and then move the files to a new location and then attempt a successful attach.
 
You can’t attach/detach master, tempdb or mssqlsystemresource.
 
You can’t attach files belonging to a new version of a database to an older one. Per example you can’t detach from SQL Server 2012 and attach in SQL Server 2005. But you could do the reverse…and once you do that…they will no longer be working in SQL Server 2005…they will be converted internally.
 
This method works well with File Groups. If you had multiple File Groups prior to DETACH…they will all appear intact after ATTACH.
 
DETACH T-SQL code is easy, no matter which database you are detaching, this is the syntax:
 
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'TravelingDB'
GO
 
When attaching databases using T-SQL, you have two choices:
 

  • CREATE DATABASE WITH ATTACH (recommended)
  • sp_attach_db

 
The reason I don’t recommend the latter is that it comes with a limit of 16 files. The first method has no limits.
 
SQL Server Database Move with Attach Detach
 
Attaching a typical database with one data file and one log file:
 
USE [master]
GO
CREATE DATABASE [abc] ON
( FILENAME = N'C:\DatabaseMoveTests\Destination\abc.mdf' ),
( FILENAME = N'C:\DatabaseMoveTests\Destination\abc_log.ldf' )
FOR ATTACH
GO
 
SQL Server Database Move with Attach Detach
 
Attaching a database with 2 data files, and 2 log files:
 
USE [master]
GO
CREATE DATABASE [TravelingDB] ON
( FILENAME = N'C:\DatabaseMoveTests\Destination\TravelingDB.mdf' ),
( FILENAME = N'C:\DatabaseMoveTests\ Destination \TravelingDB_log.ldf' ),
( FILENAME = N'C:\DatabaseMoveTests\ Destination \TravelingDB_DataFile2.ndf' ),
( FILENAME = N'C:\DatabaseMoveTests\ Destination \TravelingDB_LogFile2.ldf' )
FOR ATTACH
GO
 
SQL Server Database Move with Attach Detach
 
If you somehow delete or lose the log files…intentionally or accidentally…you could still attach the data files…however you would need to use the ATTACH_REBUILD_LOG statement in CREATE DATABASE:
 
USE [master]
GO
CREATE DATABASE [TravelingDB] ON
( FILENAME = N'C:\DatabaseMoveTests\Source\TravelingDB.mdf' ),
( FILENAME = N'C:\DatabaseMoveTests\Destination\TravelingDB_DataFile2.ndf' )
FOR ATTACH_REBUILD_LOG
GO

 
If you had multiple log files before DETACH, you’d only end up with one log file after using ATTACH_REBUILD_LOG.
 

 

Move SQL Server Databases, move master, move tempdb, move model, move msdb, move AdventureWorks, move mssqlsystemresource, move system databases,  Move ReportServer, SQL 2005, 2008, 2008R2, 2012, 2014

ALTER DATABASE method:
 
You run Alter Database once for each file you would like to move:
 
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'full path and file name' );
 
Example:
 
ALTER DATABASE TravelingDB MODIFY FILE ( NAME = TravelingDB_DataFile2 , FILENAME = ‘C:\DatabaseMoveTests\Destination\TravelingDB_DataFile2.ndf’ );
 
Afterwards, you need to either stop the SQL Server service, or put the database offline (I recommend the latter)...and then move the files manually to the new destination...and finally restart the service/database.
 
If you make any mistake in the path of the database file name…your database could be corrupted as soon as it will be restarted…I can actually recreate that problem…so lesson learned: backup your databases fully before attempting a move.
 
Microsoft documentations tell you to stop the SQL Server service once you are done with all the ALTER DATABASE commands, then copy the files over, and restart the service. I FOUND THAT UNREASONABLE FOR PRODUCTION SERVERS…and poking around SSMS’s interface…I saw Online/Offline feature…and I tried it and it worked…it successfully restarts a database, thus not requiring you to restart the entire SQL Server service.
Use SSMS's Offline and Online features to restart a database
 
This method works well with File Groups. If you have multiple File Groups prior to the move…they will all appear intact afterwards.
 

 

Move SQL Server Databases, move master, move tempdb, move model, move msdb, move AdventureWorks, move mssqlsystemresource, move system databases,  Move ReportServer, SQL 2005, 2008, 2008R2, 2012, 2014

Moving System Databases:
 
model: use ALTER DATABASE...then Stop SQL Service...move the files manually....then start the service. Do not use the Offline/Online trick on model.
 
msdb: use ALTER DATABASE...then Stop SQL Service...move the files manually....then start the service. Do not use the Offline/Online trick on msdb.
 
tempdb: Use ALTER DATABASE method only, and also you must restart the SQL Server service afterwards for changes to go through (and not my offline/online trick)….do NOT manually move the files…SQL Server will create the new files in the new destination, and since tempdb is nothing but a temporary storage area and a white board…you should go ahead and delete the old files afterwards, it's safe.
 
If you have multiple tempdb data files (and you should!) then it’s just a question of running multiple ALTER DATABASE commands back to back:
 
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME =
'C:\DatabaseMoveTests\Destination\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdb_datafile2, FILENAME
= 'C:\DatabaseMoveTests\Destination\tempdb_datafile2.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdb_datafile3, FILENAME
= 'C:\DatabaseMoveTests\Destination\tempdb_datafile3.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME
= 'C:\DatabaseMoveTests\Destination\templog.ldf');
GO

 
Afterwards:

  • Restart SQL Server Service
  • Delete the old tempdb files

 

mssqlsystemresource: This is The Resource Database. It is Read Only, and it contains a database of system object types.
 
You cannot not move or meddle with this database as of SQL Server 2008. It has since been relocated to the BINN folder.
 
However, in SQL Server 2005 when Microsoft originally introduced this database, they had placed this database in the DATA folder just besides master, and it needed to be moved along with master.
 
So again, you could only move this database in SQL Server 2005. And if you move SQL Server 2005’s master database then you must move this database alongside master as well.
 
Since it must be done at the same time as master, then I will provide the step-by-step procedures under the ‘master’ section.

 

Move SQL Server Databases, move master, move tempdb, move model, move msdb, move AdventureWorks, move mssqlsystemresource, move system databases,  Move ReportServer, SQL 2005, 2008, 2008R2, 2012, 2014

master:

  • In the Configuration Tools in Start menu, click on SQL Server Configuration Manager…SQL Server Services...right-click on the instance of your choice, choose Properties…and go to Start-up Parameters section:
     
  • -dC:\DestinationPath\master.mdf;
    -lC:\DestinationPath\mastlog.ldf
     
    Basically you are inputting the new paths for these two files.
     
  • Save and Close all windows.
     
  • Stop SQL Server Service
     
  • Move master.mdf and mastlog.ldf files to the new location.
     
  • SQL Server 2005 ONLY: Start the service using either –m (single user mode) or –f (minimal configuration) start up options.
     
    NET START MSSQLSERVER /f /T3608
    Or
    NET START MSSQL$instancename /f /T3608
     
    Use –T3608 trace flag, to skip the recovery of all databases other than master database, and to make sure nothing is using the resource database!
     
  • SQL Server 2005 ONLY:
    ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= ‘new path where master data file resides\mssqlsystemresource.mdf’);
     
    ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= ‘new path where master log file resides\mssqlsystemresource.ldf’);
     
  • SQL Server 2005 ONLY:
    ALTER DATABASE mssqlsystemresource SET READ_ONLY;
     
  • SQL Server 2005 ONLY:
    Move the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the new location.
     
  • SQL Server 2005 ONLY:
    Stop SQL Server Service.
     
  • Start the SQL Service normally, without the above options or flags.

 

Move SQL Server Databases, move master, move tempdb, move model, move msdb, move AdventureWorks, move mssqlsystemresource, move system databases,  Move ReportServer, SQL 2005, 2008, 2008R2, 2012, 2014

 

Written by Ramin Haghighat