SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Hourly snapshot
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

satchi
Starting Member

Australia
4 Posts

Posted - 06/03/2013 :  20:05:22  Show Profile  Reply with Quote
HI,

How can i create hourly snapshot on SQL data base?

Thanks
satchi

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 06/03/2013 :  21:39:05  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

satchi
Starting Member

Australia
4 Posts

Posted - 06/03/2013 :  21:57:33  Show Profile  Reply with Quote
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
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 06/03/2013 :  23:06:12  Show Profile  Visit russell's Homepage  Reply with Quote
This sounds like a bad idea. What will you do with the snapshots? I'm betting backups is what you really want.
Go to Top of Page

satchi
Starting Member

Australia
4 Posts

Posted - 06/04/2013 :  00:47:24  Show Profile  Reply with Quote
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...

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/04/2013 :  00:52:06  Show Profile  Reply with Quote
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
Go to Top of Page

satchi
Starting Member

Australia
4 Posts

Posted - 06/04/2013 :  00:58:07  Show Profile  Reply with Quote
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000