| Author |
Topic |
|
frist44
Starting Member
12 Posts |
 Posted - 2008-09-04 : 15:53:28
|
| I have a SQL 2000 machine with about 1TB of data on it which needs to be transitioned to a new server in at least a few days.The drives will be setup differently and I would like databases to be organized differently than the source server, but would like it to be as automated as possible.Let me know if you have some ideas.Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-04 : 16:02:11
|
| BACKUP/RESTORE will be best for this. You can get the full backup restored ahead of time and then apply transaction log backups, differentials, or differentials plus transaction log backups in order to get the data up to date when you are ready to cut to the new system.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
frist44
Starting Member
12 Posts |
Posted - 2008-09-04 : 16:05:42
|
| what about all the configurations, logins, other settings, not just databases? Is that just the master backup and restore? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
frist44
Starting Member
12 Posts |
Posted - 2008-09-04 : 16:16:42
|
| Unfortunately I don't have the option of using the same file path on the new machine due to some disk configuration properties and size limitations. With that being the case, how can i be able to copy the database files to an external drive, take them to the new machine, and place them where i want? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
frist44
Starting Member
12 Posts |
Posted - 2008-09-04 : 16:34:19
|
| what is the best compression agent? what would be the advantage to detaching vs. backing up during the restoration process? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-04 : 17:06:54
|
| We use WinRAR (or rar.exe from the command line), Pkzip, and Winzip.You should be using backup/restore due to the size of your database. Backup/restore could have minimal downtime. However detach/attach will have hours/days of downtime due to how long it'll take to copy the files.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
frist44
Starting Member
12 Posts |
Posted - 2008-09-05 : 09:08:32
|
| I tried backup and restore and the backup went fine. For restoring, I have to have a database with the same name already created right?I went to restore and the restoration process is looking to restore the databases to the same disk path. There are serveral .mdf files associated with a database and it's trying to put them in their original location on the source server. How can i get them to just put them where the new database is established? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-05 : 12:15:17
|
| A database does not need to exist when performing a restore. You can use the WITH MOVE option in the RESTORE command to move the database files to a different location. This option is also available in the GUI on the options page.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
Best way to move SQL server to another machine