Absurd project: deleted

Showing posts with label deleted. Show all posts

Friday, March 23, 2012

Help creating a DELETE trigger.

I need to create a trigger that will run whenever a record is deleted. I
want the trigger to delete records from table2 where the value in the ID
field matches the value in the ID field of the record being deleted from
table1.
I'm new to triggers and am not sure how to set this up.
Thanks,
JohnTry,
create trigger tr_table1_del on table1
for delete
as
set nocount on
if exists(select * from deleted as d inner join table2 as t on d.[id] = t[id])
begin
delete table2
where exists(select * from deleted ad d where d.[id] = table2.[id])
if @.@.error != 0
begin
rollback transaction
raiserror('Error deleting rows in table2.', 16, 1)
return
end
end
go
AMB
"John Piotrowski" wrote:

> I need to create a trigger that will run whenever a record is deleted. I
> want the trigger to delete records from table2 where the value in the ID
> field matches the value in the ID field of the record being deleted from
> table1.
> I'm new to triggers and am not sure how to set this up.
> Thanks,
>
> John

Wednesday, March 21, 2012

Help Altering Database Table from NULL to NOT NULL

I would drop and add the table but the data can't be deleted. So if anyone could help with the statement it would be greatly appreciated. ThanksYou alter columns to NOT NULL, not tables. Can you explain why you're trying to do this? What are you going to put in all the columns that have NULL? Why does the table have NULL in the first place. You should post what the table looks like with a couple sample rows.|||My apologies I meant a column. Currently a remote table that I'm adding a new column to where the people there have no idea how to use SQL Server. I've made a new SQL script to add a new column and have to set the column to NULL since its a new column being added to an existing table. After the apropriate data is in the new column I need to set the column to NOT NULL for future entries.....|||Could you add the column allowed to have NULL, then update the table where the column get's some value, then alter the table setting the column to NOT NULL?|||Thats pretty much what I'm trying to do but via scripts. Circumstances keep me from doing this manually so I'm trying to get it setup via a script.....|||so at which of the three steps are you stuck?|||Are you just trying to change the column null to not null if something is meet?|||if exists (select 1 from your_table where new_column is null)
raiserror ('You can't make column NON-NULLABLE if there is at least 1 NULL in it!, 15, 1)
else
alter table your_table alter column new_column <data_type> not null

Monday, March 19, 2012

HELP : all records of one table have been deleted!

