-- CREATE A
EMPTY DATABASE
USE [master];
GO
CREATE DATABASE DEEPTEST;
GO
--CREATE A
DATABASE SNPSHOT-1
CREATE DATABASE SNPDEEPDB ON
(Name ='DEEPTEST',
FileName='D:\DB_SnapShot\DEEPTEST.SNP')
AS SNAPSHOT OF DEEPTEST;
GO
--CREATE A
DATABASE SNPSHOT-2
CREATE DATABASE SNPDEEPDB2 ON
(Name ='DEEPTEST',
FileName='D:\DB_SnapShot\DEEPTEST2.SNP')
AS SNAPSHOT OF DEEPTEST;
GO
--NOW WE HAVE
TWO SNPASHOTS AND LETS TRY TO RESTORE ORIGNAL ONE FROM SNPSHOT2
USE [master]
GO
RESTORE DATABASE DEEPTEST
FROM DATABASE_SNAPSHOT = 'SNPDEEPDB';
GO
--You get the
error becuase we can only restore from a snapshot if that snapshot is the only
existing snapshot of the database while we have two
--so lets try to
drop one of snpshot and try again
--Msg 3137,
Level 16, State 4, Line 1
--Database
cannot be reverted. Either the primary or the snapshot names are improperly
specified, all other snapshots have not been dropped, or there are missing
files.
--Msg 3013,
Level 16, State 1, Line 1
--RESTORE DATABASE
is terminating abnormally.
DROP DATABASE SNPDEEPDB2
--Command(s)
completed successfully.
USE [master]
GO
RESTORE DATABASE DEEPTEST
FROM DATABASE_SNAPSHOT = 'SNPDEEPDB';
GO
--Command(s) completed
successfully.
--Lets add a new
datafile and filegroup to database now
USE [master]
GO
ALTER DATABASE [DEEPTEST] ADD
FILEGROUP [SECONDARY]
GO
ALTER DATABASE [DEEPTEST]
ADD FILE
(
NAME =
DEEPTEST3,
FILENAME = 'D:\Program Files\Microsoft
SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEEPTEST3.NDF',
SIZE = 5MB,
MAXSIZE =
100MB,
FILEGROWTH =
5MB
)
TO FILEGROUP
SECONDARY
--Now we have
database with multiple files
SELECT SDF.NAME AS [FILENAME],SDF.PHYSICAL_NAME AS 'PHYSICAL NAME',
SIZE/128
AS [SIZE_IN_MB],
FG.NAME AS
[FILE_GROUP_NAME]
FROM SYS.DATABASE_FILES SDF INNER
JOIN SYS.FILEGROUPS FG ON SDF.DATA_SPACE_ID=FG.DATA_SPACE_ID
|
FILENAME
|
PHYSICAL NAME
|
SIZE_IN_MB
|
FILE_GROUP_NAME
|
|
DEEPTEST
|
D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEEPTEST.mdf
|
1
|
PRIMARY
|
|
DEEPTEST2
|
D:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEEPTEST2.NDF
|
5
|
PRIMARY
|
|
DEEPTEST3
|
D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DEEPTEST3.NDF
|
5
|
SECONDARY
|
--Lets try to
create a snpshot again and we get the error
--CREATE A
DATABASE SNPSHOT-3
CREATE DATABASE SNPDEEPDB3 ON
(Name ='DEEPTEST3',
FileName='D:\DB_SnapShot\DEEPTEST3.SNP')
AS SNAPSHOT OF DEEPTEST;
GO
--Msg 5127,
Level 16, State 1, Line 2
--All files must
be specified for database snapshot creation. Missing the file
"DEEPTEST".
--Msg 5127,
Level 16, State 1, Line 1
--All files must be specified for
database snapshot creation. Missing the file "DEEPTEST".
-- So We will
have to We have to specify files for each of them
CREATE DATABASE DEEPTEST3 ON
(Name = 'DEEPTEST', FileName='D:\DB_SnapShot\DEEPTEST31.SNP'),
(Name = 'DEEPTEST2',Filename='D:\DB_SnapShot\DEEPTEST32.SNP'),
(Name = 'DEEPTEST3',Filename='D:\DB_SnapShot\DEEPTEST33.SNP')
AS SNAPSHOT OF DEEPTEST
--Command(s) completed
successfully.
Lets take a datafile offline
and run a snpshot now
ALTER DATABASE DEEPTEST MODIFY
FILE (NAME = 'DEEPTEST3', OFFLINE)
|
NAME
|
STATE
|
|
DEEPTEST
|
ONLINE
|
|
DEEPTEST_log
|
ONLINE
|
|
DEEPTEST2
|
ONLINE
|
|
DEEPTEST3
|
OFFLINE
|
CREATE DATABASE DEEPTEST4 ON
(Name = 'DEEPTEST', FileName='D:\DB_SnapShot\DEEPTEST41.SNP'),
(Name = 'DEEPTEST2',Filename='D:\DB_SnapShot\DEEPTEST42.SNP'),
(Name = 'DEEPTEST3',Filename='D:\DB_SnapShot\DEEPTEST43.SNP')
AS SNAPSHOT OF DEEPTEST
--Msg 5114, Level 16, State
1, Line 1
--Log files, offline files,
restoring files, and defunct files for database snapshots should not be
specified. "DEEPTEST3" is not an --eligible file for a database
snapshot.
--here we will
have to exclude the offlile datafile while taking snpshot.
CREATE DATABASE DEEPTEST4 ON
(Name = 'DEEPTEST', FileName='D:\DB_SnapShot\DEEPTEST41.SNP'),
(Name = 'DEEPTEST2',Filename='D:\DB_SnapShot\DEEPTEST42.SNP')
--(Name =
'DEEPTEST3',Filename='D:\DB_SnapShot\DEEPTEST43.SNP')
AS SNAPSHOT OF DEEPTEST
--Command(s) completed
successfully.
No comments:
Post a Comment