The easiest way of duplicating a database in SQL Server is by using backup and restore. Unfortunately this feature is not available in Amazon RDS because there is no file system access.

This is a walk through for copying a database in RDS:

Open SQL Server Management Studio

Open SQL Server Management Studio and connect to the database server. All steps are also available in Express Edition.

Generate script

First you need to generate a script for all the objects in the database, excluding the data. You do this by right clicking on the source database on Tasks > Generate Scripts…

Select the objects you want to duplicate in your database.

If you don’t have any DDL triggers, don’t select it. Amazon adds a trigger to each database to prevent creating a backup called rds_deny_backups_trigger. Otherwise make sure this trigger is excluded from the generated script.

Also don’t select the master user created when initializing the RDS database server. Every new database will already have this user.

Click Next.

Select Save to new query window. You could opt for saving it to file, but in my case I only use the script once.

Click on Advanced.

• Set Script USE DATABASE to False
• Set Script Indexes to True
• Set Script Triggers to True

Click Next and Next.

Wait until the script is created.

Create new database

Now create the new empty database by right clicking on Databases. Select New Database…

Enter the name of the database and press OK.

Change the connection of the generated script window to the new database and run the script.

Disable triggers and foreign key constraints

Run the following script on the new database to disable triggers and foreign key constraints:

--Disable foreign keys on all tables
DECLARE @table_name SYSNAME;
DECLARE @schema_name SYSNAME;
DECLARE @cmd NVARCHAR(MAX);
DECLARE table_cursor CURSOR FOR SELECT sys.tables.name,sys.schemas.name as schemaName FROM sys.tables inner join sys.schemas on sys.tables.schema_id=sys.schemas.schema_id;
OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @table_name,@schema_name;
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @cmd = 'ALTER TABLE '+QUOTENAME(@schema_name)+'.'+QUOTENAME(@table_name)+' NOCHECK CONSTRAINT ALL';
EXEC (@cmd);
FETCH NEXT FROM table_cursor INTO @table_name,@schema_name;
END
CLOSE table_cursor;
DEALLOCATE table_cursor;
GO 

--Disable triggers on all tables
DECLARE @enable BIT = 0;
DECLARE @trigger SYSNAME;
DECLARE @table SYSNAME;
DECLARE @cmd NVARCHAR(MAX);
DECLARE trigger_cursor CURSOR FOR SELECT trigger_object.name trigger_name,
 table_object.name table_name
FROM sysobjects trigger_object
JOIN sysobjects table_object ON trigger_object.parent_obj = table_object.id
WHERE trigger_object.type = 'TR';

OPEN trigger_cursor;
FETCH NEXT FROM trigger_cursor INTO @trigger, @table;

WHILE @@FETCH_STATUS = 0 BEGIN
 IF @enable = 1
 SET @cmd = 'ENABLE ';
 ELSE
 SET @cmd = 'DISABLE ';

 SET @cmd = @cmd + ' TRIGGER dbo.'+QUOTENAME(@trigger)+' ON dbo.'+QUOTENAME(@table)+' ';
 EXEC (@cmd);
 FETCH NEXT FROM trigger_cursor INTO @trigger, @table;
END

CLOSE trigger_cursor;
DEALLOCATE trigger_cursor;

GO

Copy all data

Next step is to copy all the data from the source database to the new database. For this you have to use the SQL Server Import and Export Wizard. You open this by right clicking on the source database and click Tasks > Export…

First enter the connection details for the source database.

Then enter the connection details for the target database.

Highlight all tables by holding the Shift key and clicking on the first and last table. Click on one of the check boxes left to the tables. This will select all tables. Make sure not to select any views because there are no destination tables to insert into.

Now keep the highlight and click on Edit Mappings…

Check Enable identity insert. Close the dialog by clicking OK.

Click Next.

Choose Run immidiately,

Click Next, Next and Finish.

Wait until the import completes.

Enable triggers and foreign key constraints

Enable all triggers and foreign key constraints on the target database by running this script:

--Enable foreign keys on all tables
DECLARE @table_name SYSNAME;
DECLARE @schema_name SYSNAME;
DECLARE @cmd NVARCHAR(MAX);
DECLARE table_cursor CURSOR FOR SELECT sys.tables.name,sys.schemas.name as schemaName FROM sys.tables inner join sys.schemas on sys.tables.schema_id=sys.schemas.schema_id;
OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @table_name,@schema_name;
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @cmd = 'ALTER TABLE '+QUOTENAME(@schema_name)+'.'+QUOTENAME(@table_name)+' CHECK CONSTRAINT ALL';
EXEC (@cmd);
FETCH NEXT FROM table_cursor INTO @table_name,@schema_name;
END
CLOSE table_cursor;
DEALLOCATE table_cursor;
GO
--Enable triggers on all tables
DECLARE @enable BIT = 1;
DECLARE @trigger SYSNAME;
DECLARE @table SYSNAME;
DECLARE @cmd NVARCHAR(MAX);
DECLARE trigger_cursor CURSOR FOR SELECT trigger_object.name trigger_name,
 table_object.name table_name
FROM sysobjects trigger_object
JOIN sysobjects table_object ON trigger_object.parent_obj = table_object.id
WHERE trigger_object.type = 'TR';

OPEN trigger_cursor;
FETCH NEXT FROM trigger_cursor INTO @trigger, @table;

WHILE @@FETCH_STATUS = 0 BEGIN
 IF @enable = 1
 SET @cmd = 'ENABLE ';
 ELSE
 SET @cmd = 'DISABLE ';

 SET @cmd = @cmd + ' TRIGGER dbo.'+QUOTENAME(@trigger)+' ON dbo.'+QUOTENAME(@table)+' ';
 EXEC (@cmd);
 FETCH NEXT FROM trigger_cursor INTO @trigger, @table;
END

CLOSE trigger_cursor;
DEALLOCATE trigger_cursor;
GO

Set permissions

Only the users where imported, but not its roles and permissions. These have to be added manually.

Update

Dan commented that the SQL scripts didn’t support schemas. He provided me with an updated version that takes schemas into account. I updated the scripts in this post. Thanks Dan for this great contribution! An update for triggers will follow soon.