Copy the schema of a DB to my laptop

I need to take a copy of a database schema and load it onto my laptop but I don't know how to do it or what it is even called to research it.

I am not allowed to take the data from the DB home just the layout of the DB.  For example, I only need the tables.  There are a lot of them so I'd rather not go into each one and create a script.  

Can I do this with a simple command?
huerita37Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael DyerSenior Systems Support AnalystCommented:
This can be accomplished by running the task to generate a script that will build a new copy of the database without data

in SQL Server management studio right-click on the name of the database and select tasks, generate scripts

select your database and check the box for "script all objects in the selected database"

in output, select the option for  "script to new query window" and run this.  This will create a SQL script in a new query window that you can save to a file and copy to your laptop

On your laptop, you must have a copy of SQL or SQL Express.   Run this saved query in the SQL server management studio and it will create a copy of the database.
0
huerita37Author Commented:
I was able to complete what you had suggested above to get the script but when I run it on my laptop I am getting an error because the the .MDF file.  I don't have it.

Here is the script for that part:

CREATE DATABASE [ADR_LIVE]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'data01', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ADR_LIVE.MDF' , SIZE = 2626880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [ftfg_CMSFTICatalog] 
( NAME = N'ftrow_CMSFTICatalog', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ADR_LIVE_1.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [ftfg_ExpertTimeFTICatalog] 
( NAME = N'ftrow_ExpertTimeFTICatalog', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ADR_LIVE_2.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = N'log01', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ADR_LIVE_3.LDF' , SIZE = 153934272KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO

Open in new window


How can I work around this?  Do I need that file?
0
Michael DyerSenior Systems Support AnalystCommented:
There are two areas in this script that may need adjusting.

First the path to the DATA folder may be different on your laptop.  In my case, I had to change the path in the script from C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA to C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA

Secondly, you need to be sure you have enough free space for the database.  I adjusted the file sizes to the minimum to take up less space:

Here is the revised version of that script:


CREATE DATABASE [ADR_LIVE]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'data01', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\ADR_LIVE.MDF' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
 FILEGROUP [ftfg_CMSFTICatalog]
( NAME = N'ftrow_CMSFTICatalog', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\ADR_LIVE_1.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
 FILEGROUP [ftfg_ExpertTimeFTICatalog]
( NAME = N'ftrow_ExpertTimeFTICatalog', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\ADR_LIVE_2.ndf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON
( NAME = N'log01', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\ADR_LIVE_3.LDF' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

huerita37Author Commented:
I am getting stuck on creating a user.  This is what I have:

GO
/****** Object:  User [Jacque7450\Jacque]    Script Date: 10/30/2015 10:33:41 AM ******/
CREATE USER [Jacque7450\Jacque] FOR LOGIN [Jacque7450\Jacque] WITH DEFAULT_SCHEMA=[dbo]
GO

/****** Object:  DatabaseRole [DBR_INFOR]    Script Date: 10/30/2015 10:33:41 AM ******/
CREATE ROLE [DBR_INFOR]
GO
/****** Object:  DatabaseRole [DBR_CMSADM]    Script Date: 10/30/2015 10:33:41 AM ******/
CREATE ROLE [DBR_CMSADM]
GO
ALTER ROLE [DBR_CMSADM] ADD MEMBER [cmsadm]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [cmsadm]
GO
ALTER ROLE [DBR_CMSADM] ADD MEMBER [CMSADERANT.EXPERT]
GO

Open in new window


But I am getting the following error:
Msg 15063, Level 16, State 1, Line 2
The login already has an account under a different user name.
Msg 15151, Level 16, State 1, Line 1
Cannot add the principal 'cmsadm', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 1
Cannot add the principal 'cmsadm', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 1
Cannot add the principal 'CMSADERANT.EXPERT', because it does not exist or you do not have permission.

Open in new window


What am I doing wrong?  Am I not setting up the user correctly?  I have never done this before.
0
Michael DyerSenior Systems Support AnalystCommented:
if you are just interested in working with the schema of the database, you can actually skip this whole section and then manually create a user for yourself and assign it to that database as a DB owner with full rights.  You can do this right in the SQL management studio program.  Assuming you already have a user that you login with, you just have to go to the new database and under security add that user and give it DB owner rights
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
huerita37Author Commented:
I still haven't finished this project but I am unable to continue at this time.  I am giving all points and an 'A' because the advice was good.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.