Copying an entire database in SQL Server 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Copying an entire database in SQL Server 2005

Author

Topic

JChi
Starting Member

4 Posts

Posted - 2009-06-13 : 04:23:06

Hi,

---Jchi

JChi
Starting Member

4 Posts

Posted - 2009-06-13 : 04:29:18

Hello,

I am new to SQL server. Can someone please let me know the query to copy the entire database through an SQL query; without the use of any backup file -- as we normally replicate a table something like
create table as select......
Actually, I need to invoke that query through a script.
My requirement is like creating a database, replicating it and further comparing the table with its replica. Completely through a Python script...

Thanks in advance....

---Jchi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-14 : 01:30:35

you can generate scripts for db from sql management studio as follows

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-06-14 : 11:40:02

You can script Everything and even data if you have installed SQL Server 2008 in your system.

JChi
Starting Member

4 Posts

Posted - 2009-06-15 : 01:35:48

Hey Visakh16,

Thanks for the suggestion. I have used this earlier. This copies the schema alone. I tried creating a new database by modifying the name of the existing database in the script.
By executing this script, it creates a replica of the schema but the data is not being copied to the database name speciified in the script. Can you please me out?
Best Regards.......

quote:
Originally posted by visakh16

you can generate scripts for db from sql management studio as follows

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

---Jchi

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-15 : 01:41:07

then use BACKUP DATABASE and RESTORE DATABASE command

KH
[spoiler]Time is always against us[/spoiler]

JChi
Starting Member

4 Posts

Posted - 2009-06-15 : 07:24:48

Kthan,

After a database copy,I had an idea of doing a cross check by comparing the ldf and mdf files. That succeeded if I create a database using the "CopyDatabase" wizard but if I use Backup and Restore database, the file comparison is failing, which implies, there is a minor difference from the original database.
Is there any way out????

quote:
Originally posted by khtan

then use BACKUP DATABASE and RESTORE DATABASE command

KH
[spoiler]Time is always against us[/spoiler]

---Jchi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources