Copy a Database

[Cornelia]

Hi,

I am abit frustrated, I need to copy the entire Database in MS-SQL, by a stored procedure. The name of the database is given as the first parameter, and the name of the new (copied) database can be provided as the second parameter, or it can be left as the default.
I call my own procedures to copy the necessary tables, but the Foreign Keys and Primary keys are not copied.

How do I go about creating an exact copy of the DB, given the DBName and DBNewName as parameters?

Is it possible to execute a package with parameters through a stored procedure?

What my sp loooks like...so far



-- ================================================== =========================================
-- Copy a Database structure and give it a New Database Name
-- Note: The database is given a default name if the new name has not been specified
-- Parameters: 1) Database name, 2) New Database
-- Author: Cornelia von Krosigk
-- Date Updated: 06.10.2003
-- Progress: Not Complete
-- ================================================== =========================================
CREATE PROCEDURE afsp_CopyDatabase @dbName nvarchar(128), --FROM
@dbNewName nvarchar(128) = 'x', --TO
@debug bit = 0
AS
IF LOWER(@dbNewName) = 'x'
SET @dbNewName = @dbName + '_COPY'
DECLARE @sql nvarchar(4000)
SET @sql =
'
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '''+ @dbName +''')
BEGIN
-- ==============================================
-- Creation of a New Database
-- ==============================================
EXEC dbo.afsp_CreateDatabase '+ @dbNewName + '
END
'
IF @debug = 1 PRINT @sql
EXEC sp_executesql @sql

SET @sql =
'
DECLARE @table_name nvarchar(128)
-- ==============================================
-- Select a List of all the tables in the Origional Database
-- ==============================================
DECLARE get_tablelist_cursor CURSOR FOR
SELECT name from APPLE.dbo.sysobjects WHERE name LIKE ''ps_%''

OPEN get_tablelist_cursor
FETCH NEXT FROM get_tablelist_cursor
INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @table_name
-- ==============================================
-- Settings on Creation of a New Table in the New Database
-- ==============================================
/** Drop the table in the Database if it exists**/
EXEC afsp_CheckTableExists '+@dbNewName+', @table_name, 0

/** Copy the project records from the table in the database to the copied table in the new Database **/
EXEC afsp_CopyTable '+@dbName+', '+@dbNewName+', @table_name


FETCH NEXT FROM get_tablelist_cursor
INTO @table_name
END
CLOSE get_tablelist_cursor
DEALLOCATE get_tablelist_cursor
'
IF @debug = 1 PRINT @sql
EXEC sp_executesql @sql
GO

[skhanal]

Do you need to copy data too?

If yes, then easier method would be to backup and restore the database as a new database.

[MAK]

Copying databases

1. backup and restore databases as new database.
2. DTS to copy databases



DTS can be executed using XP_CMDShell and dtsrun. Parameters can be passed to DTS using Global variable.

[Cornelia]

A really appreciate the quick response! Thanx

Yes, Data is involved, and Im trying the Backup, Restore method.

I have no experience with the DTS, but I will experiment using this method too.