This example shows the complete sequence you perform when using dump and load database, as described in “Shrinking a log before dump and load database”:
1> create database sales_db on sales_db_dev=3 log on sales_db_log1=3, sales_db_log2=3, sales_db_log1=3, sales_db_log2=3 2> go
00:00:00000:00015:2011/01/21 09:38:28.29 server Timestamp for database 'sales_db' is (0x0000, CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_dev' (1536 logical pages requested). CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_log1' (1536 logical pages requested). CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_log2' (1536 logical pages requested). CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_log1' (1536 logical pages requested). CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_log2' (1536 logical pages requested). Warning: The database 'sales_db' is using an unsafe virtual device 'sales_db_dev'. The recovery of this database can not be guaranteed. Database 'sales_db' is now online.
1> select * from sysusages where dbid=4 2> go
dbid segmap lstart size vstart location unreservedpgs crdate vdevno ---- ------ ------ ---- ------ -------- ------------- ------ ------ 4 3 0 1536 0 0 670 Jan 21 2011 9:38AM 1 4 4 1536 1536 0 0 1530 Jan 21 2011 9:38AM 2 4 4 3072 1536 0 0 1530 Jan 21 2011 9:38AM 3 4 4 4608 1536 1536 0 1530 Jan 21 2011 9:38AM 2 4 4 6144 1536 1536 0 1530 Jan 21 2011 9:38AM 3 (5 rows affected)
1> alter database sales_db log off sales_db_log2 2> select * from sysusages where dbid=4 3> go
dbid segmap lstart size vstart location unreservedpgs crdate vdevno ---- ------ ------ ---- ------ -------- ------------- ------ ------ 4 3 0 1536 0 0 670 Jan 21 2011 9:38AM 1 4 4 1536 1536 0 0 1530 Jan 21 2011 9:38AM 2 4 0 3072 1536 3072 4 1530 Jan 21 2011 9:38AM -4 4 4 4608 1536 1536 0 1530 Jan 21 2011 9:38AM 2 (4 rows affected)
1> sp_helpdb sales_db 2> go
name db_size owner dbid created durability status -------- ------------- ----- ---- ------------ ---------- -------------- sales_db 9.0 MB sa 4 Jan 21, 2011 full no options set (1 row affected) device_fragments size usage created free kbytes ------------------ ------ ---------- ------------------ -------------- sales_db_dev 3.0 MB data only Jan 21 2011 9:38AM 1340 sales_db_log1 3.0 MB log only Jan 21 2011 9:38AM not applicable sales_db_log1 3.0 MB log only Jan 21 2011 9:38AM not applicable -------------------------------------------------------------- log only free kbytes = 6082, log only unavailable kbytes = 3072 (return status = 0)
1> dump database sales_db to "c:/temp/sales_db.dmp" 2> go
Backup Server session id is: 45. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server. Backup Server: 4.41.1.1: Creating new disk file c:/temp/sales_db.dmp. Backup Server: 6.28.1.1: Dumpfile name 'sales_db11021087C7 ' section number 1 mounted on disk file 'c:/temp/sales_db.dmp' Backup Server: 4.188.1.1: Database sales_db: 848 kilobytes (67%) DUMPED. Backup Server: 4.188.1.1: Database sales_db: 862 kilobytes (100%) DUMPED. Backup Server: 3.43.1.1: Dump phase number 1 completed. Backup Server: 3.43.1.1: Dump phase number 2 completed. Backup Server: 3.43.1.1: Dump phase number 3 completed. Backup Server: 4.188.1.1: Database sales_db: 870 kilobytes (100%) DUMPED. Backup Server: 3.42.1.1: DUMP is complete (database sales_db).
1> load database sales_db from "c:/temp/sales_db.dmp" with headeronly 2> go
Backup Server session id is: 48. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server. Backup Server: 6.28.1.1: Dumpfile name 'sales_db11021087C7 ' section number 1 mounted on disk file 'c:/temp/sales_db.dmp' This is a database dump of database ID 4, name 'sales_db', from Jan 21 2011 9:39AM. ASE version: lite_642236-1/Adaptive Server Enterprise/15.7/EBF 18567 SMP Drop#2/B/X64/Windows Server/aseasap/ENG/. Backup Server version: Backup Server/15.7/B/X64/Windows Server/aseasap/ENG/64-bit/DEBUG/Thu Jan 20 11:12:51 2011. Database page size is 2048. Database contains 6144 pages; checkpoint RID=(Rid pageid = 0x604; row num = 0x12); next object ID=560001995; sort order ID=50, status=0; charset ID=2. Database log version=7; database upgrade version=35; database durability=UNDEFINED. segmap: 0x00000003 lstart=0 vstart=[vpgdevno=1 vpvpn=0] lsize=1536 unrsvd=670 segmap: 0x00000004 lstart=1536 vstart=[vpgdevno=2 vpvpn=0] lsize=1536 unrsvd=1530 Unavailable disk fragment: lstart=3072 lsize=1536 segmap: 0x00000004 lstart=4608 vstart=[vpgdevno=2 vpvpn=1536] lsize=1536 unrsvd=1530 The database contains 6144 logical pages (12 MB) and 4608 physical pages (9 MB).
1> create database sales_db2 on sales_db_dev=3 log on sales_db_log1=6 2> go
CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'sales_db_dev' (1536 logical pages requested). CREATE DATABASE: allocating 3072 logical pages (6.0 megabytes) on disk 'sales_db_log1' (3072 logical pages requested). Warning: The database 'sales_db2' is using an unsafe virtual device 'sales_db_dev'. The recovery of this database can not be guaranteed. Database 'sales_db2' is now online.
1> select * from sysusages where dbid=db_id("sales_db2")
2> go
dbid segmap lstart size vstart location unreservedpgs crdate vdevno ---- ------ ------ ---- ------ -------- ------------- ------ ------ 5 3 0 1536 1536 0 670 Jan 26 2011 1:22AM 1 5 4 1536 3072 3072 0 3060 Jan 26 2011 1:22AM 2
1> load database sales_db2 from "/tmp/sales_db.dmp" 2> go
Backup Server session id is: 10. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server. Backup Server: 6.28.1.1: Dumpfile name 'sales_db1102602564 ' section number 1 mounted on disk file '/tmp/sales_db.dmp' Backup Server: 4.188.1.1: Database sales_db2: 6148 kilobytes (33%) LOADED. Backup Server: 4.188.1.1: Database sales_db2: 9222 kilobytes (50%) LOADED. Backup Server: 4.188.1.1: Database sales_db2: 9230 kilobytes (100%) LOADED. Backup Server: 3.42.1.1: LOAD is complete (database sales_db2). Started estimating recovery log boundaries for database 'sales_db2'. Database 'sales_db2', checkpoint=(1544, 22), first=(1544, 22), last=(1544, 22). Completed estimating recovery log boundaries for database 'sales_db2'. Started ANALYSIS pass for database 'sales_db2'. Completed ANALYSIS pass for database 'sales_db2'. 00:00:00000:00011:2011/01/26 05:12:15.86 server Log contains all committed transactions until 2011/01/26 01:55:15.71 for database sales_db2. Started REDO pass for database 'sales_db2'. The total number of log records to process is 1. Completed REDO pass for database 'sales_db2'. 00:00:00000:00011:2011/01/26 05:12:15.88 server Timestamp for database 'sales_db2' is (0x0000, 0x00001612). Use the ONLINE DATABASE command to bring this database online; ASE will not bring it online automatically.
1> online database sales_db2 2> go
Started estimating recovery log boundaries for database 'sales_db2'. Database 'sales_db2', checkpoint=(1544, 22), first=(1544, 22), last=(1544, 22). Completed estimating recovery log boundaries for database 'sales_db2'. Started ANALYSIS pass for database 'sales_db2'. Completed ANALYSIS pass for database 'sales_db2'. 00:00:00000:00011:2011/01/26 05:12:22.49 server Log contains all committed transactions until 2011/01/26 01:55:15.71 for database sales_db2. Recovery of database 'sales_db2' will undo incomplete nested top actions. Database 'sales_db2' is now online.
1> select * from sysusages where dbid=db_id("sales_db2")
2> go
dbid segmap lstart size vstart location unreservedpgs crdate vdevno ---- ------ ------ ---- ------ -------- ------------- ------ ------ 5 3 0 1536 1536 0 670 Jan 26 2011 5:12AM 1 5 4 1536 1536 3072 0 1530 Jan 26 2011 5:12AM 2 5 0 3072 1536 3072 4 1530 Jan 26 2011 5:12AM -5 5 4 4608 1536 4608 0 1530 Jan 26 2011 5:12AM 2
