The most basic Microsoft SQL Server database is composed of two operating system files: a primary data file and a transaction log file. If possible, the data and log files should be on different physical disks. Today, I am going to be re-creating the [WILDLIFE] database using this simple design in preparation for talking about MOVING and COPYING database files.
The key CREATE DATABASE, CREATE TABLE and CREATE INDEX statements are below. The complete script which catalogs ANIMAL names is enclosed as well as today’s new examples. If you have correctly installed the database, the SELECT statement searching for the key word ‘fly’ should return 8 rows.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
-- Use the database USE [WILDLIFE] GO -- Define the db with default groups CREATE DATABASE [WILDLIFE] ON PRIMARY ( NAME = 'WildLifeData', FILENAME = 'C:\MSSQL\DATA\Wild_Life_Data.mdf', SIZE = 50MB, MAXSIZE = 200MB, FILEGROWTH = 5MB) LOG ON ( NAME = 'WildLifeLog', FILENAME = 'C:\MSSQL\LOG\Wild_Life_Log.ldf', SIZE = 12MB, MAXSIZE = 48MB, FILEGROWTH = 1MB) GO -- Use the database USE [WILDLIFE] GO -- Create the animals table CREATE TABLE ANIMALS ( ID INT NOT NULL IDENTITY (1, 1), NAME VARCHAR(200) NOT NULL, CATEGORY INT DEFAULT (0) ) ON [PRIMARY] GO -- Add index on name (non-clustered) CREATE CLUSTERED INDEX IDX_ANIMAL_CATEGORY ON DBO.ANIMALS(CATEGORY) ON [PRIMARY]; GO |
As you can see below, the TABLE and INDEX are defined on the default filegroup, PRIMARY.
Every Database Administrator will come across a situation in which a database needs to be duplicated on the same sever or copied to another server.
How do you accomplish this task? I will be demostrating the different ways to successfully complete this task.
Let’s make believe that your manager asks you to create a QA instance of the [WILDLIFE] database on the same server.
The first step is kick off any users and put the database in a off-line state using the ALTER DATABASE statement. This statement has many SET OPTIONS that are used in the examples below. You should be nice to your users and warn ahead of time via email of the maintenance window.
|
1 2 3 4 5 6 7 |
-- Kick off users, roll back current work ALTER DATABASE [WILDLIFE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO -- Set the database to off-line ALTER DATABASE [WILDLIFE] SET OFFLINE GO |
The SQL Server engine is no longer has a lock on the files. Therefore, a simple copy command can be used to duplicate the database. The below statement assumes the xp_cmdshell is enabled and the QA directory structure exists on the file system.
|
1 2 3 4 5 6 7 |
-- Copy data file to QA region EXEC xp_cmdshell 'COPY C:\MSSQL\DATA\Wild_Life_Data.mdf C:\QA\DATA\Wild_Life_Data.mdf'; GO -- Copy log file to QA region EXEC xp_cmdshell 'COPY C:\MSSQL\LOG\Wild_Life_Log.ldf C:\QA\LOG\Wild_Life_Log.ldf'; GO |
The second step is to bring the original database back on-line and in a multi-user state.
|
1 2 3 4 5 6 7 |
-- Bring database on-line ALTER DATABASE [WILDLIFE] SET ONLINE GO -- Add users ALTER DATABASE [WILDLIFE] SET MULTI_USER GO |
The third step is to attach the database using the options within the CREATE DATABASE statement. Before executing these statements, make sure the SQL Server engine account has access to the files. I ran into problems with the examples in which I could not modify the original files. After manually changing the sercurity permissions, these problems went away.
|
1 2 3 4 5 6 7 8 9 10 |
-- Option 1 Attach & create a new database using existing files CREATE DATABASE [QA_WILDLIFE] ON (FILENAME = 'C:\QA\Data\Wild_Life_Data.mdf'), (FILENAME = 'C:\QA\Log\Wild_Life_Log.ldf') FOR ATTACH ; -- Option 2 - Attach & create a new database using just data file CREATE DATABASE [QA_WILDLIFE] ON (FILENAME = 'C:\QA\Data\Wild_Life_Data.mdf') FOR ATTACH_REBUILD_LOG; |
After you have gone thru this lengthy task, your manager stops by your desk and tells you that he wanted a SIT environment. The quickest way to do this is to leave the files in the QA directory and rename the database.
The system stored procedure called sp_renamedb can be used to change the database name. Another way to do this is to use the ALTER DATABASE with the MODIFY NAME clause.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Allow people to finish work ALTER DATABASE [QA_WILDLIFE] SET SINGLE_USER GO -- Option 1 - rename the database sp_renamedb 'QA_WILDLIFE', 'SIT2_WILDLIFE' -- Option 2 - rename the database ALTER DATABASE QA_WILDLIFE MODIFY NAME = SIT2_WILDLIFE ; -- Allow people to access the database again ALTER DATABASE [SIT2_WILDLIFE] SET MULTI_USER GO |
Another way to solve this problem is to detach (sp_detach_db) and attach (sp_attach_db) the database instead of rename (sp_renamedb). Be carefull with this command since it can drop external objects such as full text indexes. Please see the MSDN articles for a full explanation of the command. All three commands are slated to be discontinued in the future.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-- Kick off users, roll back current work ALTER DATABASE [SIT2_WILDLIFE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO -- Set the database to off-line ALTER DATABASE [SIT2_WILDLIFE] SET OFFLINE GO -- Full text indexes can be drop if not careful. -- Detach database sp_detach_db 'SIT2_WILDLIFE' -- Attach database EXEC sp_attach_db @dbname = N'SIT_WILDLIFE', @filename1 = N'C:\QA\Data\Wild_Life_Data.mdf', @filename2 = N'C:\QA\Log\Wild_Life_Log.ldf'; -- Bring database on-line ALTER DATABASE [SIT_WILDLIFE] SET ONLINE GO -- Allow people to access the database ALTER DATABASE [SIT_WILDLIFE] SET MULTI_USER GO > |
You tell your manager manager the clever idea you had about renaming the files; However, He now informs you that a physically MOVE of the database files from the QA directory to the SIT was to free up disk space. The new task is to move the files and then change the system catalog to point to the correct location.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
-- Allow people to finish work ALTER DATABASE [SIT_WILDLIFE] SET SINGLE_USER GO -- A way to move datafiles of the database ALTER DATABASE [SIT_WILDLIFE] SET OFFLINE GO -- Move data file to SIT region EXEC xp_cmdshell 'MOVE C:\QA\DATA\Wild_Life_Data.mdf C:\SIT\DATA\Wild_Life_Data.mdf'; GO -- Move log file to SIT region EXEC xp_cmdshell 'MOVE C:\QA\LOG\Wild_Life_Log.ldf C:\SIT\LOG\Wild_Life_Log.ldf'; GO -- Update system catalog for data file ALTER DATABASE [SIT_WILDLIFE] MODIFY FILE (NAME=WildLifeData,FILENAME='C:\SIT\DATA\Wild_Life_Data.mdf') GO -- Update system catalog for log file ALTER DATABASE [SIT_WILDLIFE] MODIFY FILE (NAME=WildLifeLog,FILENAME='C:\SIT\LOG\Wild_Life_Log.ldf') GO -- Bring database on-line ALTER DATABASE [SIT_WILDLIFE] SET ONLINE GO -- Allow people to access the database ALTER DATABASE [SIT_WILDLIFE] SET MULTI_USER GO |
It is very important to remember that LOGINS do not move from server to server. Therefore, if you are moving a database from one physical server to another or from one instance to another, you must recreate the logins on the target location. In short, SQL Server gives you many ways to COPY or MOVE operating system files that make up a database.
John helped me today with a real GOTCHA.
When setting up SQL Server 2012 on my work laptop (a few months back), I set up the instance with ‘NT Service’ as the account name. All worked well with no issues until today when I backed up one database to ‘port’ to another laptop. The plot now thickens!
In attempting to restore the back-up on the destination laptop, I obtained a ‘rights violation’. The error message was clear enough, what got me was how to resolve the issue.
John gave me a few suggestions, but the path of least resistance was to detach the database from the source (as both laptops were mine) and to port the mdf and ldf files over to the destination and to restore. This WORKED. Thanks John.
While not the best way to go about doing it I shall follow up with the other options that John gave me to ensure that the next time that this happens, I know what to do.
Regards Steve Simon