copy a SQL Server 2008 database and rename it

Question

I have a SQL Server 2008 database that I want to copy and create a new database (with the a different name) on the server with. I am not concerned with maintaining data, the new database can be created with no data for starters. What I am looking to do is the following:

• Create the new database maintaining structure of old database
• Set the name of the new database
• Change all varchar and char datatypes to nvarchar and nchar
• Change all text datatypes to nvarchar(MAX)

As an aside, I have 2 more questions which are not part of my task but would like to consider the following:

• How can I upgrade the sql server database to sql server 2012
• Is there any preparatory work I need to carry out on the database to ensure I can easily upgrade it?

Answer 1

Source for the script that copies a database.

USE master;

DECLARE
    @SourceDatabaseName AS SYSNAME = '<SourceDB>', 
    @TargetDatabaseName AS SYSNAME = '<TargetDB>'



-- ============================================
-- Define path where backup will be saved
-- ============================================
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @SourceDatabaseName)
    RAISERROR ('Variable @SourceDatabaseName is not set correctly !', 20, 1) WITH LOG       

DECLARE @SourceBackupFilePath varchar(2000)
SELECT @SourceBackupFilePath = BMF.physical_device_name
FROM
    msdb.dbo.backupset B
    JOIN msdb.dbo.backupmediafamily BMF ON B.media_set_id = BMF.media_set_id
WHERE B.database_name = @SourceDatabaseName
ORDER BY B.backup_finish_date DESC

SET @SourceBackupFilePath = REPLACE(@SourceBackupFilePath, '.bak', '_clone.bak')

IF @SourceBackupFilePath IS NULL
        RAISERROR ('Could not determine file path for backup file!', 16, 1) WITH LOG



-- ============================================
-- Backup source database
-- ============================================
DECLARE @Sql NVARCHAR(MAX) 
SET @Sql = 'BACKUP DATABASE @SourceDatabaseName TO DISK = ''@SourceBackupFilePath'''
SET @Sql = REPLACE(@Sql, '@SourceDatabaseName', @SourceDatabaseName)
SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
SELECT 'Performing backup...', @Sql as ExecutedSql
EXEC (@Sql)



-- ============================================
-- Automatically compose database files (.mdf and .ldf) paths
-- ============================================
DECLARE
          @LogicalDataFileName as NVARCHAR(MAX)
        , @LogicalLogFileName as NVARCHAR(MAX)
        , @TargetDataFilePath as NVARCHAR(MAX)
        , @TargetLogFilePath as NVARCHAR(MAX)

SELECT
        @LogicalDataFileName = name,
        @TargetDataFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.mdf'
FROM sys.master_files
WHERE
        database_id = DB_ID(@SourceDatabaseName)        
        AND type = 0            -- datafile file

SELECT
        @LogicalLogFileName = name,
        @TargetLogFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.ldf'
FROM sys.master_files
WHERE
        database_id = DB_ID(@SourceDatabaseName)        
        AND type = 1            -- log file     

SELECT  
    @LogicalDataFileName as LogicalDataFileName,
    @LogicalLogFileName as LogicalLogFileName,
    @TargetDataFilePath as TargetDataFilePath,
    @TargetLogFilePath as TargetLogFilePath                

IF @TargetDataFilePath IS NULL OR @TargetLogFilePath IS NULL
    RAISERROR ('Could not determine target paths!', 16, 1) WITH LOG



-- ============================================
-- Restore target database
-- ============================================
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @TargetDatabaseName)
    RAISERROR ('A database with the same name already exists!', 20, 1) WITH LOG        

SET @Sql = 'RESTORE DATABASE @TargetDatabaseName
FROM DISK = ''@SourceBackupFilePath'' 
WITH MOVE ''@LogicalDataFileName'' TO ''@TargetDataFilePath'',
MOVE ''@LogicalLogFileName'' TO ''@TargetLogFilePath''' 
SET @Sql = REPLACE(@Sql, '@TargetDatabaseName', @TargetDatabaseName)
SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
SET @Sql = REPLACE(@Sql, '@LogicalDataFileName', @LogicalDataFileName)
SET @Sql = REPLACE(@Sql, '@TargetDataFilePath', @TargetDataFilePath)
SET @Sql = REPLACE(@Sql, '@LogicalLogFileName', @LogicalLogFileName)
SET @Sql = REPLACE(@Sql, '@TargetLogFilePath', @TargetLogFilePath)
SELECT 'Restoring...', @Sql as ExecutedSql
EXEC (@Sql)