Copying DB from one server to another, dfferent DB name, using scripts

Question

Post reply

Copying DB from one server to another, dfferent DB name, using scripts

Steve Dingle

Mr or Mrs. 500

Points: 551
More actions
May 29, 2007 at 8:55 am

#119101

Heya All,

I need to get all the data from my copy of a DB into a database, which might be of a different name, on my clients machine via a script. I have a script which will create the empty database (using a name of there choice), add user etc.., on the client server, but now I need a way to get the data copied too.

I also don't have access to there server so I well be sending them files of some sort or writing some in C# or something.

The problem I am having with the data is in getting the data loaded in the proper order based on relationship etc.. (i.e. need to make sure the Parent record is added before Child). I can't use SP_FKEYS to get that information because I can't use FOREIGN KEY constraints.

I was wondering if I could use a BACKUP but wasn't sure if it would RESTORE into a

different database.. also not sure if all the extraneous information stored in the server table would get updated properly

Any and all suggestions highly appreciated.

Much Thanks,

Steve Dingle

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 1

This can be done in a couple of different ways. The easiest I think would be a backup/restore (though you can use sp_detach_db and sp_attach_db as well). Here is an example using backup/restore.

--Perform a normal backup:

backup database TestWork to DISK='C:\Projects\Test\BACKUPFILES\TestWork.dmp' with format

go

--On the new server run the following restore command which will change the name of the database

--from TestWork to TestDW. I also like to keep the internal names striaght so the ALTER DAtabase

--statements take care of the internal names.

RESTORE DATABASE [TestDW]

FROM DISK='c:\projects\Test\BACKUPFILES\TestWork.dmp'

WITH

REPLACE,

MOVE 'TestWork_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TestDW_Data.MDF',

MOVE 'TestWork_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TestDW_Log.LDF'

go

USE [master]

GO

ALTER DATABASE [TestDW] MODIFY FILE (NAME=N'TestWork_Log', NEWNAME=N'TestDW_Log')

GO

USE [master]

GO

ALTER DATABASE [TestDW] MODIFY FILE (NAME=N'TestWork_Data', NEWNAME=N'TestDW_Data')

GO

Steve Dingle Mr or Mrs. 500 Points: 551 More actions · Answer 2

Steve Dingle

Mr or Mrs. 500

Points: 551

May 29, 2007 at 10:00 am

#709409

Thanks JLK,

Wondering if I could as a follow-up.

I assume the client would have default folders to put there database and log files, is there a function that I can use to get that information.

Thanks

Much Thanks,

Steve Dingle

Steve Dingle Mr or Mrs. 500 Points: 551 More actions · Answer 3

Sorry, yet another follow up.

I was just going to test this on my local server to see if I could create a copy using the code you provided and I noticed this line.

>> ALTER DATABASE [TestDW] MODIFY FILE (NAME=N'TestWork_Log', NEWNAME=N'TestDW_Log')

Would this cause me any trouble with my actual database (i.e. the one I just backup to run the test)?

I assume it would but wanted to check

Much Thanks,

Steve Dingle

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 4

While I'm sure there is a way to retrieve that info I have not done so through a script. If the database already exists you can get it from the filename column of master.dbo.sysdatabases. You are probably not the first to want this info so a carefully crafted search of this site is likely to find what you want.

James.