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

Question

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

Steve Dingle

SSChasing Mays

Points: 601
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 SSChasing Mays Points: 601 More actions · Answer 2

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 SSChasing Mays Points: 601 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.