EXPLORING, CREATING, AND RECOVERING DATABASES in Font

Maker ANSI/AIM Code 39 in Font EXPLORING, CREATING, AND RECOVERING DATABASES

cd c:\program files\microsoft sql server\mssql.1\mssql\data\ rename Database_1a.bak Database_1a.mdf rename Database_1a_log.bak Database_1a_log.ldf The sample concludes by recovering with the following T-SQL script. The script begins by dropping the two databases as well as the unused log file (Database_1_log.ldf) copied to the chapter03 folder. The DROP DATABASE statement removes the data and log files for both databases. An xp_cmdshell statement erases the unused log file. After renaming the backup files by invoking the recoverdb_1afiles batch file, the code invokes CREATE DATABASE with a FOR ATTACH clause. The syntax is the same as the preceding one, but this time it works successfully because the data file points at the correct log file in the appropriate path. The script concludes with a SELECT statement to list the databases on the server instance along with sp_helpdb information on the newly recovered Database_1a. The output from these two statements is the same as that showing in Figure 3-10. This outcome confirms that these steps illustrate one approach to recovering a corrupted database. DROP DATABASE Database_1b GO xp_cmdshell 'erase c:\prosseapps\chapter03\Database_1a_log.ldf' GO EXEC sp_helpdb @dbname = N'Database_1a' DROP DATABASE Database_1a GO xp_cmdshell 'c:\prosseapps\chapter03\recoverdb_1afiles' GO CREATE DATABASE Database_1a ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Database_1a.mdf') FOR ATTACH GO SELECT name, database_id FROM sys.databases EXEC sp_helpdb @dbname = N'Database_1a' GO

As the preceding section demonstrates, specifying a single database file that is, the primary data file for a CREATE DATABASE statement with a FOR ATTACH clause can sometimes be appropriate and other times lead to catastrophic failure. Designating just the primary data file is particularly appropriate when you can copy data and log files between computers so that they are in the same path on both computers. This situation may apply when you have a staging computer and a production computer. As the preceding sample illustrates, designating a single primary data file when basing a new database on copied files between paths on a single computer requires a different approach. If you specify the data file(s) and the log file(s) separately, then you can successfully create a new database with the copied files. In addition, the new database will not disrupt the database from which files were copied. The syntax for designating both the primary data file and the log file in a CREATE DATABASE statement with a FOR ATTACH clause appears next. CREATE DATABASE your_database_name ON PRIMARY (FILENAME = 'drive:\path\filename.mdf') LOG ON (FILENAME = 'drive:\path\filename.ldf') FOR ATTACH The following script excerpt is from SQLQuery6_f0312.sql. The excerpt starts by invoking an xp_cmdshell statement to copy files for Database_1a to the chapter03 folder in the c:\prosseapps directory. After the files become available in the folder, the code executes a CREATE DATABASE statement with references to the recently copied primary data file and log file in the chapter03 folder.

The excerpt concludes by listing the databases on the server instance and the displaying sp_helpdb result sets for the new database, Database_1b. xp_cmdshell 'c:\prosseapps\chapter03\copydb_1afiles' GO CREATE DATABASE Database_1b ON PRIMARY (FILENAME = 'c:\prosseapps\chapter03\Database_1a.mdf') LOG ON (FILENAME = 'c:\prosseapps\chapter03\Database_1a_log.ldf') FOR ATTACH GO SELECT name, database_id FROM sys.databases EXEC sp_helpdb @dbname = N'Database_1b' GO Figure 3-12 shows the results from the SELECT and sp_helpdb statements in the preceding script excerpt. As you can see from the first result set, the script adds a new database named Database_1b. In addition, both the primary data file and the log file for the new database point to the copied files in the c:\prosseapps\chapter03 path. This result leaves the previously existing Database_1a unaffected by the addition of Database_1b.

Figure 3-12. When referencing a data file and a log file, Database_1b is independent of Database_1a. The log file in Figure 3-11 is not independent of the log file for Database_1a (see Figure 3-10).