{"id":1753,"date":"2012-02-17T15:33:45","date_gmt":"2012-02-17T15:33:45","guid":{"rendered":"http:\/\/craftydba.com\/?p=1753"},"modified":"2017-10-12T01:08:11","modified_gmt":"2017-10-12T01:08:11","slug":"database-files-part-2","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1753","title":{"rendered":"Database Files – Part 2"},"content":{"rendered":"

The most basic Microsoft SQL Server database is composed of two operating system files<\/a>: 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.<\/p>\n

The key CREATE DATABASE<\/a>, CREATE TABLE and CREATE INDEX statements are below. The complete script which catalogs ANIMAL names is enclosed<\/a> 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.<\/p>\n

\r\n-- Use the database\r\nUSE [WILDLIFE]\r\nGO\r\n\r\n-- Define the db with default groups\r\nCREATE DATABASE [WILDLIFE]\r\nON PRIMARY\r\n( NAME = 'WildLifeData',\r\n    FILENAME = 'C:\\MSSQL\\DATA\\Wild_Life_Data.mdf',\r\n    SIZE = 50MB,\r\n    MAXSIZE = 200MB,\r\n    FILEGROWTH = 5MB)\r\nLOG ON\r\n ( NAME = 'WildLifeLog',\r\n    FILENAME = 'C:\\MSSQL\\LOG\\Wild_Life_Log.ldf',\r\n    SIZE = 12MB,\r\n    MAXSIZE = 48MB,\r\n    FILEGROWTH = 1MB)\r\nGO\r\n\r\n-- Use the database\r\nUSE [WILDLIFE]\r\nGO\r\n\r\n-- Create the animals table\r\nCREATE TABLE ANIMALS\r\n(\r\nID INT NOT NULL IDENTITY (1, 1),\r\nNAME VARCHAR(200) NOT NULL,\r\nCATEGORY INT DEFAULT (0)\r\n) ON [PRIMARY]\r\nGO\r\n\r\n-- Add index on name (non-clustered)\r\nCREATE CLUSTERED INDEX IDX_ANIMAL_CATEGORY ON DBO.ANIMALS(CATEGORY)\r\nON [PRIMARY];\r\nGO\r\n<\/pre>\n<\/p>\n
As you can see below, the TABLE and INDEX are defined on the default filegroup, PRIMARY.<\/p>\n
\"\"<\/a><\/p>\n
\"\"<\/a><\/p>\n

\nEvery Database Administrator will come across a situation in which a database needs to be duplicated on the same sever or copied to another server.
\n<\/span><\/p>\n
How do you accomplish this task? I will be demostrating the different ways to successfully complete this task.<\/p>\n
Let’s make believe that your manager asks you to create a QA instance of the [WILDLIFE] database on the same server.<\/p>\n
The first step is kick off any users and put the database in a off-line state using the ALTER DATABASE<\/a> statement. This statement has many SET OPTIONS<\/a> 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.<\/p>\n
\r\n-- Kick off users, roll back current work\r\nALTER DATABASE [WILDLIFE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;\r\nGO\r\n\r\n-- Set the database to off-line\r\nALTER DATABASE [WILDLIFE] SET OFFLINE\r\nGO\r\n<\/pre>\n<\/p>\n
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<\/a> is enabled and the QA directory structure exists on the file system.<\/p>\n
\r\n-- Copy data file to QA region\r\nEXEC xp_cmdshell 'COPY C:\\MSSQL\\DATA\\Wild_Life_Data.mdf C:\\QA\\DATA\\Wild_Life_Data.mdf';\r\nGO\r\n\r\n-- Copy log file to QA region\r\nEXEC xp_cmdshell 'COPY C:\\MSSQL\\LOG\\Wild_Life_Log.ldf C:\\QA\\LOG\\Wild_Life_Log.ldf';\r\nGO\r\n<\/pre>\n<\/p>\n
The second step is to bring the original database back on-line and in a multi-user state.<\/p>\n
\r\n\r\n-- Bring database on-line\r\nALTER DATABASE [WILDLIFE] SET ONLINE\r\nGO\r\n\r\n-- Add users\r\nALTER DATABASE [WILDLIFE] SET MULTI_USER\r\nGO\r\n<\/pre>\n<\/p>\n
The third step is to attach the database using the options within the CREATE DATABASE<\/a> 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.<\/p>\n
\r\n\r\n-- Option 1 Attach & create a new database using existing files\r\nCREATE DATABASE [QA_WILDLIFE] ON\r\n(FILENAME = 'C:\\QA\\Data\\Wild_Life_Data.mdf'),\r\n(FILENAME = 'C:\\QA\\Log\\Wild_Life_Log.ldf')\r\nFOR ATTACH ;\r\n\r\n-- Option 2 - Attach & create a new database using just data file\r\nCREATE DATABASE [QA_WILDLIFE] ON\r\n(FILENAME = 'C:\\QA\\Data\\Wild_Life_Data.mdf')\r\nFOR ATTACH_REBUILD_LOG;\r\n\r\n<\/pre>\n<\/p>\n
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.<\/p>\n
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<\/a> with the MODIFY NAME clause.<\/p>\n
\r\n\r\n-- Allow people to finish work\r\nALTER DATABASE [QA_WILDLIFE] SET SINGLE_USER\r\nGO\r\n\r\n-- Option 1 - rename the database\r\nsp_renamedb 'QA_WILDLIFE', 'SIT2_WILDLIFE'\r\n\r\n-- Option 2 - rename the database\r\nALTER DATABASE QA_WILDLIFE\r\nMODIFY NAME = SIT2_WILDLIFE ;\r\n\r\n-- Allow people to access the database again\r\nALTER DATABASE [SIT2_WILDLIFE] SET MULTI_USER\r\nGO\r\n<\/pre>\n<\/p>\n
Another way to solve this problem is to detach (sp_detach_db<\/a>) and attach (sp_attach_db<\/a>) the database instead of rename (sp_renamedb<\/a>). 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.<\/p>\n
\r\n\r\n-- Kick off users, roll back current work\r\nALTER DATABASE [SIT2_WILDLIFE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;\r\nGO\r\n\r\n-- Set the database to off-line\r\nALTER DATABASE [SIT2_WILDLIFE] SET OFFLINE\r\nGO\r\n\r\n-- Full text indexes can be drop if not careful.\r\n-- Detach database\r\nsp_detach_db 'SIT2_WILDLIFE'\r\n\r\n-- Attach database\r\nEXEC sp_attach_db @dbname = N'SIT_WILDLIFE',\r\n    @filename1 = N'C:\\QA\\Data\\Wild_Life_Data.mdf',\r\n    @filename2 = N'C:\\QA\\Log\\Wild_Life_Log.ldf';\r\n\r\n-- Bring database on-line\r\nALTER DATABASE [SIT_WILDLIFE] SET ONLINE\r\nGO\r\n\r\n-- Allow people to access the database\r\nALTER DATABASE [SIT_WILDLIFE] SET MULTI_USER\r\nGO\r\n><\/pre>\n<\/p>\n
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.<\/p>\n
\r\n\r\n-- Allow people to finish work\r\nALTER DATABASE [SIT_WILDLIFE] SET SINGLE_USER\r\nGO\r\n\r\n-- A way to move datafiles of the database\r\nALTER DATABASE [SIT_WILDLIFE] SET OFFLINE\r\nGO\r\n\r\n-- Move data file to SIT region\r\nEXEC xp_cmdshell 'MOVE C:\\QA\\DATA\\Wild_Life_Data.mdf C:\\SIT\\DATA\\Wild_Life_Data.mdf';\r\nGO\r\n\r\n-- Move log file to SIT region\r\nEXEC xp_cmdshell 'MOVE C:\\QA\\LOG\\Wild_Life_Log.ldf C:\\SIT\\LOG\\Wild_Life_Log.ldf';\r\nGO\r\n\r\n-- Update system catalog for data file\r\nALTER DATABASE [SIT_WILDLIFE] MODIFY FILE\r\n   (NAME=WildLifeData,FILENAME='C:\\SIT\\DATA\\Wild_Life_Data.mdf')\r\nGO\r\n\r\n-- Update system catalog for log file\r\nALTER DATABASE [SIT_WILDLIFE] MODIFY FILE\r\n   (NAME=WildLifeLog,FILENAME='C:\\SIT\\LOG\\Wild_Life_Log.ldf')\r\nGO\r\n\r\n-- Bring database on-line\r\nALTER DATABASE [SIT_WILDLIFE] SET ONLINE\r\nGO\r\n\r\n-- Allow people to access the database\r\nALTER DATABASE [SIT_WILDLIFE] SET MULTI_USER\r\nGO\r\n<\/pre>\n<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[51,50,117,381,380,12,15,370,378,379,368,377,375,374,376,383,382,384,28,29,385,263],"class_list":["post-1753","post","type-post","status-publish","format-standard","hentry","category-db-admin","tag-alter-database","tag-create-database","tag-database-administrator","tag-for-attach","tag-for-attach_rebuild_log","tag-free-code","tag-john-f-miner-iii","tag-log-file","tag-modify-file","tag-modify-name","tag-primary-data-file","tag-set-multi_user","tag-set-offline","tag-set-online","tag-set-single_user","tag-sp_attach_db","tag-sp_detach_db","tag-sp_renamedb","tag-sql-server","tag-tsql","tag-with-rollback-immediate","tag-xp_cmdshell"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1753","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1753"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1753\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1753"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1753"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1753"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}