For a reason that we don't know, one table consisting of about 1200 rows in
SQL Server 2005 Express SP2 shows to be empty at once. Since all records
were addded within this day, there is no suitable back-up of a couple of
hours ago, I don't know a way to restore the records.
1) Is this a known bug of SQL Server 2005 SP2?
1) Is there a way to find out the reason why this happened?
2) Is there a way to restore the deleted rows?
3) How can I prevent this to happen again?"Oscar" <oku@.xs4all.nl> wrote in message
news:udXxjALcHHA.5052@.TK2MSFTNGP06.phx.gbl...
> For a reason that we don't know, one table consisting of about 1200 rows
> in SQL Server 2005 Express SP2 shows to be empty at once. Since all
> records were addded within this day, there is no suitable back-up of a
> couple of hours ago, I don't know a way to restore the records.
> 1) Is this a known bug of SQL Server 2005 SP2?
> 1) Is there a way to find out the reason why this happened?
> 2) Is there a way to restore the deleted rows?
> 3) How can I prevent this to happen again?
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:udXxjALcHHA.5052@.TK2MSFTNGP06.phx.gbl...
> For a reason that we don't know, one table consisting of about 1200 rows
> in SQL Server 2005 Express SP2 shows to be empty at once. Since all
> records were addded within this day, there is no suitable back-up of a
> couple of hours ago, I don't know a way to restore the records.
> 1) Is this a known bug of SQL Server 2005 SP2?
Not that I've heard.
> 1) Is there a way to find out the reason why this happened?
Possibly, but may not be entirely worth the effort.
> 2) Is there a way to restore the deleted rows?
MAYBE!
If you are in full-logging and not simple logging, you may be able to do the
following:
I assume you have a full backup from say last night?
Take a Transaction Log backup now. (I'd probably add a NO_TRUNCATE option so
that you don't truncate the log, just in case).
Anyway, do a RESTORE from the full backup to a different database name WITH
NORECOVERY.
Then a RESTORE LOG using the backup you just made and then WITH STOPATMARK =<time>, STANDBY=filename.
This will restore the log to whatever time you want. The STANDBY will allow
you to put the database into read-only mode while you then do a select on
the table.
If the data is there, you can then select it back into your live database.
If not, do a new RESTORE LOG with an earlier STOPATMARK, etc.
HOWEVER, if you don't have FULL Recovery enabled, you're probably out of
luck.
If you need more help, let me know via email or here.
> 3) How can I prevent this to happen again?
Security.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi Greg,
I am not that familiar with these items.
a) how can I find whether the configuration is full-logging or simple
logging?
b) what do you mean with 'Take a Transaction Log backup now'? How can I find
this?
c) how can I do a RESTORE LOG?
thanks for your help.
Oscar
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
bericht news:%23qklnHLcHHA.4032@.TK2MSFTNGP02.phx.gbl...
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:udXxjALcHHA.5052@.TK2MSFTNGP06.phx.gbl...
>> For a reason that we don't know, one table consisting of about 1200 rows
>> in SQL Server 2005 Express SP2 shows to be empty at once. Since all
>> records were addded within this day, there is no suitable back-up of a
>> couple of hours ago, I don't know a way to restore the records.
>> 1) Is this a known bug of SQL Server 2005 SP2?
> Not that I've heard.
>
>> 1) Is there a way to find out the reason why this happened?
> Possibly, but may not be entirely worth the effort.
>> 2) Is there a way to restore the deleted rows?
> MAYBE!
> If you are in full-logging and not simple logging, you may be able to do
> the following:
> I assume you have a full backup from say last night?
> Take a Transaction Log backup now. (I'd probably add a NO_TRUNCATE option
> so that you don't truncate the log, just in case).
> Anyway, do a RESTORE from the full backup to a different database name
> WITH NORECOVERY.
> Then a RESTORE LOG using the backup you just made and then WITH STOPATMARK
> = <time>, STANDBY=filename.
> This will restore the log to whatever time you want. The STANDBY will
> allow you to put the database into read-only mode while you then do a
> select on the table.
> If the data is there, you can then select it back into your live database.
> If not, do a new RESTORE LOG with an earlier STOPATMARK, etc.
> HOWEVER, if you don't have FULL Recovery enabled, you're probably out of
> luck.
> If you need more help, let me know via email or here.
>> 3) How can I prevent this to happen again?
> Security.
>>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:%23YSytULcHHA.4012@.TK2MSFTNGP03.phx.gbl...
> Hi Greg,
> I am not that familiar with these items.
> a) how can I find whether the configuration is full-logging or simple
> logging?
> b) what do you mean with 'Take a Transaction Log backup now'? How can I
> find this?
> c) how can I do a RESTORE LOG?
> thanks for your help.
> Oscar
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
> bericht news:%23qklnHLcHHA.4032@.TK2MSFTNGP02.phx.gbl...
>> "Oscar" <oku@.xs4all.nl> wrote in message
>> news:udXxjALcHHA.5052@.TK2MSFTNGP06.phx.gbl...
>> For a reason that we don't know, one table consisting of about 1200 rows
>> in SQL Server 2005 Express SP2 shows to be empty at once. Since all
>> records were addded within this day, there is no suitable back-up of a
>> couple of hours ago, I don't know a way to restore the records.
>> 1) Is this a known bug of SQL Server 2005 SP2?
>> Not that I've heard.
>>
>> 1) Is there a way to find out the reason why this happened?
>> Possibly, but may not be entirely worth the effort.
>> 2) Is there a way to restore the deleted rows?
>> MAYBE!
>> If you are in full-logging and not simple logging, you may be able to do
>> the following:
>> I assume you have a full backup from say last night?
>> Take a Transaction Log backup now. (I'd probably add a NO_TRUNCATE option
>> so that you don't truncate the log, just in case).
>> Anyway, do a RESTORE from the full backup to a different database name
>> WITH NORECOVERY.
>> Then a RESTORE LOG using the backup you just made and then WITH
>> STOPATMARK = <time>, STANDBY=filename.
>> This will restore the log to whatever time you want. The STANDBY will
>> allow you to put the database into read-only mode while you then do a
>> select on the table.
>> If the data is there, you can then select it back into your live
>> database.
>> If not, do a new RESTORE LOG with an earlier STOPATMARK, etc.
>> HOWEVER, if you don't have FULL Recovery enabled, you're probably out of
>> luck.
>> If you need more help, let me know via email or here.
>> 3) How can I prevent this to happen again?
>> Security.
>>
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>>
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:%23YSytULcHHA.4012@.TK2MSFTNGP03.phx.gbl...
> Hi Greg,
> I am not that familiar with these items.
>
Ok, that'll make this a bit tougher, but let's see what we can do.
> a) how can I find whether the configuration is full-logging or simple
> logging?
Hmm, since you're running Express, you'll have to use SQLCMD to connect to
the database.
So, connect to your database and then type:
SELECT DATABASEPROPERTY('databasename', 'IsTruncLog');
GO
Let me know if this returns a 0 or a 1.
(You want a 0).
If this is 1, then there's very little if anything that can be done.
If it's 0, we might be able to help.
> b) what do you mean with 'Take a Transaction Log backup now'? How can I
> find this?
> c) how can I do a RESTORE LOG?
> thanks for your help.
> Oscar
>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Meanwhile I've already started the first step to do a transaction log
backup.
However it shows now '(Restoring)' after the database name instead of the
back up.
It has run for almost an hour now and I don't know how long this will last.
I've also looked into another SQL server where almost the same database
resides for testing purposes.
After typing your selection query it shows one record with two fields. In
the first field it shows a '1' and in the second field it shows a 'NULL'
Oscar
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
bericht news:umc6evLcHHA.4012@.TK2MSFTNGP03.phx.gbl...
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:%23YSytULcHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> Hi Greg,
>> I am not that familiar with these items.
> Ok, that'll make this a bit tougher, but let's see what we can do.
>
>> a) how can I find whether the configuration is full-logging or simple
>> logging?
> Hmm, since you're running Express, you'll have to use SQLCMD to connect to
> the database.
> So, connect to your database and then type:
> SELECT DATABASEPROPERTY('databasename', 'IsTruncLog');
> GO
> Let me know if this returns a 0 or a 1.
> (You want a 0).
> If this is 1, then there's very little if anything that can be done.
> If it's 0, we might be able to help.
>
>> b) what do you mean with 'Take a Transaction Log backup now'? How can I
>> find this?
>> c) how can I do a RESTORE LOG?
>> thanks for your help.
>> Oscar
>>
>>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:uNfZAOMcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Meanwhile I've already started the first step to do a transaction log
> backup.
> However it shows now '(Restoring)' after the database name instead of the
> back up.
> It has run for almost an hour now and I don't know how long this will
> last.
> I've also looked into another SQL server where almost the same database
> resides for testing purposes.
> After typing your selection query it shows one record with two fields. In
> the first field it shows a '1' and in the second field it shows a 'NULL'
> Oscar
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
> bericht news:umc6evLcHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> "Oscar" <oku@.xs4all.nl> wrote in message
>> news:%23YSytULcHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> Hi Greg,
>> I am not that familiar with these items.
>>
>> Ok, that'll make this a bit tougher, but let's see what we can do.
>>
>> a) how can I find whether the configuration is full-logging or simple
>> logging?
>> Hmm, since you're running Express, you'll have to use SQLCMD to connect
>> to the database.
>> So, connect to your database and then type:
>> SELECT DATABASEPROPERTY('databasename', 'IsTruncLog');
>> GO
>> Let me know if this returns a 0 or a 1.
>> (You want a 0).
>> If this is 1, then there's very little if anything that can be done.
>> If it's 0, we might be able to help.
>>
>> b) what do you mean with 'Take a Transaction Log backup now'? How can I
>> find this?
>> c) how can I do a RESTORE LOG?
>> thanks for your help.
>> Oscar
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>>
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:uNfZAOMcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Meanwhile I've already started the first step to do a transaction log
> backup.
> However it shows now '(Restoring)' after the database name instead of the
> back up.
> It has run for almost an hour now and I don't know how long this will
> last.
> I've also looked into another SQL server where almost the same database
> resides for testing purposes.
> After typing your selection query it shows one record with two fields. In
> the first field it shows a '1' and in the second field it shows a 'NULL'
> Oscar
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
> bericht news:umc6evLcHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> "Oscar" <oku@.xs4all.nl> wrote in message
>> news:%23YSytULcHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> Hi Greg,
>> I am not that familiar with these items.
>>
>> Ok, that'll make this a bit tougher, but let's see what we can do.
>>
>> a) how can I find whether the configuration is full-logging or simple
>> logging?
>> Hmm, since you're running Express, you'll have to use SQLCMD to connect
>> to the database.
>> So, connect to your database and then type:
>> SELECT DATABASEPROPERTY('databasename', 'IsTruncLog');
>> GO
>> Let me know if this returns a 0 or a 1.
>> (You want a 0).
>> If this is 1, then there's very little if anything that can be done.
>> If it's 0, we might be able to help.
>>
>> b) what do you mean with 'Take a Transaction Log backup now'? How can I
>> find this?
>> c) how can I do a RESTORE LOG?
>> thanks for your help.
>> Oscar
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>>
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:uNfZAOMcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Meanwhile I've already started the first step to do a transaction log
> backup.
If it's showing a 1, then it's doing SIMPLE RECOVERY mode and the log gets
truncated at each checkpoint so you're probably out of luck.
> However it shows now '(Restoring)' after the database name instead of the
> back up.
Can you type exactly what commands you used.
A BACKUP should NOT in any way change a database name to "restoring" so I
suspect you're doing something else here.
> It has run for almost an hour now and I don't know how long this will
> last.
> I've also looked into another SQL server where almost the same database
> resides for testing purposes.
> After typing your selection query it shows one record with two fields. In
> the first field it shows a '1' and in the second field it shows a 'NULL'
> Oscar
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||These are the steps :
Tasks -> Back-up
Configured : Backup type : Transaction Log
Options : Transaction Log : Back up the tail of the log and leave the
database in the restoring state (in order to deselect the option 'Trancate
the transaction log' as you advised me.
Then pressed OK after which is shows now 'Restoring'.
Since the mdf file is about 50 MB and the ldf file is about 23 MB only, I
think the process takes too much time (about three hours now), it could have
been crashed. Task Manager doesn't show any noticeable activity. Can/should
I stop this proces now?
with respect to the logging setting, the results of the query look like the
'1' responds to a record number and the second field is the value we're
looking for and it's 'null'.
Oscar
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
bericht news:eQ8B2gMcHHA.1508@.TK2MSFTNGP06.phx.gbl...
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:uNfZAOMcHHA.1244@.TK2MSFTNGP04.phx.gbl...
>> Meanwhile I've already started the first step to do a transaction log
>> backup.
> If it's showing a 1, then it's doing SIMPLE RECOVERY mode and the log gets
> truncated at each checkpoint so you're probably out of luck.
>
>> However it shows now '(Restoring)' after the database name instead of the
>> back up.
> Can you type exactly what commands you used.
> A BACKUP should NOT in any way change a database name to "restoring" so I
> suspect you're doing something else here.
>
>> It has run for almost an hour now and I don't know how long this will
>> last.
>> I've also looked into another SQL server where almost the same database
>> resides for testing purposes.
>> After typing your selection query it shows one record with two fields. In
>> the first field it shows a '1' and in the second field it shows a 'NULL'
>> Oscar
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:OY2Xv6McHHA.4488@.TK2MSFTNGP03.phx.gbl...
> These are the steps :
> Tasks -> Back-up
> Configured : Backup type : Transaction Log
> Options : Transaction Log : Back up the tail of the log and leave the
> database in the restoring state (in order to deselect the option 'Trancate
> the transaction log' as you advised me.
> Then pressed OK after which is shows now 'Restoring'.
> Since the mdf file is about 50 MB and the ldf file is about 23 MB only, I
> think the process takes too much time (about three hours now), it could
> have been crashed. Task Manager doesn't show any noticeable activity.
> Can/should I stop this proces now?
Ok. Sounds like you're using the GUI. In cases like this I REALLY REALLY
recommend you don't and you execute the commands by hand. I'm not 100% sure
what GUI is doing under the covers. (Though I was able to replicate what you
did with the GUI and cause the same issue. And telling it to generate the
script, I can see what it was doing.)
Anyway, my guess is it completed the backup and you've covered the dialog
box with the OK button. It should take only a minute or two to do that
backup (or error out if it can't.)
> with respect to the logging setting, the results of the query look like
> the '1' responds to a record number and the second field is the value
> we're looking for and it's 'null'.
I'm not sure why you're getting two columns. You should be getting one.
Anyway, before we go further, did you have an existing full backup from last
night (or rather, what is your last full backup?)
> Oscar
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi Greg,
my last full back up is of 2 days ago.
Any idea how I can stop or kill the (Restoring)?
Oscar
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
bericht news:OwwZnONcHHA.4216@.TK2MSFTNGP02.phx.gbl...
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:OY2Xv6McHHA.4488@.TK2MSFTNGP03.phx.gbl...
>> These are the steps :
>> Tasks -> Back-up
>> Configured : Backup type : Transaction Log
>> Options : Transaction Log : Back up the tail of the log and leave the
>> database in the restoring state (in order to deselect the option
>> 'Trancate the transaction log' as you advised me.
>> Then pressed OK after which is shows now 'Restoring'.
>> Since the mdf file is about 50 MB and the ldf file is about 23 MB only, I
>> think the process takes too much time (about three hours now), it could
>> have been crashed. Task Manager doesn't show any noticeable activity.
>> Can/should I stop this proces now?
> Ok. Sounds like you're using the GUI. In cases like this I REALLY REALLY
> recommend you don't and you execute the commands by hand. I'm not 100%
> sure what GUI is doing under the covers. (Though I was able to replicate
> what you did with the GUI and cause the same issue. And telling it to
> generate the script, I can see what it was doing.)
> Anyway, my guess is it completed the backup and you've covered the dialog
> box with the OK button. It should take only a minute or two to do that
> backup (or error out if it can't.)
>
>> with respect to the logging setting, the results of the query look like
>> the '1' responds to a record number and the second field is the value
>> we're looking for and it's 'null'.
> I'm not sure why you're getting two columns. You should be getting one.
> Anyway, before we go further, did you have an existing full backup from
> last night (or rather, what is your last full backup?)
>
>
>> Oscar
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:e1kYIhNcHHA.4656@.TK2MSFTNGP06.phx.gbl...
> Hi Greg,
> my last full back up is of 2 days ago.
> Any idea how I can stop or kill the (Restoring)?
> Oscar
>
Can you email me at mooregr (at) greenms.com We can probably do this faster
off line.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

HELP : all records of one table have been deleted!

Monday, March 12, 2012

HELP ! Production down and backups not restoring properly!

I was mistakenly logged into the wrong server this morning and deleted all
of our product related information!!
I've been doing weekly full backups with hourly log file backups.
I have 2 questions...
1. The most pressing first. My weekly backup happens every Saturday
morning at 2am. My hourly log backups stop between midnight and begin again
at 6:15am Saturday. When I restore the full backup from last saturday it
restores fine. When I go to restore the first log it tells me the log is
too recent and there is an earlier one with LSN###....
I'm really stuck here...there is absolutely no way that there could be an
earlier log. I even tried a RESTORE LOG using the full backup to get the
log from there and it gives the same message.
2. When doing a restore through the SMS after selecting a file off disk you
get the backup sets in that file with checkboxes to select the backupset to
restore. My full backups do not show this. The list is empty so I cannot
go on. The restore seems successful by script. My script statements
originated from the "Generate Script" button when doing backups through the
interface. Am I missing something here? Below is a sample backup
statement:
BACKUP DATABASE [Products]
FILEGROUP = N'PRIMARY'
TO DISK = @.strBackupFile
WITH NOFORMAT, NOINIT,
NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
ANY HELP IS ***GREATLY*** APPRECIATED!!!Actually now I'm seeing after restoring the full backup WITH RECOVERY it
still shows the database in a recovering state and it cannot be accessed!!
How can this happen? What have I done wrong here?
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||And while on the topic of backups, I setup this process based on input from
this group. It just seems that it ends up having an inordinate amount of
files to have to restore to get back online. Wouldn't it be better to just
do differentials hourly instead of log backups? Then in a failure you'd
never have more than 2 files to restore.
Just wondering.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||> Wouldn't it be better to just do differentials hourly instead of log backu
ps?
You could do that, but log backups has lot of advantages to diff backups. Th
ese includes
Point in time restore.
Ability to backup log if database becomes toast (zero data loss)
Ability to backup log, and restore the most recent db backup and all subsequ
ent log backup sin case
you get a corruption of the database.
Etc.
In short, log backups has so many advantages so you don't want to trade it f
or diff backups just for
convenience. But sometimes you want to do both db, diff *and* log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OnZRT6xEHHA.4740@.TK2MSFTNGP03.phx.gbl...
> And while on the topic of backups, I setup this process based on input fro
m this group. It just
> seems that it ends up having an inordinate amount of files to have to rest
ore to get back online.
> Wouldn't it be better to just do differentials hourly instead of log backu
ps? Then in a failure
> you'd never have more than 2 files to restore.
> Just wondering.
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>|||> Actually now I'm seeing after restoring the full backup WITH RECOVERY it still shows the d
atabase
> in a recovering state and it cannot be accessed!!
Perhaps just a refresh problem in your GUI?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OppZhXxEHHA.3224@.TK2MSFTNGP04.phx.gbl...
> Actually now I'm seeing after restoring the full backup WITH RECOVERY it s
till shows the database
> in a recovering state and it cannot be accessed!! How can this happen? Wh
at have I done wrong
> here?
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>|||> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My
hourly
> log backups stop between midnight and begin again at 6:15am Saturday. Whe
n I restore the full
> backup from last saturday it restores fine. When I go to restore the firs
t log it tells me the
> log is too recent and there is an earlier one with LSN###....
You need to hunt down that log backup. You can check the backup history tabl
es in the msdb database,
which can give you a clue where it is.

> 2. When doing a restore through the SMS after selecting a file off disk y
ou get the backup sets
> in that file with checkboxes to select the backupset to restore. My full
backups do not show
> this. The list is empty so I cannot go on.
I suggest you read up on the RESTORE command and use RESTORE HEADERONLY and
RESTORE FILELISTONLY so
you can manage a restore when the GUI is acting up on you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
of our product related
>information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday mor
ning at 2am. My hourly
> log backups stop between midnight and begin again at 6:15am Saturday. Whe
n I restore the full
> backup from last saturday it restores fine. When I go to restore the firs
t log it tells me the
> log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
earlier log. I even
> tried a RESTORE LOG using the full backup to get the log from there and it
gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk y
ou get the backup sets
> in that file with checkboxes to select the backupset to restore. My full
backups do not show
> this. The list is empty so I cannot go on. The restore seems successful
by script. My script
> statements originated from the "Generate Script" button when doing backups
through the interface.
> Am I missing something here? Below is a sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>

HELP ! Production down and backups not restoring properly!

I was mistakenly logged into the wrong server this morning and deleted all
of our product related information!!
I've been doing weekly full backups with hourly log file backups.
I have 2 questions...
1. The most pressing first. My weekly backup happens every Saturday
morning at 2am. My hourly log backups stop between midnight and begin again
at 6:15am Saturday. When I restore the full backup from last saturday it
restores fine. When I go to restore the first log it tells me the log is
too recent and there is an earlier one with LSN###....
I'm really stuck here...there is absolutely no way that there could be an
earlier log. I even tried a RESTORE LOG using the full backup to get the
log from there and it gives the same message.
2. When doing a restore through the SMS after selecting a file off disk you
get the backup sets in that file with checkboxes to select the backupset to
restore. My full backups do not show this. The list is empty so I cannot
go on. The restore seems successful by script. My script statements
originated from the "Generate Script" button when doing backups through the
interface. Am I missing something here? Below is a sample backup
statement:
BACKUP DATABASE [Products]
FILEGROUP = N'PRIMARY'
TO DISK = @.strBackupFile
WITH NOFORMAT, NOINIT,
NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
ANY HELP IS ***GREATLY*** APPRECIATED!!!Actually now I'm seeing after restoring the full backup WITH RECOVERY it
still shows the database in a recovering state and it cannot be accessed!!
How can this happen? What have I done wrong here?
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||And while on the topic of backups, I setup this process based on input from
this group. It just seems that it ends up having an inordinate amount of
files to have to restore to get back online. Wouldn't it be better to just
do differentials hourly instead of log backups? Then in a failure you'd
never have more than 2 files to restore.
Just wondering.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||> Wouldn't it be better to just do differentials hourly instead of log backups?
You could do that, but log backups has lot of advantages to diff backups. These includes
Point in time restore.
Ability to backup log if database becomes toast (zero data loss)
Ability to backup log, and restore the most recent db backup and all subsequent log backup sin case
you get a corruption of the database.
Etc.
In short, log backups has so many advantages so you don't want to trade it for diff backups just for
convenience. But sometimes you want to do both db, diff *and* log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OnZRT6xEHHA.4740@.TK2MSFTNGP03.phx.gbl...
> And while on the topic of backups, I setup this process based on input from this group. It just
> seems that it ends up having an inordinate amount of files to have to restore to get back online.
> Wouldn't it be better to just do differentials hourly instead of log backups? Then in a failure
> you'd never have more than 2 files to restore.
> Just wondering.
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>>I was mistakenly logged into the wrong server this morning and deleted all of our product related
>>information!!
>> I've been doing weekly full backups with hourly log file backups.
>> I have 2 questions...
>> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My hourly
>> log backups stop between midnight and begin again at 6:15am Saturday. When I restore the full
>> backup from last saturday it restores fine. When I go to restore the first log it tells me the
>> log is too recent and there is an earlier one with LSN###....
>> I'm really stuck here...there is absolutely no way that there could be an earlier log. I even
>> tried a RESTORE LOG using the full backup to get the log from there and it gives the same
>> message.
>> 2. When doing a restore through the SMS after selecting a file off disk you get the backup sets
>> in that file with checkboxes to select the backupset to restore. My full backups do not show
>> this. The list is empty so I cannot go on. The restore seems successful by script. My script
>> statements originated from the "Generate Script" button when doing backups through the interface.
>> Am I missing something here? Below is a sample backup statement:
>> BACKUP DATABASE [Products]
>> FILEGROUP = N'PRIMARY'
>> TO DISK = @.strBackupFile
>> WITH NOFORMAT, NOINIT,
>> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>>
>> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||> Actually now I'm seeing after restoring the full backup WITH RECOVERY it still shows the database
> in a recovering state and it cannot be accessed!!
Perhaps just a refresh problem in your GUI?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OppZhXxEHHA.3224@.TK2MSFTNGP04.phx.gbl...
> Actually now I'm seeing after restoring the full backup WITH RECOVERY it still shows the database
> in a recovering state and it cannot be accessed!! How can this happen? What have I done wrong
> here?
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>>I was mistakenly logged into the wrong server this morning and deleted all of our product related
>>information!!
>> I've been doing weekly full backups with hourly log file backups.
>> I have 2 questions...
>> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My hourly
>> log backups stop between midnight and begin again at 6:15am Saturday. When I restore the full
>> backup from last saturday it restores fine. When I go to restore the first log it tells me the
>> log is too recent and there is an earlier one with LSN###....
>> I'm really stuck here...there is absolutely no way that there could be an earlier log. I even
>> tried a RESTORE LOG using the full backup to get the log from there and it gives the same
>> message.
>> 2. When doing a restore through the SMS after selecting a file off disk you get the backup sets
>> in that file with checkboxes to select the backupset to restore. My full backups do not show
>> this. The list is empty so I cannot go on. The restore seems successful by script. My script
>> statements originated from the "Generate Script" button when doing backups through the interface.
>> Am I missing something here? Below is a sample backup statement:
>> BACKUP DATABASE [Products]
>> FILEGROUP = N'PRIMARY'
>> TO DISK = @.strBackupFile
>> WITH NOFORMAT, NOINIT,
>> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>>
>> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My hourly
> log backups stop between midnight and begin again at 6:15am Saturday. When I restore the full
> backup from last saturday it restores fine. When I go to restore the first log it tells me the
> log is too recent and there is an earlier one with LSN###....
You need to hunt down that log backup. You can check the backup history tables in the msdb database,
which can give you a clue where it is.
> 2. When doing a restore through the SMS after selecting a file off disk you get the backup sets
> in that file with checkboxes to select the backupset to restore. My full backups do not show
> this. The list is empty so I cannot go on.
I suggest you read up on the RESTORE command and use RESTORE HEADERONLY and RESTORE FILELISTONLY so
you can manage a restore when the GUI is acting up on you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all of our product related
>information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My hourly
> log backups stop between midnight and begin again at 6:15am Saturday. When I restore the full
> backup from last saturday it restores fine. When I go to restore the first log it tells me the
> log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an earlier log. I even
> tried a RESTORE LOG using the full backup to get the log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk you get the backup sets
> in that file with checkboxes to select the backupset to restore. My full backups do not show
> this. The list is empty so I cannot go on. The restore seems successful by script. My script
> statements originated from the "Generate Script" button when doing backups through the interface.
> Am I missing something here? Below is a sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>

Help ! How do i refer to the deleted table into a trigger statement ?

Hi there,

Could somebody post some simple example how one can refer to
a column in the 'deleted' temporary table within a trigger definition ?
Should the 'deleted' and 'inserted' temp tables be declared, or they are
'implied' ?

I keep having this msg when trying to create a trigger
"The column prefix 'deleted' does not match with a table name or alias name
used in the query"

Thanks for your inputs,
Eddy.[posted and mailed, please reply in news]

Eddy Fontaine (eddy.fontaine@.advalvas.be) writes:
> Could somebody post some simple example how one can refer to
> a column in the 'deleted' temporary table within a trigger definition ?
> Should the 'deleted' and 'inserted' temp tables be declared, or they are
> 'implied' ?

They are implied. You just use them.

> I keep having this msg when trying to create a trigger
> "The column prefix 'deleted' does not match with a table name or alias
> name used in the query"

May I guess that you are using dynamic SQL? Code executed in EXEC() or
sp_executesql is not part of the trigger, but constitutes a scope on
its own.

A practical workaround is to say:

SELECT * INTO #deleted FROM deleted
SELECT * INTO #inserted FROM inserted

first in the trigger.

This can even be useful in a trigger that does not use dynamic SQL,
particular if multi-row operations are common, because you can get
better performance. The virtual tables inserted and deleted are
not very efficient when they get some size.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Eddy Fontaine (eddy.fontaine@.advalvas.be) writes:
> Thanks Erland,
> I was trying to refer to a column in the deleted temp table with
> 'deleted.column_name'...
> My sql server does not like this !

Since you never posted the code to the trigger that gave you the
error message, I cannot say what your problem really was. Judging from
the code you post now, may you tried with:

> UPDATE DemographicData SET Lastupdate = getdate()
> WHERE ID = deleted.ID

which is not legal, but that has nothing to do with inserted/deleted,
but with the fact that you must name a table in a query in a FROM
clause or after INSERT/DELETE/UPDATE.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Wednesday, March 7, 2012

Help - Recovering a deleted job

Any ideas how I can recover a job that has been deleted (?) from SQL
7.0 ? We had one which was used on a regular basis which has now
ceased to exists. I've spoken to the people responsible for the server
and was met by blank faces (as usual). They have no idea when it was
deleted, by whom or when. To make matters worse, they don't think they
can get it off the backup set without a major bit of work to the
server. Suprisingly, none of the users noticed that things had stopped
working.

Unfortunately, I needed to do some work on this in preparation for my
monthly meeting with my boss on Monday. Whilst I'm not to blame, I'd
like to understand a little more about any options available to us for
recovery as he will undoubtedly ask when he finds out.

I have an old back up on another server, so it's not the end of the
world re-creating it, but I was under the impression it was backed up
properly. Is this normal or have they missed something ?

Are there any ways I can recover this ? What can I get done to the
backup to make sure this is backed up properly ?

Thanks in advance.

Ryan> Are there any ways I can recover this ? What can I get done to the
> backup to make sure this is backed up properly ?
> Thanks in advance.
> Ryan

If you ahve a maintenance plan that covers the system databases then you
should have a copy of the msdb database somewhere - this is the one that you
want.

Using Enterprise manager, restore this backup onto the same server, but
under a database name of "OLDmsdb"

From here, you shoul dbe able to explore sysjobs and sysjobsteps.

It is POSSIBLE to move these entries into the relevant system tables on the
live msdb, but my recommendation would be to extract the data you need from
the old sysjobsteps table, then re-create the new job manually.

Steven

Help - recover deleted .MDF file

Hello,
By mistake I deleted database file from hard disk. Is there any way I can
recover the file please?
Thanks,
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/Is it in the Recycle bin?
Do you have a backup?
Other than that, if nothing has reused any of its sectors, I think there are
untilities that can recover it, but I'm not sure which ones they are.
"Harish Mohanbabu" <Axapta@.online.nospam> wrote in message
news:BD8F810F-F5E3-4590-9CCB-3D102C4775F5@.microsoft.com...
> Hello,
> By mistake I deleted database file from hard disk. Is there any way I can
> recover the file please?
> Thanks,
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]
> http://www.harishm.com/

HELP - problem after copying MDF and Log files

I had a problem where a log file was deleted. I managed to get the database
recovered on my own machine. I sent the MDF and Log files to the client so
he could stop SQL Server, copy the files and then start SQL Server again.
Now I can't get into SQL Server at all - it keeps telling me SQL Server does
not exist or access denied. I tried to delete the files that I copied (after
shutting down SQL Server) but it didn't work - on startup I still can't
access the SQL Server with any of the client tools. HELP!Which database was the log lost for? As long as it was not master or model
you may be able to use sp_attach_single_file_db.
Check if SQL Server is running.
If you did it to master or model, you have messed up the server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"claire" <claire@.discussions.microsoft.com> wrote in message
news:AE9EC5D3-724A-4480-8367-D85855839152@.microsoft.com...
> I had a problem where a log file was deleted. I managed to get the
database
> recovered on my own machine. I sent the MDF and Log files to the client
so
> he could stop SQL Server, copy the files and then start SQL Server again.
> Now I can't get into SQL Server at all - it keeps telling me SQL Server
does
> not exist or access denied. I tried to delete the files that I copied
(after
> shutting down SQL Server) but it didn't work - on startup I still can't
> access the SQL Server with any of the client tools. HELP!
>|||Stopping the server, copying the files then starting was probably not a good
idea.
If you had the database on your machine then sending a backup and gettig the
client to restore would have been a better option.
Check the sql server logs and application logs - you will probably see a
message to say what has happenned. It sounds like he has corrupted master,
model or done something to change the path of the system databases.
This is probably recoverable but you might be better off re-installing sql
server and attaching all the databases (try ttaching on another server first
to check integrity).
You then might want to look at your backup strategy in case this happens
again.

HELP - problem after copying MDF and Log files

I had a problem where a log file was deleted. I managed to get the database
recovered on my own machine. I sent the MDF and Log files to the client so
he could stop SQL Server, copy the files and then start SQL Server again.
Now I can't get into SQL Server at all - it keeps telling me SQL Server does
not exist or access denied. I tried to delete the files that I copied (after
shutting down SQL Server) but it didn't work - on startup I still can't
access the SQL Server with any of the client tools. HELP!
Which database was the log lost for? As long as it was not master or model
you may be able to use sp_attach_single_file_db.
Check if SQL Server is running.
If you did it to master or model, you have messed up the server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"claire" <claire@.discussions.microsoft.com> wrote in message
news:AE9EC5D3-724A-4480-8367-D85855839152@.microsoft.com...
> I had a problem where a log file was deleted. I managed to get the
database
> recovered on my own machine. I sent the MDF and Log files to the client
so
> he could stop SQL Server, copy the files and then start SQL Server again.
> Now I can't get into SQL Server at all - it keeps telling me SQL Server
does
> not exist or access denied. I tried to delete the files that I copied
(after
> shutting down SQL Server) but it didn't work - on startup I still can't
> access the SQL Server with any of the client tools. HELP!
>
|||Stopping the server, copying the files then starting was probably not a good
idea.
If you had the database on your machine then sending a backup and gettig the
client to restore would have been a better option.
Check the sql server logs and application logs - you will probably see a
message to say what has happenned. It sounds like he has corrupted master,
model or done something to change the path of the system databases.
This is probably recoverable but you might be better off re-installing sql
server and attaching all the databases (try ttaching on another server first
to check integrity).
You then might want to look at your backup strategy in case this happens
again.

HELP - problem after copying MDF and Log files

I had a problem where a log file was deleted. I managed to get the databas
e
recovered on my own machine. I sent the MDF and Log files to the client so
he could stop SQL Server, copy the files and then start SQL Server again.
Now I can't get into SQL Server at all - it keeps telling me SQL Server doe
s
not exist or access denied. I tried to delete the files that I copied (afte
r
shutting down SQL Server) but it didn't work - on startup I still can't
access the SQL Server with any of the client tools. HELP!Which database was the log lost for? As long as it was not master or model
you may be able to use sp_attach_single_file_db.
Check if SQL Server is running.
If you did it to master or model, you have messed up the server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"claire" <claire@.discussions.microsoft.com> wrote in message
news:AE9EC5D3-724A-4480-8367-D85855839152@.microsoft.com...
> I had a problem where a log file was deleted. I managed to get the
database
> recovered on my own machine. I sent the MDF and Log files to the client
so
> he could stop SQL Server, copy the files and then start SQL Server again.
> Now I can't get into SQL Server at all - it keeps telling me SQL Server
does
> not exist or access denied. I tried to delete the files that I copied
(after
> shutting down SQL Server) but it didn't work - on startup I still can't
> access the SQL Server with any of the client tools. HELP!
>|||Stopping the server, copying the files then starting was probably not a good
idea.
If you had the database on your machine then sending a backup and gettig the
client to restore would have been a better option.
Check the sql server logs and application logs - you will probably see a
message to say what has happenned. It sounds like he has corrupted master,
model or done something to change the path of the system databases.
This is probably recoverable but you might be better off re-installing sql
server and attaching all the databases (try ttaching on another server first
to check integrity).
You then might want to look at your backup strategy in case this happens
again.

Friday, March 23, 2012

Help creating a DELETE trigger.

Wednesday, March 21, 2012

Help Altering Database Table from NULL to NOT NULL

Monday, March 19, 2012

HELP : all records of one table have been deleted!

HELP : all records of one table have been deleted!

HELP : all records of one table have been deleted!

Monday, March 12, 2012

HELP ! Production down and backups not restoring properly!

HELP ! Production down and backups not restoring properly!

HELP ! Production down and backups not restoring properly!

Help ! How do i refer to the deleted table into a trigger statement ?

Wednesday, March 7, 2012

Help - Recovering a deleted job

Help - recover deleted .MDF file

HELP - problem after copying MDF and Log files

HELP - problem after copying MDF and Log files

HELP - problem after copying MDF and Log files

Absurd project

Blog Archive

About Me