Showing posts with label deleted. Show all posts
Showing posts with label deleted. Show all posts

Monday, March 19, 2012

How can I make a "template" of my database?

I have a situation where my database might be deleted every so oftern and I
would like to find a way to "import" sort of a "blank template" of the
original database. Is there a way to do this?
I realize that I could have my program code just create another database,
but that's kind of a hastle, so I was looking for an easier way.
res0gyio@.verizon.netIn Enterprise Manager, right click on the database, all tasks - Generate
script - then select the appropiate options you would like (I addtion to
selecting all your tables on the first tab, I suggest Indexs & defaults on
the last tab)
Mo
"michaaal" <res0gyio@.verizon.net> wrote in message
news:uBiT7mwCEHA.1600@.tk2msftngp13.phx.gbl...
> I have a situation where my database might be deleted every so oftern and
I
> would like to find a way to "import" sort of a "blank template" of the
> original database. Is there a way to do this?
> I realize that I could have my program code just create another database,
> but that's kind of a hastle, so I was looking for an easier way.
> res0gyio@.verizon.net
>|||Almost forgot to mention, you can run the script in Query Analyzer to
re-structure your databases (it'll also wipe it out first if you selected
the "drop table" option while creating the script)
Mo
"michaaal" <res0gyio@.verizon.net> wrote in message
news:uBiT7mwCEHA.1600@.tk2msftngp13.phx.gbl...
> I have a situation where my database might be deleted every so oftern and
I
> would like to find a way to "import" sort of a "blank template" of the
> original database. Is there a way to do this?
> I realize that I could have my program code just create another database,
> but that's kind of a hastle, so I was looking for an easier way.
> res0gyio@.verizon.net
>|||A lots of tools and options to generate DDL scripts:
http://www.karaszi.com/sqlserver/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"michaaal" <res0gyio@.verizon.net> wrote in message
news:uBiT7mwCEHA.1600@.tk2msftngp13.phx.gbl...
> I have a situation where my database might be deleted every so oftern and
I
> would like to find a way to "import" sort of a "blank template" of the
> original database. Is there a way to do this?
> I realize that I could have my program code just create another database,
> but that's kind of a hastle, so I was looking for an easier way.
> res0gyio@.verizon.net
>

Friday, February 24, 2012

How can I get back the lost view?

Hi,

I am a newbie working on MS Sql Server 2000 for a while. I accidentally deleted a view through Query Analyzer and want to get it back. All data are backed-up every day but there are a lot of red tapes I have to go through in order to draw the lost view from the backup. Indeed, a different division is taking care of backups in our organization and they don't want to spend time on my issue.

I'm wondering if there is an automatic logging capability of sql server showing modified/ deleted/ updated data objects on daily basis with their contents that can be accessed later on. Or is there another recovery mechanism that can be used to get back the lost view?

Thanks for your attention to this matter,

Batuhan

Hi,

as this is your first post in here, welcome to the groups :-)

Actions are logged within the tranaction log of SQL Server, but if you did not change the view (with an alter or create command) there will be no information in the log to rely on, in addition you would need to have the last backup for applying the transaction (log) to this version. i guess you will have to go the hard way and let the backup division restore the database for you to an older version. Thats why I keep a script of my database as a "small" backup to restore the object that are just scriptable Perhaps you should add this as a best practise to your daily work.


HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I'd like to second Jens's suggestion to keep track of the T-SQL that was used to generate any database object - think of it as the database source code. You can manage that as you manage your application code, using SourceSafe, for example.

Thanks
Laurentiu

|||

Thanks for replying my post.

I'm really curious about the content of transaction-log. Does it record every change we made in the database or just 'transactions'?
Does it cover logging of update, insert, delete operations that were executed in the database? Another question is how I can view the transaction log. Do you know any free software tool to read transaction log?

Batuhan

|||Statements are wrapped in transactions to ensure the ACID of databases. You can either use explicit transactions using BEGIN TRANSACTIONS or the appropiate functionality of the provider like the ADO.NET implementation or implicit while doing a regular DML operation which is not wrapped up in a explicit transaction. The transactionlog cannot be viewed easily, there are special (non-free tools) for viewing these like this from L**igent (you will propably find the name searching on the internet).

There is an undocumented way to read the log, but this is sort a cryptic to investigate:

DBCC Log('tempdb',1) --use the appopiate parameters to read the log

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Sunday, February 19, 2012

How can I get Actual Operation Cost from Actual Execution Plan?

I have a view in SQLServer 2005. It took 30 sec. to finish. Then I deleted 4500 records from one table that is used in view. It took 90 sec. to finish now. I did a comparison on Actual Execution Plan between before I deleted data and after I deleted data, they are almost same, only different is Actual Number Rows become less after deleted data. So, I wonder why data become less but time become more. When I look closely on the Actual Execution Plan, the ridiculous thing is, there are only Estimated Operation Cost on each step, no Actual Operation Cost. I guess there are something wrong with optimizer because reuse same Execution Plan, but how can I tell which step wrong without Actual Operation Cost.

