Hourly snapshot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Hourly snapshot

Author

Topic

satchi
Starting Member

4 Posts

Posted - 2013-06-03 : 20:05:22

HI,

How can i create hourly snapshot on SQL data base?

Thanks
satchi

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-06-03 : 21:39:05

Have a look here: Create a Database Snapshot.

You can set up a SQL Agent job to create a snapshot every hour, and drop old ones.

Dynamically assign a meaningful name, like myDatabaseSnapshot0100, myDatabaseSnapshot0200 etc.

satchi
Starting Member

4 Posts

Posted - 2013-06-03 : 21:57:33

Hi ,

I have gone through this.. By the way i am not a expert in SQL, I need to create hourly file and keep them for a week.

So totally 24*7 files should be there. with date and time..

can you guide me or start some where to write the query

Thanks
satchi

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-06-03 : 23:06:12

This sounds like a bad idea. What will you do with the snapshots? I'm betting backups is what you really want.

satchi
Starting Member

4 Posts

Posted - 2013-06-04 : 00:47:24

Hi,

Just for a purpose of if in case anything goes wrong we can restore, yes backup can do the same.

I though snapshot will be less capacity than backup. but seems snapshot also same size of the data base...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 00:52:06

quote:
Originally posted by satchi

Hi,

Just for a purpose of if in case anything goes wrong we can restore, yes backup can do the same.

I though snapshot will be less capacity than backup. but seems snapshot also same size of the data base...

from books online

Database snapshots are dependent on the source database. Therefore, using database snapshots for reverting a database is not a substitute for your backup and restore strategy. Performing all your scheduled backups remains essential. If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

satchi
Starting Member

4 Posts

Posted - 2013-06-04 : 00:58:07

Thanks mate.....

declare @MyDay varchar(20)
declare @query varchar(1000)
declare @DatabaseName varchar(128)
declare @snapshotName varchar(128)
declare @snapDataName varchar(128)
declare @snapFileName varchar(128)
declare @snapFilePath varchar(128)

set @Myday = (Select datename(weekday,getdate())) + '_HOUR_' + (Select datename(HOUR,getdate()))
print 'It is ' + @MyDay
Set @DatabaseName ='Record'
Set @SnapDataName='Record'
Set @SnapshotName ='Record_Snapshot'+'_'+@MyDay
Set @SnapFilename ='e:\Snapshot\Record_Data'+'_'+@MyDay+'.ss'
Print 'Snapshot name is ' +@SnapshotName
select * from sys.databases where source_database_id =db_id(@databasename) and name = @SnapshotName
if @@rowcount <>0
begin
Print 'Delete Database ' +@SnapshotName

set @query = 'Drop database ['+ @SnapshotName +']'
print @query
exec(@query)
end

set @query ='Create database ['
+ @SnapshotName
+ '] on (Name = ['
+@snapDataName +'], FileName='''
+@SnapFilename +''') AS SNAPSHOT of [' + @databasename+'];'

print @query
exec(@query)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources