SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Copy a Database
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

larrykl
Yak Posting Veteran

67 Posts

Posted - 10/27/2004 :  07:08:05  Show Profile
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

United Kingdom
195 Posts

Posted - 10/27/2004 :  09:46:28  Show Profile
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
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts

Posted - 10/27/2004 :  12:40:43  Show Profile
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?
Go to Top of Page

gpl
Posting Yak Master

United Kingdom
195 Posts

Posted - 10/27/2004 :  15:47:48  Show Profile
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 10/27/2004 :  15:52:05  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

larrykl
Yak Posting Veteran

67 Posts

Posted - 10/29/2004 :  03:40:09  Show Profile
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?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/29/2004 :  07:10:31  Show Profile
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

Kristen

Edited by - Kristen on 10/29/2004 07:16:19
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 10/29/2004 :  12:32:34  Show Profile  Visit tkizer's Homepage
Very cool, Kristen.

Tara
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/29/2004 :  12:41:32  Show Profile
Gee thanks, Tara. It's homework for my RSN blogg!

Kristen
Go to Top of Page

tkbutt
Starting Member

USA
1 Posts

Posted - 10/29/2004 :  15:47:52  Show Profile
test
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 10/29/2004 :  15:51:44  Show Profile  Visit tkizer's Homepage
Oh no!

Tara
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000