Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
twsnnva
Starting Member
4 Posts |
 Posted - 2005-01-11 : 15:40:37
|
| Hello all,I need some advice with moving everything on my MSSQL server to a new server. I have some background with *nix/MySQL, and am pretty comfortable with the databases themselves, however the backup/restore process is all new to me. We will be restructuring our entire site, to ensure security of our networks, and protection of our data. The only thing left that I can't quite get a grasp on is the MSSQL DBs. Downtime is not much of a concern as I am already anticipating no less than 4 hours, for everything. Could someone please assist, with a simple walkthrough of how this would be done? I was doing some testing and successfully copied all of our databases to the new server, and moved them to a single folder, using restore commands like thisrestore database testfrom disk = 'd:\sql_backup\test_db.BAK'with move 'test_Data' to 'd:\sql_server_data\mssql\data\test_Data.mdf',move 'test_Log' to 'd:\sql_server_data\mssql\data\test_log.ldf'Everything worked until I did this to the master db. Then SQL would no longer start. MySQL dosen't have a master db, is this similar to the default "mysql" database that holds SQL user info? How can I transfer this to a new server? I really appreciate any help.Thanks,Thomas |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-11 : 15:44:43
|
| You can only restore the master database if the path information is the same on both servers. Since you are using the WITH MOVE option of the RESTORE command, then I'm guessing that they aren't. Can you fix it so that they are the same? It's much easier. If you can't, then you are going to need to move the things that are stored in master via other ways, like sp_help_revlogin for the users. What do you need from master? Typically, it's only user accounts.http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql2kTara |
 |
|
|
twsnnva
Starting Member
4 Posts |
Posted - 2005-01-11 : 18:33:51
|
| I could leave all of the paths the same, however I would perfer changing them. On our current server master and a few other databases are stored in c:\program files\microsoft sql server\mssql\data, some are in d:\sql_data, and some are on e:\sql_data. Our new server has a 130GB D:\ partition that I would like to put everything together on it. Is master simply used for user accounts? If so, is just moving our data over like I mentioned and just adding our users back? Honestly, I just want to do this the easiest way possible. Also, can someone tell me what the other default databases are used for, like model, northwind, and msdb. Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-11 : 19:01:27
|
| Typically, users are the only things that you need from it. What I would do to make this easier is install SQL Server to the same paths as the originating server. Then do your restores to these paths. Then move things around how you want them using that article link I posted. I've done that successfully a few times now. Northwind is a play database. Model is used to create new databases. Msdb is used for DTS packages, jobs, alerts, etc...Tara |
|
|
|
twsnnva
Starting Member
4 Posts |
Posted - 2005-01-11 : 22:06:01
|
| Quote: What I would do to make this easier is install SQL Server to the same paths as the originating server. Then do your restores to these paths. Then move things around how you want them using that article link I posted.Actually, that's exactly what I thought about doing after you mentioned that I had to restore the master db to the original path. I'm gonna give that a shot tommorow, hopefully it's exactly what I need. Thanks. |
|
|
|
twsnnva
Starting Member
4 Posts |
Posted - 2005-01-12 : 12:36:04
|
| Tara,I successfully restored all of my databases (including master) to their original paths. I can successfully detach a database, however when I try to attach the databases, where I want them, I get a Device activation error. sp_attach_db 'TESTDB','D:\SQL_SERVER_DATA\NEW\TESTDB.mdf','D:\SQL_SERVER_DATA\NEW\TESTDB.ldf'Server: Msg 5105, Level 16, State 4, Line 1Device activation error. The physical file name 'D:\SQL_SERVER_DATA\NEW\TESTDB.mdf' may be incorrect.The directory D:\SQL_SERVER_DATA\NEW does exist. Do you know what may be causing this?Thanks.Edit:BTW, is there a command line utility to query the db? I don't really like the query analyzer. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-12 : 15:27:24
|
| Before running sp_attach_db, you must manually move the MDFs and LDFs to their new location. sp_attach_db assumes that they are already in that location. The stored procedure just loads them into SQL Server.For command line utility, you can use osql.exe.Tara |
|
|
|
|
|
|
|
|
Transferring MSSQL to new server