Copy Database

[NoKeS]

HI,
How do I copy all tables and all procedures from a database to another, (all by code).
HELP ME PLEASE!!!

 

[tlbroadbent]

What do you mean, "all by code?" What kind of code - T-SQL, C, VB? Which version of SQL Server are you running? Are the databases on the same server? Do you also want views copied? Do you already have scripts for the database objects? Do you want to copy data as well as ojects or juest the schema?

Please be more specific. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[NoKeS]

SQL CODE.
The database is in the same server. I want to copy all items of one database to onother database, including data.
SQL Version = 8.0

 

[moonshadow]

Design a DTS package, save it on the server, and just get your program to execute it. That's the easiest way.

 

[NoKeS]

What program???? I have to do it all by code!! SQL CODE!!

 

[tlbroadbent]

If you can take the database offline, the quickest method of copying a database would be to detach the source datbase, copy its files to files for the new database and the attach the source and destination databases.

-----------------------------------------
Use master
go
if exists (Select * from sysdatabases Where name=N'addressbook')
exec sp_detach_db AddressBook
go
if exists (Select * from sysdatabases Where name=N'addresstest')
exec sp_detach_db AddressesTest
go
exec xp_cmdshell 'copy "C:\Program Files\Microsoft SQL Server\MSSQL\Data\AddressBook_Data.mdf" "C:\Program Files\Microsoft SQL Server\MSSQL\Data\AddressTest_Data.mdf"'
exec xp_cmdshell 'copy "C:\Program Files\Microsoft SQL Server\MSSQL\Data\AddressBook_Log.ldf" "C:\Program Files\Microsoft SQL Server\MSSQL\Data\AddressTest_Log.ldf"'
go
exec sp_attach_db
@dbname = AddressBook,
@filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\AddressBook_Data.MDF',
@filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\AddressBook_Log.LDF'
Go
exec sp_attach_db
@dbname = AddressTest,
@filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\AddressTest_Data.MDF',
@filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\AddressTest_Log.LDF'
-----------------------------------------

Alternatively, you can backup the source database and restore to the destination database.

BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO

Both methods require exlusive use of the database. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.