Answers from Microsoft - 27 Nov 2000

Editor's Note: Send your SQL Server questions to Richard Waymire, program manager for SQL Server development at Microsoft, at [email protected]

How can I move a database's transaction log to a different drive?

You can move the transaction log by using SQL Server's stored procedures for detaching and attaching the database. You need to detach the database, move the log file, then reattach the database. For example, to move the Pubs database from the C drive to the D drive, you first enter the following sp_detach_db command to detach the database:

EXEC sp_detach_db 'pubs'

Next, copy the pubs.mdf and pubs_log.ldf files to the destination drive. The following example shows how to copy these two files to the d:\mssql7\data directory:

copy c:\mssql7\data\pubs.mdf d:\mssql7\datacopy c:\mssql7\data\pubs_log.ldf d:\mssql7\data\

Finally, after you copy the files, you can use the sp_ attach_db command to reattach the data to SQL Server:

EXEC sp_attach_db 'pubs', 'd:\mssql7\data\pubs.mdf', 'd:\mssql7\data\pubs_log.ldf'

I'm confused about how SQL Server 7.0 handles full backups and transaction log backups. I recently did a series of four backups on my database: a full database backup, then a transaction log backup, then another full backup, followed by another transaction log backup. After each backup session, I issued many transactions. Then I dropped the database. I recovered the database from the first full backup, then from the first transaction log backup, then from the second transaction log backup.

Using this method, I brought the database back online as it was at the last transaction log backup. Why was I able to bring the database back up to this state, even though I skipped restoring the second full backup?

You must restore your SQL Server database from a full backup to give the transaction log a place to start. However, as long as you have an unbroken chain of transaction log backups, you don't need to recover any backups other than those for the transaction logs. Because full backups don't modify the log history, you can skip as many full backups as you want. However, you must start the recovery process by restoring from a full backup.

Can I upgrade directly from SQL Server 6.5 to SQL Server 2000? Also, does SQL Server 2000 require Windows 2000 or does it work with Windows NT 4.0?

Yes; you can upgrade from SQL Server 6.5 to SQL Server 2000. The process is similar to an upgrade from SQL Server 6.5 to 7.0. SQL Server 2000 requires NT 4.0 Service Pack 5 (SP5) or later.

I'm trying to implement an application role for all users who access a SQL Server database by using Microsoft Access. I've set up the role and assigned a password to it, but I don't know how to force the sp_setapprole for all Access connections. How can I restrict users' permissions when they use Access to access a SQL Server database?

You can't force sp_setapprole—which is a stored procedure that activates the permissions associated with an application role—for Access connections. You can set a startup procedure for an .mdb file or .adp file that could call sp_setapprole by using ADO. But this method doesn't cover the scenario of someone creating a new .mdb file or .adp file with a connection to SQL Server. (For more information about granting access to SQL Server from Access applications, see Rick Dobson, SQL Server/Office Integration, "Access Granted," page 31.)

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.