Database 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

SQL Server 2005 Forums

Transact-SQL (2005)

Database snapshot

Author

Topic

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2010-11-09 : 01:49:19

Hi,
Can u tel me how to do database snapshot . when i try to create with follwoing syntax its giving following error. what is that _data file where v ii find it. does it comes when v attach northwind database or v ahve to create it.
CREATE DATABASE Northwind_Snapshot1000 ON (NAME = Northwind_Data,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Snapshot\Northwind_data1000.ss' ) AS SNAPSHOT
OF Northwind
this is the error
The file 'Northwind_Data' does not exist in database 'Northwind'.

Sachin.Nand

2937 Posts

Posted - 2010-11-09 : 02:14:37

NAME = Northwind_Data.... should be Database name which I guess is Northwind.

PBUH

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-09 : 02:52:40

It's not the database name

The name (Northwind_Data) is the name of the data file for the database that you're making a snapshot of. You would find that by querying sys.database_files

--
Gail Shaw
SQL Server MVP

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2010-11-09 : 03:12:25

How to find datafile and how to create .ss file

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-09 : 03:24:33

quote:
Originally posted by yadhu_cse

How to find datafile and how to create .ss file

--How to find datafile location.

SELECT name, physical_name AS current_file_location
FROM sys.master_files where name = 'YourDBName'

--Have a look at below link for info on how to create snapshot.
http://msdn.microsoft.com/en-us/library/ms175876(v=SQL.90).aspx

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-09 : 03:47:18

quote:
Originally posted by yadhu_cse

How to find datafile and how to create .ss file

I told you.

quote:
You would find that by querying sys.database_files

--
Gail Shaw
SQL Server MVP

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2010-11-09 : 03:53:49

there i can find .mdf file not .ss file.
i,m not getting what to supply for filename

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-09 : 04:11:43

quote:
Originally posted by yadhu_cse

there i can find .mdf file not .ss file.
i,m not getting what to supply for filename

I am littlebit confused here.
You want to view existing snapshot or create a new one.

If you wish to see exisitng snapshot then
To view a database snapshot on a server
--------------------------------------------------------------------------------

1.In Object Explorer, connect to the instance of the Microsoft SQL Server Database Engine and then expand that instance.

2.Expand Databases.

3.Expand Database Snapshots, and select the snapshot you want to view.

If you wish to create a new one then you need to specify the new filename with ss extension.

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2010-11-09 : 04:59:20

thanks.

i have created sanpshot. i have deleted records from source database, when i try to restore data from snap shot its giving error.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-09 : 05:39:43

quote:
Originally posted by yadhu_cse

thanks.

i have created sanpshot. i have deleted records from source database, when i try to restore data from snap shot its giving error.

What's the error message you are getting ?

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2010-11-09 : 05:42:31

thnaks for response
i fixed it

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2010-11-09 : 07:27:11

Hi,
I am getting following error when i execute following

declare @MyDay varchar(20)
declare @query varchar(100)
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()))
print 'It is ' + @MyDay
Set @DatabaseName ='MyDB'
Set @SnapDataName='MyDB_Data'
Set @SnapshotName ='MyDB_Snapshot'+'_'+@MyDay
Set @SnapFilename ='c:\data\MyDB_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
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)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-09 : 08:25:31

What error?

--
Gail Shaw
SQL Server MVP

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2010-11-09 : 23:18:21

i am getting following error
declare @MyDay varchar(20)
declare @query varchar(100)
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()))
print 'It is ' + @MyDay
Set @DatabaseName ='MyDB'
Set @SnapDataName='MyDB_Data'
Set @SnapshotName ='MyDB_Snapshot'+'_'+@MyDay
Set @SnapFilename ='c:\data\MyDB_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
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)

for the bove

Create database MyDB_Snapshot_Wednesday on (Name = 'MyDB_Data',
'FileName='c:\data\MyDB_Data_Wednes
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'FileName='.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-10 : 02:28:10

There's a stray ' before Filename

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-11-10 : 02:35:00

Create database MyDB_Snapshot_Wednesday on (Name = 'MyDB_Data',
'FileName='c:\data\MyDB_Data_Wednes
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'FileName='.

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2010-11-10 : 04:02:48

hi,
can help me in this regard
USE [DBsnapshot]
GO
/****** Object: StoredProcedure [dbo].[databaseSnapshot] Script Date: 11/10/2010 12:20:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[databaseSnapshot]

AS
BEGIN
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)
DECLARE @Mytime VARCHAR(50)
DECLARE @Myminute VARCHaR(50)
set @Myday = (SELECT DATENAME(WEEKDAY,getdate()))
print 'It is ' + @MyDay
set @Mytime=(SELECT DATEPART(hour,GETDATE()))
set @Myminute=(SELECT DATEPART(minute,GETDATE()))
Set @DatabaseName ='MyDB'
Set @SnapDataName='MyDB'
Set @SnapshotName ='MyDB_Snapshot'+'_'+@MyDay
Set @SnapFilename ='c:\data\MyDB_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
set @query = 'Drop database '+ @SnapshotName
print @query
exec(@query)
end
SET @query='create Database ' + @SnapshotName + ' on (Name=''' +@snapDataName +''','
+N'Filename=' +'''' +@SnapFilename +''')' +'AS SNAPSHOT of ' +@databasename
print @query
exec(@query)
END
exec databaseSnapshot

when i run above m getting following error

Msg 217, Level 16, State 1, Procedure databaseSnapshot, Line 23
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
what is the Problem with above code

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-10 : 07:02:34

Try

quote:

SET @query='create Database ' + @SnapshotName + ' on (Name=''' +@snapDataName +''','
+N'Filename=' +'''' +@SnapFilename +''')' +'AS SNAPSHOT of ' +@databasename
print @query
exec(@query)
END

GO

exec databaseSnapshot

Please don't post same query on existing and new thread.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=152772

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources