Post reply

peventing accidental deletes

  • February 26, 2007 at 8:19 am

    #119067

    Is there a way to get sql server to present a message box asking if you are you sure you want to delete all the data in a table.

    also are there any 'best practises' to avoid accidental deletes.

    For example Im making a lot of delete statements like:

    del from table_name

    where item_id = 23

    but one time I executed this statement I mistakenly excuted the first line of the statment only, resulting in the loss of all the data in the table, luckily I was able to re-populate the table, but I wish to avoid that mistake in the future.

     

  • February 26, 2007 at 8:32 am

    #691783

    begin tran

    select count(*) from table_name

    del from table_name

    where item_id = 23

    select count(*) from table_name

    rollback tran

     

    Run once like this, then if everything looks fine, then just run the delete by itself.  Also you normally have to do test runs in developpement or Quality assurance environement before running the scripts in production.

  • February 26, 2007 at 9:16 am

    #691802

    thanks for that.

  • February 28, 2007 at 3:49 am

    #692252

    Hi Mark /Ninja,

    I am new to the DBA Administration and while reviewing the discussed topics, I was just trying the steps which you have specified with begin transaction and roll back up.

    When I tried for the first time, I deleted some of the data in the table, before that I used the begin tran and rolled back the data to the table again.

    After that I just tried to run it one more time, at that time suddenly I deleted the data before starting the begin tran. Now I am wondering how I could restore the deleted data back to the table.

    Please help me out...

    Thanks in Advance,

    Regards

    Senthil Kumar

  • February 28, 2007 at 5:59 am

    #692273

    Hi Senthil,

          just now i checked ur prob. As u should run the query from 'begin tran' to 'Roll back' together.anyway if you have any duplicate once in  of that table in any other database or any other networked server. then you can populate the whole data using import export wizerd. for that you right click on the databse where the duplicate table is available.then go to task and select the export data.. or else if you have a backup then easily you can racover the table.

     

    Thanks

     

    Rahul Das

     

  • February 28, 2007 at 6:11 am

    #692275

    Hi Rahul,

    Thanks for your immediate reply.  I understand that we should use from 'begin tran' to 'Roll back' before deleting the data from the table . Also I used to run the complete backup of that database, I have the back of the complete database only. But Is it is possible to restore the particular table in any other options, because I dont have any duplicate of that table in which I deleted the data.

    I see a way that, I can restore the database backup in a different name and then restore the table. Is that correct ?

    Can you tell me, Is there a way I could restore the data if I dont have the backup of the database.

    Thanks in advance...

    Regards

    Senthil Kumar

  • February 28, 2007 at 6:28 am

    #692280

    Hi Senthil,

            I can tell you that u can 1st take the full back up and then take the differential back up based on the day or weeks..

     Full backups can actually start to slow down the process a bit. Instead, after consultation with the business, we've decided to do a weekly full backup and daily differential backups. Differential backups only backup the data pages that have changed since the last full backup. Following is the SQL backup command to perform a differential backup:

    BACKUP DATABASE AdventureWorks  
TO DISK = 'C:\backups\AdventureWorks.bak' 
WITH DIFFERENTIAL;

    Now, if we had to restore this database, we'd first go to the last full backup, restore that, and then restore the differential backups in order (more on that later).

    You might have noticed, if you're trying these commands out as you go along, that we have not been changing the backup file names each time. Yet we haven't run into any errors. When running backups as we've done, SQL Server treats the file like a backup device and simply keeps appending the backups to the file.

     

    If you want to simply overwrite the existing file you'll need to modify your backup statements:

    BACKUP DATABASE Adventureworks  
TO DISK = 'C:\backups\AdventureWorks.bak'  
WITH INIT;

    There are a number of other backup options that I won't be detailing here. Read the books online to see details on BLOCKSIZE, EXPIREDATE, RETAINDAYS, PASSWORD, NAME, STATS, and so on.

    You can also run a statement that will check the integrity of a database backup. It doesn't check the integrity of the data within a backup, but it does verify that the backup is formatted correctly and accessible.

    RESTORE VERIFYONLY  
FROM DISK = 'C:\backups\Adventureworks.bak'
     

       I think you can use this method to solve this problem

    Please let me know if you get any furhter problem

    thanks

     

    Rahul das

     

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply