May 28, 2008 at 5:08 am
Hello!
Is there any possibilities to make SnapShot database but i order way adding the time of that snapshot:
For example:
CREATE DATABASE TEST_DB ON
( NAME = TEST,
FILENAME = 'C:\SQL2005\Snapshot\TEST_DB1200.ss' )
AS SNAPSHOT OF TEST;
GO
this database according to the filename created on 12:00, and another one I want to create at 12:15 but how to change the name of the database because I want schedule this as job on SQL Agent! Simple if I want it to run as script whenever I want it doesn't work couz it's same name of the database!
I want like this:
CREATE DATABASE TEST_DB+timestamp here+ ON
( NAME = TEST,
FILENAME = 'C:\SQL2005\Snapshot\TEST_DB+ timestamp here +.ss' )
AS SNAPSHOT OF TEST;
GO
Any chance to do it in SQL Server 2005 or maybe SQL Server 2008!?
:w00t:
May 28, 2008 at 5:20 am
Try this procedure, which I developed (I will post it also in the scripts part of this website)
ALTER procedure [dbo].[usp_create_snapshot](@databasename sysname
, @SQLInstance varchar(10) = 'MSSQL.1'
, @debug bit = 0
, @verbose bit = 1) as
/*
Nameusp_create_snapshot
authorWilfred van Dijk (www.wilfredvandijk.nl)
DescriptionCreates a snapshot of given database
Parameters@databasename
@SQLInstance
@debug
@verbose
Returnsnull - ok
1 - Snapshot not supported
Date20080422
AuthorWvDijk
Commentsinitial release
Date20080515
AuthorWvDijk
Commentsfixed backslash issue
Date20080519
AuthorWvDijk
Commentsfixed multifile issue
*/
begin
declare @SQLCmd nvarchar(max)
declare @RegPath varchar(512)
declare @DataPath varchar(256)
declare @LogicalName sysname
declare @ss_stamp char(12)
Declare @SS_DB sysname
/*
Snapshots are only supported on Enterprise editions
*/
if CAST(serverproperty('Edition') AS VARCHAR) not like 'Enterprise%'
begin
if @verbose = 1
raiserror('This SQL Server edition does not support snapshots',10,1)
return 1
end
/*
Get default datapath from registry
*/
Set@RegPath = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @SQLInstance + '\MSSQLServer'
EXECmaster..xp_regread 'HKEY_LOCAL_MACHINE', @RegPath, 'DefaultData', @value=@DataPath OUTPUT
/*
Timestamp is in format YYYYMMSSHHMI
*/
set@ss_stamp = LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ',''),12)
set@ss_db = @databasename + '_ss_' + @ss_stamp
/*
Make sure database exists ...
*/
if not exists(select 'yes' from master.sys.databases where name = @databasename and source_database_id is null)
begin
raiserror('Specified database (%s) not found.',16,1,@databasename)
return 1
end
Declare c_logicalname cursor for
selectname
frommaster.sys.master_files
wheredatabase_id = DB_ID(@Databasename)
andtype_desc = 'ROWS'
openc_logicalname
fetchnext
fromc_logicalname
into@logicalname
set@SQLCmd = 'CREATE DATABASE '+ @ss_db + ' On '+ char(13)
set@SQLCmd = @SQLCmd + '(name = '+ @logicalname +', filename = '''+ @datapath + '\' + @logicalname + '.ss'')'+ char(13)
fetchnext
fromc_logicalname
into@logicalname
while@@fetch_status = 0
begin
set@SQLCmd = @SQLCmd + ',(name = '+ @logicalname +', filename = '''+ @datapath + '\' + @logicalname + '.ss'')'+ char(13)
fetchnext
fromc_logicalname
into@logicalname
end
closec_logicalname
deallocate c_logicalname
set@SQLCmd = @SQLCmd + 'AS SNAPSHOT OF ' + @Databasename
if @verbose = 1
begin
print '- Source database : ' + @Databasename
print '- Creating snapshot : ' + @ss_db
print '- Snapshot is located in ' + char(39) + @Datapath + char(39)
end
if @debug = 1
printchar(13) + @SQLCmd + char(13)
else
exec(@SQLCmd)
end
Wilfred
The best things in life are the simple things
May 28, 2008 at 5:22 am
Sure. Works in 2005 just fine
DECLARE @TimeStamp VARCHAR(4)
SET @TimeStamp = REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':','')
EXECUTE ('CREATE DATABASE TEST_DB' + @TimeStamp + ' ON
( NAME = TEST,
FILENAME = ''C:\SQL2005\Snapshot\TEST_DB' + @TimeStamp + '.ss'' )
AS SNAPSHOT OF TEST')
Just bear in mind that multiple snapshots are likely to degrade your insert/update/delete performance.
In a test I did (on a fairly beefy server) 4 snapshots more than doubled the time necessary to delete 5000 rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 28, 2008 at 5:27 am
May 28, 2008 at 5:38 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply