6

I am following this tutorial from AWS to export a SQL Server database that currently sits on my laptop to Amazon Web Services RDS. In particular I am trying the Generate and Publish script wizard followed by bulk copy.

The script that gets generated starts like this:

CREATE DATABASE [Test]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Datapump_Test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Datapump_Test.mdf' , SIZE = 1127424KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Datapump_Test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Datapump_Test_log.ldf' , SIZE = 2160960KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

So my question is what are those paths for (i.e. the C:\..... lines) and are they pointing to files on my laptop? And if so, should I change them when I run this script on the RDS DB? I find that if I leave them there it will error, but if I manually create the DB in SSMS and then comment out these lines and run the script, I still get many errors, but it does create all the table schemas, user and indices etc... so is this the correct approach? If it is, it seems strange that the link I posted above doesn't mention this. This makes me think I've done something wrong. Does anyone have any insight on the matter?

3
+50

The paths in the generated script are the locations where the data and log files should be on your laptop.

If you copy paste the script to amazon AWS that will be the location where the files are created when you create the database.

If that path doesn't exist on the Amazon instance you will get errors.

You could comment out the lines and then the files will be created in the default locations for the instance, with default sizes and a filename generated from the database name.

CREATE DATABASE [Test]
 CONTAINMENT = NONE
 /* ON  PRIMARY 
( NAME = N'Datapump_Test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Datapump_Test.mdf' , SIZE = 1127424KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Datapump_Test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Datapump_Test_log.ldf' , SIZE = 2160960KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)*/
GO

As you indicate some of the other errors are related to things like roles and permissions there is an advanced settings dialog where you can change some options, and you can also select which objects to script (such as users)

See this link How to: Generate a Script (SQL Server Management Studio) for an overview of the wizard.

For example if you select "select specific options" in this page you can deselect "Users"

enter image description here

On the next page you can click an advanced button

enter image description here

Which opens a dialog with a lot more options, allowing to script things like permissions and triggers and stuff like that.

enter image description here

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.