Question. Why must you recreate SQL Server databases in the EXACT same order you created them if you want to restore them from a backup? (Applies to 6.0, 6.5, and possibly 4.2 and sybase servers)

Answer. SQL Server assigns LOGICAL page numbers to each fragment of a database. For example, if you create a 4MB database with a 2MB log, it will assign pages 0 - 2047 to the data, and 2048 - 3071 to the log. If you then increase the database data and add 2MB it will assign 3072-4095 to the second data fragment. When the database is backed up, it backs up all the logical pages in sequence. It also restores them in sequence. That means that if you create a 6MB database with a 2MB log and restore it from the dump taken of the database created 4,2,2; SQL Server will place the log data that it had assigned to logical pages 2048-3071 on the original database and put them in logical pages 2048-3071 on the new database; but in the new database pages 0-3071 are all assigned to the a data segment, and 3072-4095 are assigned to a log segment (in sysusages in master).

Question. What problems can I expect if I don't have a correct database configuration?

Answer. You may experience no problems initially. To read data from tables, SQL Server will locate the beginning of the table correctly and traverse through the data in the table pages using logical page numbers, it will not pay attention to the fragment it is in. When doing inserts, SQL Server will probably find the extent it needs to insert to correctly and allow you to do an insert as well. However THE PROBLEM OCCURS WHEN SQL SERVER ATTEMPTS TO ALLOCATE A NEW EXTENT for an object. It looks at sysusages to see in which fragment it should allocate more space for an object, but if it finds that the fragment is full of wrong kind of pages(log or data), it will give you error message 1105, out of space.

Q. How do I find out how a database was originally created?

Answer. All of the information is in sysdevices, sysusages, and sysdatabases in master. You must obtain this information before deleting any databases or devices. You can run this query:

select Dbase = db_name(dbid),MB=size/512,DeviceName=dv.name,dbid,segmap,lstart,vstart from sysusages us, sysdevices dv where us.vstart >= dv.low and us.vstart < dv.high and status <> 16 order by dbid, lstart

This tells you that database DB1 was created originally with

1. Create Database DB1 on DB1dev = 40, DB1_Inx = 20 LOG ON DB1_Log = 10
2. It was later altered with Alter Database DB1 on DB1dev = 20
3. Alter Database DB1 on DB1dev = 15

Be aware of segments - they are a special challenge. Run sp_helpsegment in each database to see if you have them. If you have segmaps(from above) with values other than 3(data), 4(log), or 7(data and log) then you probably have segments.