SQL Server Performance Forum – Threads Archive

convert from win2k/sql 2k to win03/sql 2k??

This is very strange. I am planning to upgrade my application system from
Win2000 server/SQL Sever 2k SP3 to Win2003 server/SQL Server 2k SP3. After upgrading the user database, I used exec sp_password NULL, ‘password_1’, ‘existingUser’ to reset user password to my app database, it worked fine since I can verify it by
reset it from password_1 into password_2: exec sp_password ‘password_1’, ‘password_2’, ‘existingUser’ Also, query analyser showed "password changed".
But I cannot logon to this database (from the appl system). However if I do the upgrade from Win2000 Pro/SQL Server 2000 SP3: Win2000 Pro/SQL Sever 2k SP3 to Win2003 server/SQL Server 2k SP3. and reset the password, I can logon to the new system. The reason I reset password is, after the upgrade from win2k/sql 2k
to win03/sql 2k, user password doesn’t work anymore – I don’t want to
restore the master db from win2k to win03, I restored only the user
database. Any help is appreciated! – mingus

If you do not restore master database how will you get the database users ported to the upgraded system. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I am able to do it without restoring master if the source is Win2k Pro/SQL Server 2K sp3
and the destination is Win2k Svr/SQL Server 2K sp3
But not when the source is also a server version. Tried SQL Server’s Copy Database Wizards. Again if the
source is Win2k Pro/SQL Server 2K sp3, fine, if it is also
a server edition, cannot even connect: Access Denied or the
server doesn’t exist!! Tried to restore master, but it always tells me that
"the database to be restored was named [user_db],
reissue the statement using the WITH REPLACE option to
overwrite it."
where [user_db] is a valid name for my user defined database name.
I repeated this X times to make sure that I backed up the
correct master db to master_backup file name ‘master_backup’. restore database master
from disk = ‘d:master_backup’
with replace
When I added the with replace option, I got the same error (asking
for the WITH REPLACE option).
Am I the only one worry about Convert db from
Win2k Svr/SQL Server 2K sp3 to Win03 Svr/SQL Server 2K sp3??

One of the KBA refers to restore system databases as : Restore the master database. In the following example, the master database contains one data file, Master.mdf, and one log file, Mastlog.ldf, from the full database backup file, Master_db.bak. Run the following command from a command prompt to start SQL Server: d:mssql7innsqlservr -c -m NOTE: The -m switch starts SQL Server in single-user mode. In single-user mode, you cannot successfully make more than a single connection. Please be aware of any other clients or services that could make that single connection before you connect with SQL Server Query Analyzer. In SQL Server Query Analyzer, use the following syntax to restore a backup of the master database: RESTORE DATABASE master FROM disk=’d:mssql7ackupmaster_db.bak' WITH MOVE ‘master’ to ‘d:mssql7datamaster.mdf’, MOVE ‘mastlog’ to ‘d:mssql7datamastlog.ldf’, REPLACE go NOTE: SQL Server shuts down automatically after you restore the master database. Run the following command from a command prompt to start SQL Server: d:mssql7innsqlservr -c -f -T3608 -T4022 NOTE: By using this syntax, you can perform system table updates. In SQL Server Query Analyzer, execute the following set of statements: WARNING: Updating system tables incorrectly can cause serious problems that may require you to reinstall your SQL Server installation. Microsoft cannot guarantee that problems resulting from the incorrect updating of system tables can be solved. Update system tables at your own risk. USE master go UPDATE sysdatabases SET filename=’d:mssql7data empdb.mdf’ WHERE name=’tempdb' go ALTER DATABASE tempdb MODIFY FILE (name = tempdev, filename = ‘d:mssql7data empdb.mdf’) ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = ‘d:mssql7data emplog.ldf’) go You should receive the following messages confirming the change: File ‘tempdev’ modified in sysaltfiles. Delete old file after restarting SQL Server. File ‘templog’ modified in sysaltfiles. Delete old file after restarting SQL Server. Restore the model database. In the following example, the model database contains one data file Model.mdf, and one log file, Modellog.ldf, from the full database backup file, Model_db.bak. If you do not have a backup of the model database or if you want to use the one that is installed by default during the SQL Server installation, you can remap the model database. This procedure is similar to the procedure that is described for the northwind database and the pubs database in step 4. In SQL Server Query Analyzer, execute the following stored procedure to detach the current model database from SQL Server: exec sp_detach_db N’model' go After you execute the stored procedure, you receive the following message: Successfully detached database ‘model' DBCC execution completed. If DBCC printed error messages, contact your system administrator NOTE: You need to detach the model database because you cannot directly overwrite it by using the RESTORE statement. You have to remove the system table references for this system database before the database is restored. In addition, you cannot drop the database for the purpose of removing those system entries. In SQL Server Query Analyzer, use the following syntax to restore a backup of the model database: RESTORE DATABASE model FROM disk=’d:mssql7ackupmodel_db.bak' WITH MOVE ‘modeldev’ TO ‘d:mssql7datamodel.mdf’, MOVE ‘modellog’ TO ‘d:mssql7datamodellog.ldf’, REPLACE go NOTE: SQL Server shuts down automatically after you restore the model database. Restore the msdb database. In the following example, the msdb database contains one data file, Msdbdata.mdf, and one log file, Msdblog.ldf, from the full database backup file, Msdb_db.bak. Run the following command from a command prompt to start SQL Server<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />:mssql7innsqlservr -c -f -T3608 -T4022 In SQL Server Query Analyzer, execute the following stored procedure to detach the msdb database:exec sp_detach_db N’msdb' go In SQL Server Query Analyzer, use the following syntax to restore a backup of the msdb database: RESTORE DATABASE msdb FROM disk=’d:mssql7ackupmsdb_db.bak' WITH MOVE ‘msdbdata’ TO ‘d:mssql7datamsdbdata.mdf’, MOVE ‘msdblog’ TO ‘d:mssql7datamsdblog.ldf’, REPLACE go NOTE: SQL Server shuts down automatically after you restore the msdb database. <hr noshade size="1">Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

Thanks a lot Satya. (1) But I couldn’t backup master log file.
I scheduled a job for master log backup only, it always fails,
the log in EventLog doesn’t tell much. The master data file can be
backed up without problem. All the user db can be backed up,
data files and log files, separately. Also, if I go to Tools->Backup Database… from Enterprise manager,
I can only select "Database – complete" for master, while for user
dbm I can select any of the four options. (2) if I restore master without master log by using: RESTORE DATABASE master FROM disk=’drogram FilesMicrosoft SQL ServerMSSQLackupmaster_db.bak’
WITH MOVE ‘master’ to ‘drogram FilesMicrosoft SQL ServerMSSQLdatamaster.mdf’,
REPLACE
go my master got corrupted and have to re-install sql server. No big deal since I
am experimenting. At the informit.com website, they suggest to add a logic device: sp_addumpdevice ‘disk’, ‘master_backup’, ‘c: mpsql_backupmaster_backup.dmp’
go then restore from the logic devive: restore database master from master_backup
go Is the dmp file the master db complete backup file?
– mingus

1) System databases default recovery model is SIMPLE and you cannot perform the BACKUP log on those databases. 2) If the source MASTER database or backup is corrupted the only way is to rebuild the master using REBUILDM utility as defined in the books online. As define when you backup the master database it will perform compete database backup. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>