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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy