Database Files – Part 2

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.

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.

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.

The second step is to bring the original database back on-line and in a multi-user state.

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.

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.

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.

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.

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.

Related posts

One Thought to “Database Files – Part 2”

  1. 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

Leave a Comment