| Author |
Topic |
|
lanmou
Starting Member
4 Posts |
 Posted - 2007-05-16 : 11:24:57
|
Hi All, I am new to sql server and the database concepts and just started learning.I want to copy a database and local package from a old sql server to a new server.can anybody guide me with the steps? I am very new to this field so if you could give me detailed steps i would very much appreciate it.thanks in advance, |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-16 : 12:19:28
|
| For the database - back it up, copy the file and restore it. Right click on the database and see alltasks.For dts packages you should be saving them as structured storage files so it is just a matter of copying the file.If you are saving in msdb then load it into the designer, save as a structured storage file and copy that across (or you could just save it to the other server).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
lanmou
Starting Member
4 Posts |
Posted - 2007-05-16 : 12:24:44
|
Thanks for the response. here is what i meant when is said iam new to this  .............iam sorry but iam going to write it down in stepsso i understand how to take a backup for the dts. i usually remote desktop to the new server, go to enterprise manager and map to the new server and open the package there and in the save as dialog box give the destination as my new server.well now about the database. how do i take a backup?? I do have to detach it right? and then how do i take a backup?thanks |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-16 : 12:42:53
|
| >> I do have to detach it right?No you don't.In enterprise manager (the thing you aer using to edit packages) right click on the database, all tasks, backup database, click on add to specify a file.Copy the file created to the new serverIn enterprise manager on that serverright click on databases, alltasks, restore database, from device, select the file you copied, type in the database name in the box at the top.Click on the options tab - the folders for the file locations must exist - if they don't then overtype with locations that do exist.Click ok.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
lanmou
Starting Member
4 Posts |
Posted - 2007-05-16 : 14:34:34
|
| Is there a particular folder i need to copy the files to in the new server.I copied it to the C:\dbname and when i tried to restore it gives me a error saying:Device activation error. The physical file name 'E:\MSSQL7\data\Email_Data.MDF' may be incorrect.File 'Email_Data' cannot be restored to 'E:\MSSQL7\data\Email_Data.MDF'.Use WITH MOVE to identify a valid location for the file.Device activation error. The physical file name 'E:\MSSQL7\data\Email_Log.LDF' may be incorrect.File 'Email_Log' cannot be restored to 'E:\MSSQL7\data\Email_Log.LDF'.Use WITH MOVE to identifya valid location for the file.RESTORE DATABASE is terminating abnormally. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-16 : 15:39:39
|
| If db file path on target machine is different from source machine, you have to move it with move option in restore statement like:restore database db_name from disk = ... with ...move 'Email_Data' to 'new_path\'Email_Data.MDF',move 'Email_Log' to 'new_path\'Email_Log.LDF' |
|
|
|
lanmou
Starting Member
4 Posts |
Posted - 2007-05-16 : 17:19:46
|
| If i keep the same file path in the destination server as the source would it work? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-16 : 23:15:20
|
| Then you don't need move files. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-17 : 06:44:59
|
| Read the instructions I gave you.it is saying that E:\MSSQL7\data\ does not exist - you either ened to create it or >> Click on the options tab - the folders for the file locations must exist - if they don't then overtype with locations that do exist.When you restore from a backup it will create the database files but will not create the folder so that needs to exist.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-05-17 : 08:59:00
|
| This utility works very well for what you want to do. It can transfer hundreds of packages from on server to another in a few minutes.And it is Free.DTSBackup 2000http://www.sqldts.com/242.aspx"DTSBackup 2000 is a designed to help with both backup and transfer of DTS packages. Whilst it is not recommended as a substitute for your normal file and database backup routines, it is an additional layer of protection, with the benefit of package level granularity. See the article Backing Up Packages for additional information. Whilst the name is backup, it is most frequently recommend as a tool to transfer packages between servers, either directly or via a file stage. This version of DTSBackup provides full support for Microsoft SQL Server 2000. New features introduced in this version of DTSBackup include a new DTSBackup file format and direct transfer of packages between SQL Servers. These methods do not suffer the loss of layout information encountered when using the DTS object model."CODO ERGO SUM |
|
|
|
|
How to copy databases and dts packages?