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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-28 : 08:04:50
|
| Al writes "OK, I know it's in Books OnLine, except that it's a pretty cursory description for us relatively new SQLserver DBAs. Obviously(?) the goal is to rebuild the master with the new collation sequence (we're talking moving from nocase_850 to noaccents_850) and preserve all of the SQLserver user permissions, database characteristics, etc.; i.e. an identical environment to that with which we started except for that collation sequence change.I don't have any experience with bcp, or much with scripting an entire database, and most of my experience is with MMC and not T-SQL or other 'command line interface'. The basic question is whether y'all know of a white paper that delves in excrutiating detail into the specific commands to execute to preserve master, msdb, etc., not to mention databases with diagrams (which makes use of DTS problematic). I'll settle with that basic question for the moment, as the quantity of questions I'd have if that isn't true is non-trivial. Thanks,Al" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-28 : 08:52:00
|
| You need to export your data into other files; text, CSV etc. You could also use MS Access; if you do, link to the SQL Server using ODBC, include all the tables you need, and create the links. Then, you must create make-table queries to COPY the SQL data into new, separate Access tables. Once you rebuild master, you recreate the databases. You can use the Upsize wizard in Access, or just DTS, to move the data back into SQL Server. If you use flat files, you can bcp in, BULK INSERT, or DTS the data.If you have Enterprise Manager, there is an option to Generate Scripts for a database. (right-click the database, look for All Tasks, and Generate Scripts). I recommend that you go through the wizard very slowly, look at EVERY checkbox and setting, and view the help for it. The quick and dirty way to do it is to check everything and generate the script file, but it *might* cause some minor issues. Once the script is generated, you rebuild master, then open query analyzer, and load and run the script file. Voila! Your databases are created, all you have to do is load the data.Database diagrams are stored in the dtproperties table. Unless your diagrams have a lot of annotations on them, it might be better to just regenerate them instead of copying the table. If you have another SQL Server that you can DTS into, you can transfer dtproperties to the other server, rebuild master, then DTS back into it. |
 |
|
|
|
|
|
|
|
Changing the Collation Sequence in SQL7 SP3 (NT4,SP6a)