Copy the schema of a DB to my laptop

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.

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

Select allOpen in new window

How can I work around this?  Do I need that file?

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

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

Select allOpen 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.

Select allOpen in new window

What am I doing wrong?  Am I not setting up the user correctly?  I have never done this before.

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.