Thanks!

Henry

There is only Estimated Operation Cost, since the actual operation cost will change from time to time. You may use DBCC to rearange the page allocation, to see if the performance can be improved. However, the view tooks 30 sec. to run does not look good. I would check the view first, to see if there is any thing wrong.|||

Thank you for the response. Because the view is using in production site, we are not able to do tuning on it. We need to figure out why there are big different after deleted records and explain to client. Do you know any way to show us which step or operation spent more time than before. Thanks!!

How can I generate INSERT statements from Management Studio

Hello All,

How are you guys doing? Hope all is well.

This is my problem...I accidentally deleted a bunch of my SQL scripts and would like to generate the INSERT statements from Management Studio. I am currently using SQLExpress. Is there a way I can accomprish my task? Kindly advise.

Thanks a bunch!

Mohammed

No, you can′t SSMS does not support that yet. You can create the statements using the script from here:

http://vyaskn.tripod.com/code.htm#inserts

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

hi Mohammed,

in addition to Jens, you can have a look at http://www.karaszi.com/sqlserver/info_generate_script.asp, where "several" option are listed.. for your convenience, you can perhaps try a free prj of mine, amInsert, available for free at the link following my sign..

regards

|||

Hello Jens!

Good to hear from you and thank you for responding to my post. I really appreciate your assistance. I used to live in Germany myself...in Bonn....and I had an amazing time...and I would love to go back there!!!

Thanks again,

Mohammed

|||

Hello Jens,

Sorry to bug you again. Would I be able to generate the INSERT statements if I was using full-blown SQL Server 2005?...or does this have nothing to do with the fact that I am using SQL Server Express?

Thanks,

Mohammed

|||

hi Mohammed,

even SQL Server Management Studio "full version" does not provide that feature..

regards

How can I generate INSERT statements from Management Studio

Hello All,

How are you guys doing? Hope all is well.

This is my problem...I accidentally deleted a bunch of my SQL scripts and would like to generate the INSERT statements from Management Studio. I am currently using SQLExpress. Is there a way I can accomprish my task? Kindly advise.

Thanks a bunch!

Mohammed

No, you can′t SSMS does not support that yet. You can create the statements using the script from here:

http://vyaskn.tripod.com/code.htm#inserts

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

hi Mohammed,

in addition to Jens, you can have a look at http://www.karaszi.com/sqlserver/info_generate_script.asp, where "several" option are listed.. for your convenience, you can perhaps try a free prj of mine, amInsert, available for free at the link following my sign..

regards

|||

Hello Jens!

Good to hear from you and thank you for responding to my post. I really appreciate your assistance. I used to live in Germany myself...in Bonn....and I had an amazing time...and I would love to go back there!!!

Thanks again,

Mohammed

|||

Hello Jens,

Sorry to bug you again. Would I be able to generate the INSERT statements if I was using full-blown SQL Server 2005?...or does this have nothing to do with the fact that I am using SQL Server Express?

Thanks,

Mohammed

|||

hi Mohammed,

even SQL Server Management Studio "full version" does not provide that feature..

regards

How can i fix sql2000 virtual server

It's too bad day. I deleted "sql server/sql agent/fulltext" three resources
in the cluster management.
How can i fix it? i try to reinstall virtual server,but it says : the
default instance was existed.Help me plz!
Hi
Add a resource by running the "Configure Application" wizard from the menu.
See Windows BOL for more information. The SQL Server setup does this
automaticcaly for you.
Regards
Mike
"luk" wrote:

> It's too bad day. I deleted "sql server/sql agent/fulltext" three resources
> in the cluster management.
> How can i fix it? i try to reinstall virtual server,but it says : the
> default instance was existed.Help me plz!
>
>
|||Hi
I added a resources(sql server) by "Configure Application" .But it can not
online,it failed. I think it maybe do something in the sql server's config
or regedit.I dont know how to configure in detail .
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> д?:EA27AF5A-3181-4FD5-8B22-8B83F9A2DEFC@.microsoft.com...[vbcol=seagreen]
> Hi
> Add a resource by running the "Configure Application" wizard from the
> menu.
> See Windows BOL for more information. The SQL Server setup does this
> automaticcaly for you.
> Regards
> Mike
> "luk" wrote:
|||Hi
Open a case with Microsoft PSS. They should be able to help you.
Regards
Mike
"luk" wrote:

> Hi
> I added a resources(sql server) by "Configure Application" .But it can not
> online,it failed. I think it maybe do something in the sql server's config
> or regedit.I dont know how to configure in detail .
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> D′è????¢D???:EA27AF5A-3181-4FD5-8B22-8B83F9A2DEFC@.microsoft.com...
>
>