Copy a 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

Old Forums

CLOSED - General SQL Server

Copy a Database

Author

Topic

larrykl
Yak Posting Veteran

67 Posts

Posted - 2004-10-27 : 07:08:05

I'm trying to use DTS to copy all data/objects/SPs/views to a different server, basically copying the production data to my development box to update it.
I keep getting a failure on some tables regarding Foreign Key constraints.
How do I do this? Is there a certain order I need to follow?
I'm currently using the Copy Data and Ojects option.

Any tips?

Thanks!

gpl
Posting Yak Master

195 Posts

Posted - 2004-10-27 : 09:46:28

The easiest way is to backup the database and reattach them on the dev server

otherwise you need to copy the highest (parent) level tables first, then the next level and so on

Then the views / stored procs etc

Graham

larrykl
Yak Posting Veteran

67 Posts

Posted - 2004-10-27 : 12:40:43

Hi gpl,

That sounds good. I was thinking of doing something like that. I know how to do a backup and restore on a single server. How do I restore on another server? Do I have to do a backup, copy the file over to the new server and then restore? How will the restore work when the database doesn't already exist on the new server?

gpl
Posting Yak Master

195 Posts

Posted - 2004-10-27 : 15:47:48

Yes, do just that
Im going to have to think about the restore, I havent done it for a while.

Using Enterprise Manager, restore, from filegroup, new filegroup. Then when you have located where the file is stored, you select it, then before clicking ok, select the main file and log tabls and overwrite the location and name of the database filenames.

If you cant work it, let me know and Ill run through the process when I get back to work in the morning and note the steps.

Or someone more experienced can reply with the steps (please)
Graham

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-27 : 15:52:05

The restore command will create the database for you if a container doesn't exist already. If one does, then you'll need the REPLACE command. I always have the REPLACE option in my RESTORE command cause it works either way. Anyway, yes you'll need to backup the db at the source, copy the file over, then restore. If the path information to the mdf and ldf files are identical, then the RESTORE command is simple:

RESTORE DATABASE DBNameGoesHere -- change the database name
FROM DISK = 'F:\MSSQL\BACKUP\Somefile.BAK' -- change this information obviously
WITH REPLACE

If the path information isn't the same, then let us know.

Tara

larrykl
Yak Posting Veteran

67 Posts

Posted - 2004-10-29 : 03:40:09

The path info is different. I added a new hard drive to the server so the drive letter is the only difference. How do I make that change? Should I be using the EM dialog to do this or from query analyzer?

Kristen
Test

22859 Posts

Posted - 2004-10-29 : 07:10:31

You can use EM, but the check boxes and options are Not Very Obvious - speaking politely!

There again, I think that the commands for RESTORE are a bit hairy-techie too ...

You need to do three things:

1) Interogate the backup file to find what its logical names are
2) Restore the file into the appropriate database (including indicating where the path is)
3) "RENAME" the logical names to more appropriate ones

Step (1):


RESTORE FILELISTONLY FROM DISK = 'x:\Mypath\MyFullBackupfile.BAK'

This will give something like:


LogicalName        PhysicalName                 Type FileGroupName Size      MaxSize
------------------ ---------------------------- ---- ------------- --------- --------------
MyOldDatabase_data D:\MSSQL\DATA\MyDatabase.mdf D    PRIMARY       355467264 35184372080640
MyOldDatabase_log  D:\MSSQL\DATA\MyDatabase.ldf L    NULL          168624128 35184372080640

Step (2a):


RESTORE DATABASE MyNewDatabaseName
	FROM DISK = 'x:\Mypath\MyFullBackupfile.BAK'
	WITH
		REPLACE,	-- Overwrite DB - if one exists
--		NORECOVERY,	-- Use if more T/Logs to recover
		RECOVERY,	-- Use if no more T/Logs to recover, database will be set ready to use
		STATS = 10,	-- Show progress (every 10%)
	MOVE 'MyDatabase_data' TO 'x:\MySQLDataPath\MyNewDatabaseName.mdf', 
	MOVE 'MyDatabase_log' TO 'x:\MySQLDataPath\MyNewDatabaseName.ldf'
GO

Step (2b)

If you have a Differential backup to restore do this:


RESTORE DATABASE MyNewDatabaseName
	FROM DISK = 'x:\Mypath\MyDifferentialBackupfile.BAK'
	WITH
--		RECOVERY	-- Use if NO more files to recover, database will be set ready to use
		NORECOVERY	-- Use if there are T/Logs to recover

Step (2c)

If you have any Transaction Backup files to restore repeat this for each one in turn:


RESTORE LOG MyNewDatabaseName
	FROM DISK = 'x:\Mypath\MyTransactionBackupfile.BAK'
	WITH
--		RECOVERY	-- Use if NO more T/Logs to recover, database will be set ready to use
		NORECOVERY	-- Use if more T/Logs to recover

Step (3)


-- Rename logical names:
ALTER DATABASE MyNewDatabaseName MODIFY FILE (NAME = 'MyOldDatabase_data', NEWNAME = 'MyNewDatabase_data')
GO
ALTER DATABASE MyNewDatabaseName MODIFY FILE (NAME = 'MyOldDatabase_log', NEWNAME = 'MyNewDatabase_log')
GO