对于tmstamp的 一些小测试

SELECT TOP 1 b.type, b.first_lsn, b.last_lsn, b.checkpoint_lsn, b.database_backup_lsn 
FROM msdb..restorehistory a 
INNER JOIN msdb..backupset b ON a.backup_set_id = b.backup_set_id 
WHERE a.destination_database_name = 'TMSTAMP_test' 
ORDER BY restore_date DESC




use tmstamp_test
go


create table tt
(
id int,
tmstamp timestamp
)


--insert into tt(id) values (1);
--insert into tt(id) values (2);
--insert into tt(id) values (3);


select * from tt
--id tmstamp
--1 0x00000000000007D1
--2 0x00000000000007D2
--3 0x00000000000007D3


BACKUP DATABASE [TMSTAMP_test] TO  
DISK = N'D:\SqlBak\TMSTAMP_test.bak' WITH NOFORMAT, NOINIT, 
 NAME = N'TMSTAMP_test-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


--insert into tt(id) values (4);
--id tmstamp
--1 0x00000000000007D1
--2 0x00000000000007D2
--3 0x00000000000007D3
--4 0x00000000000007D4


USE [master]
BACKUP LOG [TMSTAMP_test] TO  DISK = N'D:\SqlBak\TMSTAMP_test_LogBackup_2017-06-14_23-34-46.bak' WITH NOFORMAT, NOINIT,  NAME = N'TMSTAMP_test_LogBackup_2017-06-14_23-34-46', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5
RESTORE DATABASE [TMSTAMP_test] FROM  DISK = N'D:\SqlBak\TMSTAMP_test.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5


GO




select * from tt
id tmstamp
1 0x00000000000007D1
2 0x00000000000007D2
3 0x00000000000007D3
insert into tt(id) values (4);


select id,cast(tmstamp as bigint) from tt
id tmstamp
1 0x00000000000007D1
2 0x00000000000007D2
3 0x00000000000007D3
4 0x0000000000001771




id (无列名)
1 2001
2 2002
3 2003
4 6001


select cast(0x00000000000007D4 as bigint) 
(无列名)
2004




insert into tt(id) values (5);
select * from tt;
select id,cast(tmstamp as bigint) from tt;
--id tmstamp
--1 0x00000000000007D1
--2 0x00000000000007D2
--3 0x00000000000007D3
--4 0x0000000000001771
--5 0x0000000000001772


--id (无列名)
--1 2001
--2 2002
--3 2003
--4 6001
--5 6002


USE [master]
RESTORE DATABASE [TMSTAMP_test] FROM  DISK = N'D:\SqlBak\TMSTAMP_test.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5
GO


insert into tt(id) values (4);
select * from tt;
select id,cast(tmstamp as bigint) from tt;


--id tmstamp
--1 0x00000000000007D1
--2 0x00000000000007D2
--3 0x00000000000007D3
--4 0x0000000000001771
--id (无列名)
--1 2001
--2 2002
--3 2003
--4 6001


USE [master]
BACKUP LOG [TMSTAMP_test] TO  DISK = N'D:\SqlBak\TMSTAMP_test_LogBackup_2017-06-14_23-34-46.bak' WITH NOFORMAT, NOINIT,  NAME = N'TMSTAMP_test_LogBackup_2017-06-14_23-34-46', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5
RESTORE DATABASE [TMSTAMP_test] FROM  DISK = N'D:\SqlBak\TMSTAMP_test.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5


GO
insert into tt(id) values (4);
select * from tt;
select id,cast(tmstamp as bigint) from tt;


--id tmstamp
--1 0x00000000000007D1
--2 0x00000000000007D2
--3 0x00000000000007D3
--4 0x0000000000001771
--id (无列名)
--1 2001
--2 2002
--3 2003
--4 6001


BACKUP DATABASE [TMSTAMP_test] TO  
DISK = N'D:\SqlBak\TMSTAMP_test01.bak' WITH NOFORMAT, NOINIT, 
 NAME = N'TMSTAMP_test-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
USE [master]
RESTORE DATABASE [TMSTAMP_test] FROM  DISK = N'D:\SqlBak\TMSTAMP_test01.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5
GO


select * from tt;
select id,cast(tmstamp as bigint) from tt;


--id tmstamp
--1 0x00000000000007D1
--2 0x00000000000007D2
--3 0x00000000000007D3
--4 0x0000000000001771
--id (无列名)
--1 2001
--2 2002
--3 2003
--4 6001
insert into tt(id) values (5);
select * from tt;
select id,cast(tmstamp as bigint) from tt;
--1 0x00000000000007D1
--2 0x00000000000007D2
--3 0x00000000000007D3
--4 0x0000000000001771
--5 0x0000000000002711
--id (无列名)
--1 2001
--2 2002
--3 2003
--4 6001
--5 10001
--
----------------------------------------------------------------------------------------------------------------------------------------------------------------
USE [master]
GO


/****** Object:  Database [T_restore]    Script Date: 2017/6/15 17:43:55 ******/
CREATE DATABASE [T_restore]


USE [master]
RESTORE DATABASE [T_restore] FROM  DISK = N'D:\SqlBak\TMSTAMP_test.bak' WITH  FILE = 1,
  MOVE N'TMSTAMP_test' TO N'D:\SqlData\T_restore.mdf', 
 MOVE N'TMSTAMP_test_log' TO N'D:\SqlLog\T_restore_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 5


GO
--USE master;
--GO
--ALTER DATABASE [T_restore]
--SET SINGLE_USER
--WITH ROLLBACK IMMEDIATE;
--GO


--ALTER DATABASE [T_restore]
--SET MULTI_USER;
--GO




select * from tt;
select id,cast(tmstamp as bigint) from tt;


insert into tt(id) values (4);


--id tmstamp
--1 0x00000000000007D1
--2 0x00000000000007D2
--3 0x00000000000007D3
--4 0x0000000000001771


--1 2001
--2 2002
--3 2003
--4 6001


--restore again to check if the tmstamp will be change


USE [master]
RESTORE DATABASE [T_restore] FROM  DISK = N'D:\SqlBak\TMSTAMP_test.bak' WITH  FILE = 1,
  MOVE N'TMSTAMP_test' TO N'D:\SqlData\T_restore.mdf', 
 MOVE N'TMSTAMP_test_log' TO N'D:\SqlLog\T_restore_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 5


GO


select * from tt;
select id,cast(tmstamp as bigint) from tt;


insert into tt(id) values (4);


select * from tt;
select id,cast(tmstamp as bigint) from tt;



/*
结论：
1.tmstamp不会重复
2.restore后tmstamp会从新的Lsnt开始，可以看到原来的tmstamp在2004，
但是Restore后并没有从2004开始，而是直接从6001开始
3.是否backup log并不影响tmstamp的取值
4.恢复后的数据库将从6001开始，只要备份一样，备份里的lsn一样，tmstamp的开始的值不变
5.恢复到新库的结果也和以上一直
6.目前比较感兴趣的是Restore后每次都比较规律的会在原来的tmstamp上加4000，不知道是何用意，有待继续升入研究
*/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16131092/viewspace-2140762/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16131092/viewspace-2140762/