best way to copy database from one server to another?
-
Hi,
I have a WORKGROUP setup and two computers on which I have SQL server 20000 (enterprise edition) installed. I would like to transfer database from one server to another. I tried to use the restore functionality, and it did not work for me --- I guess restore works only within a server. I tried to use the copy database wizard and it also produced some error that spat out some error message without any explanation about the error. The last thinh I tried is the import functionality of the DTS. I went to the destination server and, at first, when I tried to just choose the option of selection all objects to be transferred, it did not work. A message speaking of an invalid object name such as 'dbo.inventory received'. Hence I came across an article saying that MS has a bug of not being able to synchronize the transfer of objecs such that the depended objects will be transferred last. So I started manually transferring first the tables, then I transferred all the other objets making sure not to transfer the dependents, as the article suggested. The final outcome is that the Diagrams were not transferred.
My question is whether I could accomplish the transfer in better way and whether diagrams can be transferred. It seems like this sql server that is supposed to be powerful in functiionality does not miss an opportunity to fail oot to have a buf with respect to any functionality.
any help will be much appreciated
Avi
-
Backup/Restore should have no problem moving databases between servers. How far are you getting when you try this method?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
I would also go for the backup/restore method but make sure that you transfer logins first, otherwise you will get broken users.
If your two servers have different sort orders, code pages etc then restore can give you problems.
If your servers can talk to each other then you could try replication as an option.
-
Hi,
Thank you all for your input. When I read your replies I am comforted and encouraged in the sense that all the methods of copying a db from one server to another all seem to be feasible without too many difficulties as backup/restore should not be difficult. I don’t know if I’m doing the right thing when using the back/restore functionality. I will provide the details of my doings, and I will appreciate your guidance/comments if something is done wrongly (which I’m pretty sure it’s the case).
Ok, so I have a one machine in which I have an sql server 2000, call it server1. In server1 I have a fbig file (approx. 500,000 kb), and that file is the file that contains all the backups of the database, call it mydatabase, which I have been backing up for the past months.
It’s important to note that when I go about restoring the database under any given name of database in server1, I don’t encounter any problems. Going back to the main topic, I have another machine with sql server 2000 installed on it, call it server2. This is the server to which I desire to copy mydatabase to. I guess that this is the point where I’m doing the wrong thing. More specifically, in order to restore mydatabase from the backup created in server1 into some database in server2, I use the fact that I am inside a WORKGROUP setup. I simply copy the big backup file from server1’s machine into server2’s file directory(c: drive), and then I proceed to perform the restoration of mydatabase inside server2 using the backup file that is in server2’s machine at that point. I manually copied the file from server1 to server2 files directory because I did not see any way by which I could select a backup file (by clicking on select devices button and when using sql restore wizard) from server2’s restore wizrad to the extent that the file is located in a different machine.
When I start the restoration, it fails and it produces the following error message
Could anyone tell me where did I go wrong --- what are the steps, assuming that those described above are wrong, to restore mydatabse as a database in server2.
Thank you in advance
Avi
-
Your restore error message is not appearing - I think it is looking for a linked jpg file - please update your post to include it.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
hi,
I can not paste the image, so I'll copy word for word the error message. it says the following:
title bar: microsoft sql-DMO(ODBC SQL State 42000)
the message itself is:
device activation error. The physical file name 'D:\Program Files\Microsoft sql server\mssql\data\mybackupfile.mdf' may be incorrect.
File cellular_data can not be restored to D:\Program Files\Microsoft sql server\mssql\data\mybackupfile.mdf. use WITH MOVE to identify a valid location for the file
device activation error. The physical file name 'D:\Program Files\Microsoft sql server\mssql\data\newcellularclient_log.ldf' may be incorrect.
file 'cellular_log' can not be restored to 'D:\Program Files\Microsoft sql server\mssql\data\newcellularclient_log.ldf'. use WITH MOVE to identify a valid location for the file
restore database is terminating abnormally
I will make a personal note and say that the file paths described above in the message error are server1's machine paths --- that is, the restore wizard is launched in server2's machine trying to restore the file I copied from server1's machine to server2'2 machine, as I explained in my previous post. To remind all the caring people in this group, server1 is the server that contains the databse I desire to copy into server2, which is running on a different machine. Bothe machines are connected to one another in a WORKGROUP setup.
Please feel free to ask for anything that is missing from the input regarding the problem at hand, so that all the details would be readily available for the scrutiny of this workgroup.
-
I'm assuming that you're using Enterprise Manager...
When you perform the restore, the destination file paths default to what they were on the previous server and you need to manually amend them (under the Options tab, in the 'Restore As' field, just type in the new file paths - for both data and log files) before clicking the OK button. A bit painful, I know.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
hi,
i'm a great believer of the saying that one person can make a difference, and i appreciate people like you who by their knowledge and willingness to help make a big difference with a seeming easiness.
thank you phil -- thanks to you i successfully restored the file for backup purposes
avi
-
hi avi it seams u had a lot of probs but no perfect reply. I have a solution for u.
First add the backup device (Disk type) on the Production Server( Source server)
with sp_addumpdevice procedure
with the network path of the destination server for eg-:
\\servername\c$\DBbackup\backup.bak
make sure the SQL server Service (on production server /source server)starts with the login account which has the rights to access the network drives.
then u can use backup command to take backup and restore for the restoring the database. Make sure to verify the DB before restoreing it.
Good luck
Charul
Regards,
NEO
-
I am fairly new to SQL and have a great automatic way to do this:
I have 2 Servers, Windows 2003 Standard, both with SQL Server 2000 and sp3a.
I have a folder on the main server d:\mssql\data\backup\ - I made a folder here called "disasterbackupcopy".
I have a folder on the backup server d:\mssql\data\backup\ - I made a folder here called "disasterrecoverycopy".
Now setup job to run in SQL Agent, Jobs, here is what my job does:
--start of the script--
USE master
EXEC xp_cmdshell 'DEL D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS_HIPAA.BAK', no_output
EXEC xp_cmdshell 'DEL D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS.BAK', no_output
EXEC xp_cmdshell 'DEL D:\MSSQL\BACKUP\DisasterBackupCopy\CMS.BAK', no_output
EXEC xp_cmdshell 'DEL \\ufcw655hw2\DisasterRecoveryCopy\VHCS_HIPAA.BAK', no_output
EXEC xp_cmdshell 'DEL \\ufcw655hw2\DisasterRecoveryCopy\VHCS.BAK', no_output
EXEC xp_cmdshell 'DEL \\UFCW655HW2\DisasterRecoveryCopy\CMS.BAK', no_output
BACKUP DATABASE VHCS_HIPAA TO DISK='D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS_HIPAA.BAK'
BACKUP DATABASE VHCS TO DISK='D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS.BAK'
BACKUP DATABASE CMS TO DISK='D:\MSSQL\BACKUP\DisasterBackupCopy\CMS.BAK'
EXEC xp_cmdshell 'COPY D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS_HIPAA.BAK \\UFCW655HW2\DisasterRecoveryCopy\*.*', no_output
EXEC xp_cmdshell 'COPY D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS.BAK \\UFCW655HW2\DisasterRecoveryCopy\*.*', no_output
EXEC xp_cmdshell 'COPY D:\MSSQL\BACKUP\DisasterBackupCopy\CMS.BAK \\UFCW655HW2\DisasterRecoveryCopy\*.*', no_output
-- End of the Script --
Set your shares for all administrator rights.
Then I have a script that runs every day at 4:00am and for the start of each day I have a perfect backup of the .bak files - I can easily restore if needed if the main server dies. NOTE: I also everynight have Veritas Backup Exec v9 with SQL Agent do a full backup to tape. I have on the hour a transaction log created thru the day while users work.
The purpose of the about script and shares is only incase the main production server dies, I can be up pretty quick and the database from the previous day is sitting over there. 9 times out of 10, your server dies or crashes at night and its when you come in for the 8am work to start somebody yells, "I can't connect to the database!"
-
I am fairly new to SQL and have a great automatic way to do this:
I have 2 Servers, Windows 2003 Standard, both with SQL Server 2000 and sp3a.
I have a folder on the main server d:\mssql\data\backup\ - I made a folder here called "disasterbackupcopy".
I have a folder on the backup server d:\mssql\data\backup\ - I made a folder here called "disasterrecoverycopy".
Now setup job to run in SQL Agent, Jobs, here is what my job does:
--start of the script--
USE master
EXEC xp_cmdshell 'DEL D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS_HIPAA.BAK', no_output
EXEC xp_cmdshell 'DEL D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS.BAK', no_output
EXEC xp_cmdshell 'DEL D:\MSSQL\BACKUP\DisasterBackupCopy\CMS.BAK', no_output
EXEC xp_cmdshell 'DEL \\ufcw655hw2\DisasterRecoveryCopy\VHCS_HIPAA.BAK', no_output
EXEC xp_cmdshell 'DEL \\ufcw655hw2\DisasterRecoveryCopy\VHCS.BAK', no_output
EXEC xp_cmdshell 'DEL \\UFCW655HW2\DisasterRecoveryCopy\CMS.BAK', no_output
BACKUP DATABASE VHCS_HIPAA TO DISK='D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS_HIPAA.BAK'
BACKUP DATABASE VHCS TO DISK='D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS.BAK'
BACKUP DATABASE CMS TO DISK='D:\MSSQL\BACKUP\DisasterBackupCopy\CMS.BAK'
EXEC xp_cmdshell 'COPY D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS_HIPAA.BAK \\UFCW655HW2\DisasterRecoveryCopy\*.*', no_output
EXEC xp_cmdshell 'COPY D:\MSSQL\BACKUP\DisasterBackupCopy\VHCS.BAK \\UFCW655HW2\DisasterRecoveryCopy\*.*', no_output
EXEC xp_cmdshell 'COPY D:\MSSQL\BACKUP\DisasterBackupCopy\CMS.BAK \\UFCW655HW2\DisasterRecoveryCopy\*.*', no_output
-- End of the Script --
Set your shares for all administrator rights.
Then I have a script that runs every day at 4:00am and for the start of each day I have a perfect backup of the .bak files - I can easily restore if needed if the main server dies. NOTE: I also everynight have Veritas Backup Exec v9 with SQL Agent do a full backup to tape. I have on the hour a transaction log created thru the day while users work.
The purpose of the about script and shares is only incase the main production server dies, I can be up pretty quick and the database from the previous day is sitting over there. 9 times out of 10, your server dies or crashes at night and its when you come in for the 8am work to start somebody yells, "I can't connect to the database!"
-
hi to all,
Once again, thank you all. For the time being I managed to use the backup file to restore the database to a different server(which is what i wanted to do to begin with), but what I got from all this group is what I wanted and much more, and I'm happy to be so lucky. I'm sort of researching this subject of 'backup and recovery, by reading some book, so once I complete reading a few more chapters, I'll be in a better position to organize in my mind the rich suggestions that this group has included in this thread--- by using it as a repository of ideas written by seasoned sql-savvy people.
Once again, it's great to know that there is such wealth of knowledge by people like you
Avi
-
I run the following job on the server with the database I want to backup daily:
BACKUP DATABASE [BUDGET] TO DISK = N'\\DRmachine\d$\DBBACKUP\BUDGET_FULL_DAILY' WITH INIT , NOUNLOAD , NAME = N'BUDGET_Daily_Full_Backup', NOSKIP , STATS = 10, NOFORMAT
I run the following job 1 hour later on the server I want to restore the database on:
RESTORE DATABASE [BUDGET] FROM DISK = N'D:\DBBackup\BUDGET_FULL_DAILY' WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , REPLACE , MOVE N'BUDGET_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\BUDGET.mdf', MOVE N'BUDGET_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\BUDGET_log.ldf'
This way I always have duplicate copies of the database on each server and I get notifications if anything went wrong like the power going out at the Disaster Recovery site. Just replace the name of your database everywhere you see BUDGET
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply