CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedures
Supra
Supra

Reputation: 1652

How to copy tables , functions and stored procedures from one database to other in same Instance

I want to copy all tables, functions and stored procedures from a database to another in the same sql server 2008 instance. I am able to generate a script out of the source database but unable to run it programmatically (from another procedure). I have searched a lot on this, but unable to find an answer yet.

For now, I am just copying the sql code generated into a query window and executing it manually.

Upvotes: 1

Views: 1532

Answers (1)

twoleggedhorse
twoleggedhorse

Reputation: 5048

You can script a backup/restore to do this, just restore to a different database name:

BACKUP DATABASE [DatabaseToCopy] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DatabaseToCopy.bak' WITH NOFORMAT, INIT, NAME = N'DatabaseToCopy-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM, CONTINUE_AFTER_ERROR go

RESTORE DATABASE [DatabaseToCreate] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DatabaseToCopy.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10 go

Just tweak the names and locations and create a sql server job to run it.

Upvotes: 1

Related Questions