Copying and renaming database - SQL Server Forums

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 and renaming database

Author

Topic

GloriaLuz
Starting Member

1 Post

Posted - 2005-10-25 : 02:09:05

Hello,

I have created a complete database with its tables, views, procedures, everything in SQL Server for a system. Now I am creating another system and I have realised that I can use almost the same fields I have and only do small changes, therefore I want to copy the existing one and rename it

I have been trying to copy it but I don't know how to copy exactly everything. Could you help me please?

Any help would be really appreciated!
Thank you
Gloria

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-25 : 02:22:17

1
Create a new database
Script the Old Database from Enterprise Manager
Run the script in new Database (Use Query Analyser to run this)

2
Back up old database and restore it in new DB

Madhivanan

Failing to plan is Planning to fail

gkrishn
Starting Member

16 Posts

Posted - 2005-10-25 : 12:23:22

Check in BOL about changing DB name.
sp_changedbname

activecrypt
Posting Yak Master

165 Posts

Posted - 2005-10-26 : 00:26:22

Hi,

1). Backup Database and Restore It

2). Dettach - Attach Database

3). If you wants to Rename DB

quote:
sp_renamedb
Changes the name of a database.

Syntax
sp_renamedb [ @dbname = ] 'old_name' ,
[ @newname = ] 'new_name'

Arguments
[@dbname =] 'old_name'

Is the current name of the database. old_name is sysname, with no default.

[@newname =] 'new_name'

Is the new name of the database. new_name must follow the rules for identifiers. new_name is sysname, with no default.

Return Code Values
0 (success) or a nonzero number (failure)

If you wants to Synchronise it the you may go for

1). Replication
2). Log Shiping

-----------------------------------------------------------
MSSQL Server encryption software http://www.activecrypt.com

Kristen
Test

22859 Posts

Posted - 2005-10-26 : 00:52:06

I think I would script the database, globally change all the columns you want to, and then run that script on a new (empty) database.

Then I would create a script (DTS / BCP / or just INSERT INTO TargetDB.dbo.MyTable (ColA, ...) SELECT ColA_NewName, ... FROM SourceDB.dbo.MyTable) to punt the data across.

The problem with the Backup & Restore - followed by "Rename Column" - approach is that the "Rename column" will break the Triggers, Stored Procedures, Views etc. whereas a global Find&Replace of the script [to rename an object] will get around that.

FWIW we make all the column names in our databases unique (i.e. we do not have an column called "ID" in every table!), and use the column name for any variables that refer to that column - so we can safely do a find&replace without accidentally changing something unexpectedly!

OTOH if your database does not have any Triggers, Views or Stored Procedures this will be a bit overkill!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources