| Author |
Topic |
|
a_k_
Starting Member
23 Posts |
 Posted - 2005-08-25 : 14:48:22
|
| We have a SQL Server 2000 database of 950GB containing images. We need to copy it to the new server in the same network. We do not have space for the full backup. We can put the database offline for any time. What is the safest way to copy the database of this size? Is detach and attach is reliable enough? May detach cause corruption in the source database? Is it better to create a new database on the destination computer and copy tables one by one using DTS? |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-08-25 : 15:21:18
|
quote:
Originally posted by X002548
Unplug the drive and put it in the new box?I guess the answer is probably a tape device.....I do not know.....Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Hehe. Dont they make 1tb secure digital cards now?  a_k_, I don't suppose you use anything like SQL Litespeed to backup with? How do you do your backups? Restoring the data will probably not be the fastest way but would probably be the most reliable. I had detach/attach blow up on me one time in SQL 7 and I've not trusted it since.DanielSQL Server DBAwww.dallasteam.com |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-25 : 15:23:22
|
| Are the MDF and LDF files on a SAN drive? If so, you could stop the MSSQLSERVER service on the old server, unpresent the drive on the old server, present it on the new server, start up SQL Server on the new server, then attach the MDF and LDF.Tara |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-08-25 : 15:31:49
|
| "We do not have space for the full backup."Does this mean you have no backup of this database? If so, you have a disaster waiting to happen, unless it is OK to lose your database.CODO ERGO SUM |
|
|
|
a_k_
Starting Member
23 Posts |
Posted - 2005-08-25 : 15:33:10
|
| We need to copy a database to the larger RAID system because there is not enough space for it on the current one, so we cannot re-attach the drive. |
|
|
|
a_k_
Starting Member
23 Posts |
Posted - 2005-08-25 : 15:34:27
|
| We have additional database that contain the same data as a backup. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-08-25 : 15:52:24
|
Is the new RAID system at least twice the size of your current database? If it is, backup you current database to the new RAID system over the network, and then restore it from that backup.If you are going to have databases this size, you need to plan for your disk storage requirements before you implement the database. The rule of thumb that I use is to have enough disk storage available for at least 5 times the expected maximum size of your database: 1 for the database, 1 for a database backup, 1 for a second database backup (so you don't have to delete your first backup before making your new backup), 1 for a restore of a backup file from tape if you need to recover data, and 1 for a restore of the database to recover data. You wiil need additional space for transaction logs; the amount needed will depend on the amount of activity on your database.You should really have some kind of tape backup also.quote:
Originally posted by a_k_
We need to copy a database to the larger RAID system because there is not enough space for it on the current one, so we cannot re-attach the drive.
CODO ERGO SUM |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-08-25 : 15:54:50
|
quote:
Originally posted by a_k_
We have additional database that contain the same data as a backup.
1tb Firewire drive:[url]http://www.lacie.com/products/product.htm?pid=10452[/url]Don't wait so long for those backups to run and don't worry about them being so big. Use Litespeed: [url]http://www.idera.com/Products/sqlsafe/?S=GW[/url]DanielSQL Server DBAwww.dallasteam.com |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-08-25 : 15:57:51
|
quote:
Originally posted by a_k_
We have additional database that contain the same data as a backup.
Is that database on the same server and same array? If yes then you will not be happy when you get a big hardware failure.DanielSQL Server DBAwww.dallasteam.com |
|
|
|
a_k_
Starting Member
23 Posts |
Posted - 2005-08-25 : 17:02:04
|
| The backup database is on the different server.quote:<<<<Is the new RAID system at least twice the size of your current database? If it is, backup you current database to the new RAID system over the network, and then restore it from that backup.>>>>Do I have to restore to the new not-existing database? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-08-25 : 17:51:28
|
| Why not detach the database, and robocopy the files over?Robocopy is pretty darn fast with one big file like that.I'm in the process of moving 200GB of files and SQL databases using robocopy.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-25 : 19:03:31
|
| I have not had any problems with the Detach/Attach approach, but then I've not tried it with a 1TB file. I'd be curious what problems Dan ran into and whether that was on the Detach side or the Attach side.---------------------------EmeraldCityDomains.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-25 : 22:08:13
|
| If you don't want to detach the DB [and I wouldn't either without a just-in-case backup] but you can stop the SQL Server, then you can copy the MDF file over [with SQL Stopped] and reattach it the other end. (Copy the LDF file too for a normal attach, MDF only for a single_file_attach.)Obviously having the LDF too is a good idea, but maybe its huge too. Maybe truncate the log file first, before the stop & copy operation, to reduce its size.Kristen |
|
|
|
a_k_
Starting Member
23 Posts |
Posted - 2005-08-26 : 13:39:14
|
| quote:<<<<you can stop the SQL Server, then you can copy the MDF file over [with SQL Stopped] and reattach it the other end.>>>>Thank you very much, I tried it on the small database and it worked fine. But what 'detach' procedure does differently? Was it implemented only in order not to stop the entire Server or it does something additional to the detached database? |
|
|
|
a_k_
Starting Member
23 Posts |
Posted - 2005-08-26 : 13:47:02
|
| quote:<<<<Is the new RAID system at least twice the size of your current database? If it is, backup you current database to the new RAID system over the network, and then restore it from that backup.>>>>Thank you very much, I tried it on the small database using restore "with move" and it worked fine. But in this case I will have to copy 1TB of data twice - first time from the source db to the backup file and second time from the backup file to the destination db. Is backup/restore option much safer then stopping Server, coping db files to the destination server and attaching them to the destination server? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-26 : 13:58:42
|
| Yes backup/restore is safer, however it's slower than detach/attach or stopping the MSSQLSERVER/copying files/attach. When you stop the MSSQLSERVER service, copy the files, then attach to the other server, it's the same as detach/attach except you brought the server down to do it. The advantage of detach/attach besides being able to keep the server up is that you can do an update statistics prior to the detach. I've done this though as oru statistics are already updated.Tara |
|
|
|
a_k_
Starting Member
23 Posts |
Posted - 2005-08-26 : 14:08:11
|
| Why backup/restore is safer? I hope that if I just stop the server and copy db files nothing may happen to the source database. Is it right? Is it possible that destination Server will not be able to attach the database? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-08-26 : 14:13:37
|
| You can move your database with minimum downtime using backup/restore. You can have the database active on the old server for all but a few minutes, and then quickly switch to the new server. If anything goes wrong with the move, you can just keep using the old server.I usually follow this procedure:1. Make sure the old database is in full recovery mode and is running transaction log backups about every 15 minutes.2. Setup all the logons on the new server that are in the database on the old server.3. Make a full backup of the old database, and restore it onto the new server WITH NORECOVERY. Use the WITH MOVE option to place the database files in the correct directories4. Restore the transaction logs from the old database to the new database in order WITH NORECOVERY.5. Set the old database to DBO ONLY, and get all of the users out of the database.6. After you are sure there is no one in the old database and all work is complete, make one last transaction log backup and then set the old database to READ ONLY.7. Restore the last transaction log backup to the new database WITH RECOVERY and RESTRICTED_USER.8. Setup a backup job for the new database, and run it.9. Setup a transaction log backup job for the new database and start it running.10. Turn off the DBO ONLY on the new database.11. Point the applications at the new database.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-27 : 02:01:34
|
| "RESTRICTED_USER"Never knew thatKristen |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-08-29 : 10:57:16
|
quote:
Originally posted by AjarnMark
I have not had any problems with the Detach/Attach approach, but then I've not tried it with a 1TB file. I'd be curious what problems Dan ran into and whether that was on the Detach side or the Attach side.---------------------------EmeraldCityDomains.com
It was SQL 7 and the DB would not reattach. I think the file became corrupted or something. All I know is that I ended up having to restore from a db dump.  I didn't do much troubleshooting to find the exact problem and don't remember the exact error. That was one of my first DBA jobs and I was very green. |
|
|
|
Next Page
|
How to copy 1TB database