Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

tables from snapshot file

2 views
Skip to first unread message

Johnson

unread,
Dec 28, 2009, 7:35:37 AM12/28/09
to
Is there any way to get list of tables from snapshot file using SMO or some
other API?

Tnx

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4721 (20091228) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com


Tibor Karaszi

unread,
Dec 29, 2009, 3:40:45 AM12/29/09
to
Can you define what you mean by "snapshot file"?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Johnson" <Joh...@gmail.com> wrote in message
news:eA70Jp7h...@TK2MSFTNGP06.phx.gbl...

Johnson

unread,
Dec 29, 2009, 5:05:37 AM12/29/09
to
We can take database snapshot in Sql Server. Like mdf file to store data, we
will have snapshot data file with extension .ss or any thing.
We use "AS SNAPSHOT OF" keyword to create database snapshot .

"Tibor Karaszi" <tibor_please.n...@hotmail.nomail.com> wrote in
message news:uOgnJKGi...@TK2MSFTNGP04.phx.gbl...

> signature database 4723 (20091228) __________


>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4723 (20091228) __________

John Bell

unread,
Dec 29, 2009, 7:56:49 AM12/29/09
to

"Johnson" <Joh...@gmail.com> wrote in message
news:OucJF6Gi...@TK2MSFTNGP05.phx.gbl...

> We can take database snapshot in Sql Server. Like mdf file to store data,
> we will have snapshot data file with extension .ss or any thing.
> We use "AS SNAPSHOT OF" keyword to create database snapshot .
>
> "Tibor Karaszi" <tibor_please.n...@hotmail.nomail.com> wrote
> in message news:uOgnJKGi...@TK2MSFTNGP04.phx.gbl...
>> Can you define what you mean by "snapshot file"?
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>>
>> "Johnson" <Joh...@gmail.com> wrote in message
>> news:eA70Jp7h...@TK2MSFTNGP06.phx.gbl...
>>> Is there any way to get list of tables from snapshot file using SMO or
>>> some other API?
>>>
>>> Tnx
>>>

A database snapshot will be transactionally consistent with the source
database therefore once a table/data is removed from the source database.
That said you can use SMO in the same way as any database to generate
scripts e.g.

the database MyAdventureworks

CREATE DATABASE [MyAdventureWorks] ON PRIMARY
( NAME = N'AdventureWorks_Data', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MyAdventureWorks_Data.mdf' )
AS SNAPSHOT OF AdventureWorks
GO

The tables can be scripted out in Powershell and SMO using:

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST"
$dbs=$s.Databases
foreach ($tables in $dbs["MyAdventureworks"].Tables)
{
$outfile = "C:\Powershell\Examples\Output Scripts\" + $tables.name +
".sql"
$tables.Script() | out-File $outfile
}

If you want other scripts look at
http://www.mssqltips.com/tip.asp?tip=1842&home

John

Johnson

unread,
Dec 29, 2009, 10:51:42 AM12/29/09
to
Thanks for your response.

We have only snapshot data file(.ss file). We dont have any connection info.

"John Bell" <jbellne...@hotmail.com> wrote in message
news:13DEDED2-7D72-484C...@microsoft.com...

> __________ Information from ESET NOD32 Antivirus, version of virus

> signature database 4725 (20091229) __________


>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4725 (20091229) __________

Tibor Karaszi

unread,
Dec 29, 2009, 12:21:14 PM12/29/09
to
The snapshot file is useless by itself. It is in no way complete, and need
to be in its original SQL Server installation, where the original database
need to be still healthy. If that were the case, then you could connect to
that SQL Server and use the snapshot database (as outlined by John).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Johnson" <Joh...@gmail.com> wrote in message

news:#tsYw7Ji...@TK2MSFTNGP04.phx.gbl...

John Bell

unread,
Dec 30, 2009, 7:32:05 AM12/30/09
to

"Johnson" <Joh...@gmail.com> wrote in message
news:%23tsYw7J...@TK2MSFTNGP04.phx.gbl...

Hi

To add to what Tibor has said.

Your .ss file is what was specified as the data file in the CREATE DATABASE
statement. It can not be detached or backed up, so without the instance
being stopped I would not be hopeful that even a normal mdf file would be
usable.

If you try to attach a copied file with sp_attach_single_file_db as
NewAdventureworks you will get an error:

Msg 3415, Level 16, State 3, Line 1
Database 'NewAdventureworks' cannot be upgraded because it is read-only or
has read-only files. Make the database or files writeable, and rerun
recovery.

Therefore it is pretty useless to you.

John

Johnson

unread,
Dec 30, 2009, 11:35:57 PM12/30/09
to
Thanks.

"John Bell" <jbellne...@hotmail.com> wrote in message

news:F8AF5D80-8D63-4820...@microsoft.com...

> __________ Information from ESET NOD32 Antivirus, version of virus

> signature database 4730 (20091230) __________


>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4730 (20091230) __________

0 new messages