Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to create a snapshot of sql server 2005 database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Use the create database command to create a snapshot of the database, and when you create a snapshot, you must include each data file in the source database in the create database command. Includes the original logical name and a new physical name. No other file properties can be specified, and log files are not used.

The operation to create a snapshot of the Archive database is as follows:

1. First look at the logical name of the data file in the Archive database

C:\ Users\ Administrator > sqlcmd-S WINS7-2014DITHH\ JY1 > use Archive2 > GO has changed the database context to 'Archive'. 1 > select name,physical_name from sys.database_files 2 > GOname physical_name -- Arch2 C:\ Program Files\ Microsoft SQLServer\ MSSQL.1\ MSSQL\ Data\ archdat1.mdfArchlog1 C:\ Program Files\ Microsoft SQLServer\ MSSQL.1\ MSSQL\ Data\ archlog1.ldfArch3 C:\ Program Files\ Microsoft SQLServer\ MSSQL.1\ MSSQL\ Data\ archdat2.ndf (3 lines affected)

two。 Create a database snapshot Archive_snapshot

1 > create database Archive_snapshot on2 > (3 > logical name of name='Arch2',-- data file 4 > filename='C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ Data\ Archive_snapshot_1.mdf'-snapshot file 5 >), 6 > (7 > name='Arch3',8 > filename='C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ Data\ Archive_snapshot_2.mdf'9 >) 10 > as snapshot of Archive;11 > GO

The following inserts a row of records into table T1 in the source database

1. Check the records in table T1 first

1 > use Archive;2 > GO has changed the database context to 'Archive'. 1 > select * from T1 politics 2 > GOt_id tdate1-1 2019-04-25 (1 line affected)

two。 Insert a row of records into table T1

1 > insert into T1 values (2 June 2019-04-26'); 2 > GO; (1 line affected)

3. Query the records in table T1 again

1 > select * from T1 * 2 > GOt_id tasking date-1 2019-04-252 2019-04-26 (2 lines affected)

Query the record of table T1 in the snapshot database

1 > use Archive_snapshot;2 > GO has changed the database context to 'Archive_snapshot'. 1 > select * from T1 politics 2 > GOt_id tdate1-1 2019-04-25 (1 line affected)

You can see that there is still only one record for T1 in the snapshot database, and the snapshot database contains only the data that existed in the source database when the snapshot was created.

Each file in the snapshot is created as a sparse file, which is a feature of the NTFS file system. Initially, a sparse file contains no user data and no disk space is allocated to store user data. When data is written to a sparse file, NTFS gradually allocates disk space for it. A sparse file can grow a lot. Sparse files grow in increments of 64KB, so the size of sparse files on disk is always a multiple of 64KB.

The snapshot file contains only data that has changed in the source database. For each file, SQL Server creates a bitmap saved in the cache, and each page of the file corresponds to a bit, indicating whether that page has been copied to the snapshot. Every time a page in the source database is updated, SQL Server looks at the bitmap to see if the page has been copied, and if not, immediately copies it to the snapshot. This operation is called a copy on write operation.

As mentioned earlier, the bitmap is saved in the adjustment cache, not the file itself, so it is always available for use. When SQL Server shuts down or the database shuts down, the bitmap is lost and needs to be rebuilt when the database is started. When SQL Server is visited, it determines whether each page is read in a sparse file, and then saves this information in a comparison diagram for general use.

The snapshot reflects the point in time when the create database command was issued-that is, at the beginning of the creation operation. SQL Server checkpoints the source database and records a synchronous log sequence number (Log Sequence Number,LSN) in the log file of the source database. LSN is a way to determine a specific point in time in a database. SQL Server then runs the restore on the source database so that any uncommitted transactions can be rolled back in the snapshot. So although the sparse files of snapshots are initially blank, they can't be maintained for long. If a transaction is in progress when the snapshot is created, the recovery process will undo the uncommitted transaction before the database snapshot is available, so the snapshot will contain the original version of all pages in the source database of the modified data.

Snapshots can only be created on volumes in NTFS format because this format is the only file format that supports sparse file technology. If we try to create a snapshot on a FAT or FAT32 volume, we will receive an error similar to the following:

Msg 1823, Level 16, State 2, Line 1A database snapshot cannot be created because it failed to start.Msg 5119, Level 16, State 1, Line 1Cannot make the file "E:\ AW_snapshot.MDF" a sparse file. Make sure the file system supportssparse files.

The first error is basically a normal error message, and the second error letter provides more detailed information about why the operation failed.

You can find out how much byte data is in use on the disk in each sparse file of the database snapshot by looking at the dynamic management function sys.dm_io_virtual_file_stats, which returns the current byte data in a file through the size_on_disk_bytes column. This function takes database_id and file_id as arguments. The database id of the snapshot database and the file ID of each sparse file are displayed in the catalog view sys.master_files. You can also view its size through Windows Explorer.

1 > select name,database_id from sys.databases 2 > GOname database_id-master 1 tempdb 2 model 3 msdb 4 AdventureWorksDW 5 AdventureWorks 6 resource_COPY 7 Archive 8 Archive_snapshot 9 (9 rows affected) 1 > select database_id File_id,name,physical_name from sys.master_files 2 > GOdatabase_id file_id name physical_name- -- 1 1 master C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ DATA\ master.mdf 1 2 mastlog C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ DATA\ mastlog.ldf 2 1 tempdev C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ DATA\ tempdb.mdf 2 2 templog C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ DATA\ templog.ldf 3 1 modeldev C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ DATA\ model.mdf 3 2 modellog C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ DATA\ modellog.ldf 4 1 MSDBData C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ DATA\ MSDBData.mdf 4 2 MSDBLog C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ DATA\ MSDBLog.ldf 5 1 AdventureWorksDW_Data C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ Data\ AdventureWorksDW_Data.mdf 52 AdventureWorksDW_Log C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ Data\ AdventureWorksDW_Log.ldf 6 1 AdventureWorks_Data C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ Data\ AdventureWorks_Data.mdf 6 2 AdventureWorks_Log C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ Data\ AdventureWorks_Log.ldf 7 1 data C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ Data\ mssqlsystemresource_copy.mdf 7 2 log C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ Data\ mssqlsystemresource_copy.ldf 8 1 Arch2 C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ Data\ archdat1. Mdf 8 2 Archlog1 C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ Data\ archlog1.ldf 8 3 Arch3 C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ Data\ archdat2.ndf 9 1 Arch2 C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ Data\ Archive_snapshot_1.mdf 9 3 Arch3 C:\ Program Files\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ Data\ Archive_snapshot_2.mdf (19 lines affected) 1 > select * from sys.dm_io_virtual_file_stats (9 NULL) AS vfs 2 > GOdatabase_id file_id sample_ms num_of_reads num_of_bytes_read io_stall_read_ms num_of_writes num_of_bytes_written io_stall_write_ms io_stall size_on_disk_bytes file_handle-- - -91 1902593092 60 491520 133 5 40960 4 262144 0x000000000000080C9 3 1902593092 4 32768 11 1 8192 0 11 65536 0x0000000000000840 (2 rows affected)

Because it is possible to have multiple snapshots of the same database, we must ensure that there is enough disk space available. Snapshots start relatively small, and each snapshot grows as the source database is updated. Space for sparse files is allocated according to fragments called regions, in 64KB. When an area is assigned, all pages are cleared except for one that has been changed. At this point, there is space in the area for seven changed pages, and no new area will be allocated until the seven page spaces are used up.

It is possible to overapply for storage space. This means that under normal circumstances, we can have multiple snapshots that are many times the actual physical storage space, but once the snapshot grows, the physical volume will be exhausted (this can happen when running online dbcc checkdb commands or related commands, because we have no control over the physical location of the internal snapshots used by these commands-it will be stored on the same Zheng as the parent database file. The dbcc check will fail in this case). Once the physical volume runs out of space, writes to the source database cannot copy the page image before writing to a sparse file. Snapshots that cannot be written to the page are recorded as suspect and cannot be used, but the source database can continue to run. It is not possible to repair a suspect snapshot and the snapshot database must be deleted.

Manage Snapshot

If a snapshot exists in a source database, you cannot delete, detach, or restore the source database. If a database is switched to an offline (offline) state, the snapshot will be deleted automatically. In addition, we can basically Revert the source data back to the state it was in when the snapshot was created, and replace the source database with a snapshot of it. The command is as follows:

1 > use master;2 > GO has changed the database context to 'master'. 1 > restore database Archive from database_snapshot='Archive_snapshot';2 > GO1 > use Archive;2 > GO has changed the database context to 'Archive'. 1 > select * from T1 politics 2 > GOt_id tdate1-1 2019-04-25 (1 line affected)

After restoring the source database Archive to the state it was in when the snapshot was created, there is only one record in table T1.

During the Revert operation, the snapshot and source database are available and are marked as "restoring". If an error occurs during the reply operation, the operation will attempt to complete the reply operation when the database is restarted. You cannot revert to any of the snapshots when there are multiple snapshots, so we should first delete all snapshots except the one we want to restore. Deleting a snapshot is very similar to a drop database operation. When the snapshot is deleted, all NTFS sparse files are deleted.

The following additional considerations related to database snapshots need to be noted:

. Snapshots cannot be created on model,master and tempdb databases (some snapshots can be created internally by SQL Server to run online DBCC checks on master databases, but these snapshots cannot be created explicitly).

1 > drop database Archive_snapshot;2 > GO

. A snapshot inherits security constraints from its source database, and because the snapshot is read-only, we cannot change its permissions.

. If you delete a user from the source database, the user remains in the snapshot.

. Snapshots cannot be backed up or restored, but the source database can be backed up normally; it is not affected by database snapshots.

. Snapshots cannot be attached or detached.

. Database snapshots do not support full-text indexing, and full-text catalogs are not propagated from the source database to the snapshot.

This is the end of "how to create a snapshot of a sql server 2005 database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.