If I back up a database without using a maintenance plan, is there a query
that can be made to determine when the last full backup of that database
occurred?
Thanks in advance!Try:
select top 1
backup_finish_date
from
msdb.dbo.backupset
where
database_name = 'Northwind'
and type = 'D'
order by
backup_finish_date desc
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Jim Norton" <jim.norton@.joe.com> wrote in message
news:%237SbYsn%23FHA.1312@.TK2MSFTNGP09.phx.gbl...
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
>|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !
Showing posts with label back. Show all posts
Showing posts with label back. Show all posts
Tuesday, March 27, 2012
Determine last backup?
If I back up a database without using a maintenance plan, is there a query
that can be made to determine when the last full backup of that database
occurred?
Thanks in advance!Try:
select top 1
backup_finish_date
from
msdb.dbo.backupset
where
database_name = 'Northwind'
and type = 'D'
order by
backup_finish_date desc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Jim Norton" <jim.norton@.joe.com> wrote in message
news:%237SbYsn%23FHA.1312@.TK2MSFTNGP09.phx.gbl...
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
>|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102
) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102
) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !
that can be made to determine when the last full backup of that database
occurred?
Thanks in advance!Try:
select top 1
backup_finish_date
from
msdb.dbo.backupset
where
database_name = 'Northwind'
and type = 'D'
order by
backup_finish_date desc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Jim Norton" <jim.norton@.joe.com> wrote in message
news:%237SbYsn%23FHA.1312@.TK2MSFTNGP09.phx.gbl...
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
>|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102
) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102
) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !
Determine last backup?
If I back up a database without using a maintenance plan, is there a query
that can be made to determine when the last full backup of that database
occurred?
Thanks in advance!
Try:
select top 1
backup_finish_date
from
msdb.dbo.backupset
where
database_name = 'Northwind'
and type = 'D'
order by
backup_finish_date desc
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Jim Norton" <jim.norton@.joe.com> wrote in message
news:%237SbYsn%23FHA.1312@.TK2MSFTNGP09.phx.gbl...
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
>
|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '?'
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !
|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '?'
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !
that can be made to determine when the last full backup of that database
occurred?
Thanks in advance!
Try:
select top 1
backup_finish_date
from
msdb.dbo.backupset
where
database_name = 'Northwind'
and type = 'D'
order by
backup_finish_date desc
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Jim Norton" <jim.norton@.joe.com> wrote in message
news:%237SbYsn%23FHA.1312@.TK2MSFTNGP09.phx.gbl...
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
>
|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '?'
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !
|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '?'
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !
Sunday, March 11, 2012
Detaching and Attaching Standby Database
I need to be able to detach my standby database so I can back it up.
How do I reattach it and keep it from going through a recover (thus
breaking my log consistency with the production database)?
I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
START MSSQLSERVER, but I'm not sure if that will work.
Brad
Have you read the article in the BOL
"How to set up, maintain, and bring online a standby server (Transact-SQL)"?
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bedee1a217f72989689@.news...
> I need to be able to detach my standby database so I can back it up.
> How do I reattach it and keep it from going through a recover (thus
> breaking my log consistency with the production database)?
> I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> START MSSQLSERVER, but I'm not sure if that will work.
|||In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bedee1a217f72989689@.news...
> Have you read the article in the BOL
> "How to set up, maintain, and bring online a standby server (Transact-SQL)"?
Yes, I've read that and I have all of that working. The problem is that
I really want to just keep restoring logs on this server, but I want to
have a local backup of the standby database (45GB production database is
on the west coast and I am on the east with only a T1 between the two).
Yesterday I had some kind of disk hiccup during a log restore and the
standby database was hosed so I have to go back to a 1.5 week old full
backup and a ton of log restores. If I could just automatically backup
(full) the standby database each night my restore issue would be much
smaller. I understand that I can't do an actual backup, but I should be
able to detach, copy and attach or something like that.
|||Brad
I see what you mean
http://www.sql-server-performance.co...g_shipping.asp
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bf5b3bb5a904198968a@.news...[vbcol=seagreen]
> In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
> says...
(Transact-SQL)"?
> Yes, I've read that and I have all of that working. The problem is that
> I really want to just keep restoring logs on this server, but I want to
> have a local backup of the standby database (45GB production database is
> on the west coast and I am on the east with only a T1 between the two).
> Yesterday I had some kind of disk hiccup during a log restore and the
> standby database was hosed so I have to go back to a 1.5 week old full
> backup and a ton of log restores. If I could just automatically backup
> (full) the standby database each night my restore issue would be much
> smaller. I understand that I can't do an actual backup, but I should be
> able to detach, copy and attach or something like that.
|||In article <utmHMwueEHA.2908@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bf5b3bb5a904198968a@.news...
> (Transact-SQL)"?
> I see what you mean
> http://www.sql-server-performance.co...g_shipping.asp
But I can't do real log shipping because I am on standard.
How do I reattach it and keep it from going through a recover (thus
breaking my log consistency with the production database)?
I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
START MSSQLSERVER, but I'm not sure if that will work.
Brad
Have you read the article in the BOL
"How to set up, maintain, and bring online a standby server (Transact-SQL)"?
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bedee1a217f72989689@.news...
> I need to be able to detach my standby database so I can back it up.
> How do I reattach it and keep it from going through a recover (thus
> breaking my log consistency with the production database)?
> I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> START MSSQLSERVER, but I'm not sure if that will work.
|||In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bedee1a217f72989689@.news...
> Have you read the article in the BOL
> "How to set up, maintain, and bring online a standby server (Transact-SQL)"?
Yes, I've read that and I have all of that working. The problem is that
I really want to just keep restoring logs on this server, but I want to
have a local backup of the standby database (45GB production database is
on the west coast and I am on the east with only a T1 between the two).
Yesterday I had some kind of disk hiccup during a log restore and the
standby database was hosed so I have to go back to a 1.5 week old full
backup and a ton of log restores. If I could just automatically backup
(full) the standby database each night my restore issue would be much
smaller. I understand that I can't do an actual backup, but I should be
able to detach, copy and attach or something like that.
|||Brad
I see what you mean
http://www.sql-server-performance.co...g_shipping.asp
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bf5b3bb5a904198968a@.news...[vbcol=seagreen]
> In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
> says...
(Transact-SQL)"?
> Yes, I've read that and I have all of that working. The problem is that
> I really want to just keep restoring logs on this server, but I want to
> have a local backup of the standby database (45GB production database is
> on the west coast and I am on the east with only a T1 between the two).
> Yesterday I had some kind of disk hiccup during a log restore and the
> standby database was hosed so I have to go back to a 1.5 week old full
> backup and a ton of log restores. If I could just automatically backup
> (full) the standby database each night my restore issue would be much
> smaller. I understand that I can't do an actual backup, but I should be
> able to detach, copy and attach or something like that.
|||In article <utmHMwueEHA.2908@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bf5b3bb5a904198968a@.news...
> (Transact-SQL)"?
> I see what you mean
> http://www.sql-server-performance.co...g_shipping.asp
But I can't do real log shipping because I am on standard.
Detaching and Attaching Standby Database
I need to be able to detach my standby database so I can back it up.
How do I reattach it and keep it from going through a recover (thus
breaking my log consistency with the production database)?
I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
START MSSQLSERVER, but I'm not sure if that will work.Brad
Have you read the article in the BOL
"How to set up, maintain, and bring online a standby server (Transact-SQL)"?
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bedee1a217f72989689@.news...
> I need to be able to detach my standby database so I can back it up.
> How do I reattach it and keep it from going through a recover (thus
> breaking my log consistency with the production database)?
> I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> START MSSQLSERVER, but I'm not sure if that will work.|||In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...[vbcol=seagreen]
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bedee1a217f72989689@.news...
> Have you read the article in the BOL
> "How to set up, maintain, and bring online a standby server (Transact-SQL)"?[/vbco
l]
Yes, I've read that and I have all of that working. The problem is that
I really want to just keep restoring logs on this server, but I want to
have a local backup of the standby database (45GB production database is
on the west coast and I am on the east with only a T1 between the two).
Yesterday I had some kind of disk hiccup during a log restore and the
standby database was hosed so I have to go back to a 1.5 week old full
backup and a ton of log restores. If I could just automatically backup
(full) the standby database each night my restore issue would be much
smaller. I understand that I can't do an actual backup, but I should be
able to detach, copy and attach or something like that.|||Brad
I see what you mean
http://www.sql-server-performance.c...og_shipping.asp
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bf5b3bb5a904198968a@.news...
> In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
> says...
(Transact-SQL)"?[vbcol=seagreen]
> Yes, I've read that and I have all of that working. The problem is that
> I really want to just keep restoring logs on this server, but I want to
> have a local backup of the standby database (45GB production database is
> on the west coast and I am on the east with only a T1 between the two).
> Yesterday I had some kind of disk hiccup during a log restore and the
> standby database was hosed so I have to go back to a 1.5 week old full
> backup and a ton of log restores. If I could just automatically backup
> (full) the standby database each night my restore issue would be much
> smaller. I understand that I can't do an actual backup, but I should be
> able to detach, copy and attach or something like that.|||In article <utmHMwueEHA.2908@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bf5b3bb5a904198968a@.news...
> (Transact-SQL)"?
> I see what you mean
> http://www.sql-server-performance.c...og_shipping.asp
But I can't do real log shipping because I am on standard.
How do I reattach it and keep it from going through a recover (thus
breaking my log consistency with the production database)?
I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
START MSSQLSERVER, but I'm not sure if that will work.Brad
Have you read the article in the BOL
"How to set up, maintain, and bring online a standby server (Transact-SQL)"?
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bedee1a217f72989689@.news...
> I need to be able to detach my standby database so I can back it up.
> How do I reattach it and keep it from going through a recover (thus
> breaking my log consistency with the production database)?
> I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> START MSSQLSERVER, but I'm not sure if that will work.|||In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...[vbcol=seagreen]
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bedee1a217f72989689@.news...
> Have you read the article in the BOL
> "How to set up, maintain, and bring online a standby server (Transact-SQL)"?[/vbco
l]
Yes, I've read that and I have all of that working. The problem is that
I really want to just keep restoring logs on this server, but I want to
have a local backup of the standby database (45GB production database is
on the west coast and I am on the east with only a T1 between the two).
Yesterday I had some kind of disk hiccup during a log restore and the
standby database was hosed so I have to go back to a 1.5 week old full
backup and a ton of log restores. If I could just automatically backup
(full) the standby database each night my restore issue would be much
smaller. I understand that I can't do an actual backup, but I should be
able to detach, copy and attach or something like that.|||Brad
I see what you mean
http://www.sql-server-performance.c...og_shipping.asp
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bf5b3bb5a904198968a@.news...
> In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
> says...
(Transact-SQL)"?[vbcol=seagreen]
> Yes, I've read that and I have all of that working. The problem is that
> I really want to just keep restoring logs on this server, but I want to
> have a local backup of the standby database (45GB production database is
> on the west coast and I am on the east with only a T1 between the two).
> Yesterday I had some kind of disk hiccup during a log restore and the
> standby database was hosed so I have to go back to a 1.5 week old full
> backup and a ton of log restores. If I could just automatically backup
> (full) the standby database each night my restore issue would be much
> smaller. I understand that I can't do an actual backup, but I should be
> able to detach, copy and attach or something like that.|||In article <utmHMwueEHA.2908@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bf5b3bb5a904198968a@.news...
> (Transact-SQL)"?
> I see what you mean
> http://www.sql-server-performance.c...og_shipping.asp
But I can't do real log shipping because I am on standard.
Detaching and Attaching Standby Database
I need to be able to detach my standby database so I can back it up.
How do I reattach it and keep it from going through a recover (thus
breaking my log consistency with the production database)?
I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
START MSSQLSERVER, but I'm not sure if that will work.Brad
Have you read the article in the BOL
"How to set up, maintain, and bring online a standby server (Transact-SQL)"?
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bedee1a217f72989689@.news...
> I need to be able to detach my standby database so I can back it up.
> How do I reattach it and keep it from going through a recover (thus
> breaking my log consistency with the production database)?
> I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> START MSSQLSERVER, but I'm not sure if that will work.|||In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bedee1a217f72989689@.news...
> > I need to be able to detach my standby database so I can back it up.
> > How do I reattach it and keep it from going through a recover (thus
> > breaking my log consistency with the production database)?
> >
> > I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> > START MSSQLSERVER, but I'm not sure if that will work.
> Have you read the article in the BOL
> "How to set up, maintain, and bring online a standby server (Transact-SQL)"?
Yes, I've read that and I have all of that working. The problem is that
I really want to just keep restoring logs on this server, but I want to
have a local backup of the standby database (45GB production database is
on the west coast and I am on the east with only a T1 between the two).
Yesterday I had some kind of disk hiccup during a log restore and the
standby database was hosed so I have to go back to a 1.5 week old full
backup and a ton of log restores. If I could just automatically backup
(full) the standby database each night my restore issue would be much
smaller. I understand that I can't do an actual backup, but I should be
able to detach, copy and attach or something like that.|||Brad
I see what you mean
http://www.sql-server-performance.com/sql_server_log_shipping.asp
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bf5b3bb5a904198968a@.news...
> In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
> says...
> >
> > "Brad" <brad@.seesigifthere.com> wrote in message
> > news:MPG.1b7bedee1a217f72989689@.news...
> > > I need to be able to detach my standby database so I can back it up.
> > > How do I reattach it and keep it from going through a recover (thus
> > > breaking my log consistency with the production database)?
> > >
> > > I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> > > START MSSQLSERVER, but I'm not sure if that will work.
> >
> > Have you read the article in the BOL
> > "How to set up, maintain, and bring online a standby server
(Transact-SQL)"?
> Yes, I've read that and I have all of that working. The problem is that
> I really want to just keep restoring logs on this server, but I want to
> have a local backup of the standby database (45GB production database is
> on the west coast and I am on the east with only a T1 between the two).
> Yesterday I had some kind of disk hiccup during a log restore and the
> standby database was hosed so I have to go back to a 1.5 week old full
> backup and a ton of log restores. If I could just automatically backup
> (full) the standby database each night my restore issue would be much
> smaller. I understand that I can't do an actual backup, but I should be
> able to detach, copy and attach or something like that.|||In article <utmHMwueEHA.2908@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bf5b3bb5a904198968a@.news...
> > In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
> > says...
> > >
> > > "Brad" <brad@.seesigifthere.com> wrote in message
> > > news:MPG.1b7bedee1a217f72989689@.news...
> > > > I need to be able to detach my standby database so I can back it up.
> > > > How do I reattach it and keep it from going through a recover (thus
> > > > breaking my log consistency with the production database)?
> > > >
> > > > I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> > > > START MSSQLSERVER, but I'm not sure if that will work.
> > >
> > > Have you read the article in the BOL
> > > "How to set up, maintain, and bring online a standby server
> (Transact-SQL)"?
> >
> > Yes, I've read that and I have all of that working. The problem is that
> > I really want to just keep restoring logs on this server, but I want to
> > have a local backup of the standby database (45GB production database is
> > on the west coast and I am on the east with only a T1 between the two).
> > Yesterday I had some kind of disk hiccup during a log restore and the
> > standby database was hosed so I have to go back to a 1.5 week old full
> > backup and a ton of log restores. If I could just automatically backup
> > (full) the standby database each night my restore issue would be much
> > smaller. I understand that I can't do an actual backup, but I should be
> > able to detach, copy and attach or something like that.
> I see what you mean
> http://www.sql-server-performance.com/sql_server_log_shipping.asp
But I can't do real log shipping because I am on standard.
How do I reattach it and keep it from going through a recover (thus
breaking my log consistency with the production database)?
I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
START MSSQLSERVER, but I'm not sure if that will work.Brad
Have you read the article in the BOL
"How to set up, maintain, and bring online a standby server (Transact-SQL)"?
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bedee1a217f72989689@.news...
> I need to be able to detach my standby database so I can back it up.
> How do I reattach it and keep it from going through a recover (thus
> breaking my log consistency with the production database)?
> I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> START MSSQLSERVER, but I'm not sure if that will work.|||In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bedee1a217f72989689@.news...
> > I need to be able to detach my standby database so I can back it up.
> > How do I reattach it and keep it from going through a recover (thus
> > breaking my log consistency with the production database)?
> >
> > I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> > START MSSQLSERVER, but I'm not sure if that will work.
> Have you read the article in the BOL
> "How to set up, maintain, and bring online a standby server (Transact-SQL)"?
Yes, I've read that and I have all of that working. The problem is that
I really want to just keep restoring logs on this server, but I want to
have a local backup of the standby database (45GB production database is
on the west coast and I am on the east with only a T1 between the two).
Yesterday I had some kind of disk hiccup during a log restore and the
standby database was hosed so I have to go back to a 1.5 week old full
backup and a ton of log restores. If I could just automatically backup
(full) the standby database each night my restore issue would be much
smaller. I understand that I can't do an actual backup, but I should be
able to detach, copy and attach or something like that.|||Brad
I see what you mean
http://www.sql-server-performance.com/sql_server_log_shipping.asp
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b7bf5b3bb5a904198968a@.news...
> In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
> says...
> >
> > "Brad" <brad@.seesigifthere.com> wrote in message
> > news:MPG.1b7bedee1a217f72989689@.news...
> > > I need to be able to detach my standby database so I can back it up.
> > > How do I reattach it and keep it from going through a recover (thus
> > > breaking my log consistency with the production database)?
> > >
> > > I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> > > START MSSQLSERVER, but I'm not sure if that will work.
> >
> > Have you read the article in the BOL
> > "How to set up, maintain, and bring online a standby server
(Transact-SQL)"?
> Yes, I've read that and I have all of that working. The problem is that
> I really want to just keep restoring logs on this server, but I want to
> have a local backup of the standby database (45GB production database is
> on the west coast and I am on the east with only a T1 between the two).
> Yesterday I had some kind of disk hiccup during a log restore and the
> standby database was hosed so I have to go back to a 1.5 week old full
> backup and a ton of log restores. If I could just automatically backup
> (full) the standby database each night my restore issue would be much
> smaller. I understand that I can't do an actual backup, but I should be
> able to detach, copy and attach or something like that.|||In article <utmHMwueEHA.2908@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
says...
> "Brad" <brad@.seesigifthere.com> wrote in message
> news:MPG.1b7bf5b3bb5a904198968a@.news...
> > In article <#b#zEYueEHA.1036@.TK2MSFTNGP10.phx.gbl>, urid@.iscar.co.il
> > says...
> > >
> > > "Brad" <brad@.seesigifthere.com> wrote in message
> > > news:MPG.1b7bedee1a217f72989689@.news...
> > > > I need to be able to detach my standby database so I can back it up.
> > > > How do I reattach it and keep it from going through a recover (thus
> > > > breaking my log consistency with the production database)?
> > > >
> > > > I thought about doing a NET STOP MSSQLSERVER, backup files, and NET
> > > > START MSSQLSERVER, but I'm not sure if that will work.
> > >
> > > Have you read the article in the BOL
> > > "How to set up, maintain, and bring online a standby server
> (Transact-SQL)"?
> >
> > Yes, I've read that and I have all of that working. The problem is that
> > I really want to just keep restoring logs on this server, but I want to
> > have a local backup of the standby database (45GB production database is
> > on the west coast and I am on the east with only a T1 between the two).
> > Yesterday I had some kind of disk hiccup during a log restore and the
> > standby database was hosed so I have to go back to a 1.5 week old full
> > backup and a ton of log restores. If I could just automatically backup
> > (full) the standby database each night my restore issue would be much
> > smaller. I understand that I can't do an actual backup, but I should be
> > able to detach, copy and attach or something like that.
> I see what you mean
> http://www.sql-server-performance.com/sql_server_log_shipping.asp
But I can't do real log shipping because I am on standard.
Detached database can't be attached back
Hi
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
Percy
Sounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scri...p?scriptid=599
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
|||Are you saying that you deleted one of the log files, and then copied the second log file to the
first log files name and location? If so, you probably need to go the restore route, open a case or
try the last resort posted by Andrew. If not, well, your options are the same...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
> Device activation errror, The physical name 'c:\program files\microsoft sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
Percy
Sounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scri...p?scriptid=599
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
|||Are you saying that you deleted one of the log files, and then copied the second log file to the
first log files name and location? If so, you probably need to go the restore route, open a case or
try the last resort posted by Andrew. If not, well, your options are the same...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
> Device activation errror, The physical name 'c:\program files\microsoft sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
Detached database can't be attached back
Hi
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file i
n
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf i
n
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted
.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
PercySounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy|||Are you saying that you deleted one of the log files, and then copied the se
cond log file to the
first log files name and location? If so, you probably need to go the restor
e route, open a case or
try the last resort posted by Andrew. If not, well, your options are the sam
e...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the D
B
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original locati
on
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a prima
ry
> database file. Could not open new darabase 'crn'. CREATE DATABASE is abort
ed.
> Device activation errror, The physical name 'c:\program files\microsoft sq
l
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file i
n
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf i
n
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted
.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
PercySounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy|||Are you saying that you deleted one of the log files, and then copied the se
cond log file to the
first log files name and location? If so, you probably need to go the restor
e route, open a case or
try the last resort posted by Andrew. If not, well, your options are the sam
e...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the D
B
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original locati
on
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a prima
ry
> database file. Could not open new darabase 'crn'. CREATE DATABASE is abort
ed.
> Device activation errror, The physical name 'c:\program files\microsoft sq
l
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
Detached database can't be attached back
Hi
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
PercySounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
--
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy|||Are you saying that you deleted one of the log files, and then copied the second log file to the
first log files name and location? If so, you probably need to go the restore route, open a case or
try the last resort posted by Andrew. If not, well, your options are the same...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
> Device activation errror, The physical name 'c:\program files\microsoft sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
PercySounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
--
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy|||Are you saying that you deleted one of the log files, and then copied the second log file to the
first log files name and location? If so, you probably need to go the restore route, open a case or
try the last resort posted by Andrew. If not, well, your options are the same...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
> Device activation errror, The physical name 'c:\program files\microsoft sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
Wednesday, March 7, 2012
Despirately need help! Must Rollback but dont know how!
Don't ask, but I iadvertantly managed to delete 57,000+ records in the EM console and I need to roll back the database a few minutes to recover said information. I do not care about transactions that have occured since due to shutting the system down immediately upon realizing what I did. I have however never had the need to roll back the database before, so I have experience doing so. Any help is desperately needed and greatly appreciated!!!ASSUMING:
1. Your database is in Full Recovery mode (check properties)
2. You have been making regular full backups of the database and these backups are stored on disk and accessible to the database server
3. You have been making regular transaction log backups of the database and these backups are stored on disk and accessible to the database server
THEN:
If the date/time of the last transaction log backup is sufficiently recent for your recovery needs, proceed to step 2:
1. Backup the committed transactions in the current transaction log:
BACKUP LOG [databasename]
TO [DEVICE]
WITH NO_TRUNCATE
2.
RESTORE DATABASE [DatabaseName]
FROM DISK = '[path to full backup]'
WITH NORECOVERY
RESTORE LOG [DatabaseName]
FROM DISK = '[path to first log backup after last full backup]'
WITH NORECOVERY
RESTORE LOG [DatabaseName]
FROM DISK = '[path to next log file in sequence]'
WITH NORECOVERY
.
.
.
RESTORE LOG [DatabaseName]
FROM DISK = '[path to last log file in sequence]'
WITH RECOVERY, STOPAT = '10/06/2005 09:05'
3. Smile confidently at your boss and say, "see, no problem!"
Alternatively, you can do this through Enterprise Manager. Right click, All Tasks, Restore Database. HOWEVER, I have had problems in the past with the Point in Time recovery feature (it just blows right past the point in time and continues processing transactions).
Best of luck and best regards,
hmscott
Don't ask, but I iadvertantly managed to delete 57,000+ records in the EM console and I need to roll back the database a few minutes to recover said information. I do not care about transactions that have occured since due to shutting the system down immediately upon realizing what I did. I have however never had the need to roll back the database before, so I have experience doing so. Any help is desperately needed and greatly appreciated!!!|||Any help is desperately needed and greatly appreciated!!!
Sure,
Here you go
http://www.dice.com/|||Sure,
Here you go...
Ouch! That's mean! :)|||Brett must have been up all night at the grindstone, sharpening his tongue. Yeouch!
-PatP|||No I'm not, I'm being a realist...or maybe Mr lindman has been rubbing off.
In either case, would you care lay the odds that this will be recoverable?
better yet, does anyone want to make a wager|||The table information difference from last night to today was roughly 100 records. I was unable to do the recover due to a failure in the process. We needed to get back up and running ASAP, so I'm manually entering the missing 100 records.
BTW, I still have a job :p|||I'm not being snide or trying to be rude, but I hope you will take this as an opportunity to review your database backup and recovery procedures. Know when the backups are made, know where they're stored, and have it all written down somewhere close at hand. Have a script to recover available (you can actually create a job to have the recovery script generated periodically with correct values for file names and such).
Then...
practice.
Practice recovering to a point in time.
Practice recovering from disk.
Practice recovering from files that have been moved to tape.
Practice recovering multiple databases.
Practice recovering an entire server.
Congrats on the save (and on keeping your job). I hope to see you again on the forum. Don't mind Brett; he just hasn't had is fourth cup of coffee yet. :-)
Regards,
hmscott
The table information difference from last night to today was roughly 100 records. I was unable to do the recover due to a failure in the process. We needed to get back up and running ASAP, so I'm manually entering the missing 100 records.
BTW, I still have a job :p|||Not that it's an excuse, but I'm not a DBA. I'm a web applications developer who just happens to be in charge of the DB for this application/server. I'm learning alot, but unforutately, sometimes we must learn from our mistakes.
Thanks for you help and input.|||Not that it's an excuse, but I'm not a DBA. I'm a web applications developer who just happens to be in charge of the DB for this application/server. I'm learning alot, but unforutately, sometimes we must learn from our mistakes.
Thanks for you help and input.
Dude, I would have put money that you didn't have nightly backups...now what you need to do is create transaction dumps say every half hour...
What is your recovery model?|||Dude, I would have put money that you didn't have nightly backups...now what you need to do is create transaction dumps say every half hour...
What is your recovery model?I'm a web applications developer who obviously knows enough SQL to get into trouble :)
I am however NOT that dumb ... I have nightly backups with Full Recovery turned on ;)
Please, educate me if you don't mind ... how do I create transaction bumps (every 15 minutes would be great if performance isn't going to be an issue).|||I'm a web applications developer who obviously knows enough SQL to get into trouble :)
I am however NOT that dumb ... I have nightly backups with Full Recovery turned on ;)
Please, educate me if you don't mind ... how do I create transaction bumps (every 15 minutes would be great if performance isn't going to be an issue).
The pros will take exception to this recommendation, but I would use the Maintenance wizard (create two wizards, one for the system databases and one for user databases). All the parameters that you need to specify for full backups and log backups are there. Skip over the optimizations and consistency checks for now. In the System maintenance plan, skip the transaction log backups (master, model and msdb should be in Simple Recovery mode).
Later on you can read about sp_sqlmaint.
I know your pain. I've been there and I've done it.
Regards,
hmscott|||Listen...as far as the state of things are now, the wizard should be used right now. How are your drives configured? Do you have a c and d partition?
Here, check this out:
http://weblogs.sqlteam.com/tarad/category/95.aspx|||Yes, I have a C and D partition. The machine is RAID 5/hotswap, so drive failure is taken care of. The DB is backed up locally and then tap backup is taken from this backup daily as well. I guess the only things lacking is my knowledge and a good solid recovery plan ... backups are being done.|||Yes, I have a C and D partition. The machine is RAID 5/hotswap, so drive failure is taken care of. The DB is backed up locally and then tap backup is taken from this backup daily as well. I guess the only things lacking is my knowledge and a good solid recovery plan ... backups are being done.
Just to verify: are C: and D: partitions of the same RAID-5 disk volume? If so, you're not fully protected in the event of failure (say, for example, failure of the RAID card). For best results (performance and recoverability) the data and the log files must be on separate physical volumes.
Regards,
hmscott|||Server has Dual everything: Dual Processor, Dual NIC cards, Dual SCSI Controller, etc. The SCSI cards operate in a load balancing capacity and if one fails, the network admin is notified while the other card handles 100% of the load until the admin can replace the failed card.
Correct me if I am wrong, but my understanding is this: in a RAID 5 setup (3 disk array in this case), they are simply partitions that take space scattered across the 3 drives and that you can't specify which partion goes on which disk ... that is the point of RAID is it not? That way, if one drive fails, you swap it out and it rebuilds the missing information from the mirrored information on the other two drives.
Of course, without a solid recovery model and appropriate planning, it doesn't matter how the data is stored :banghead:|||Nevermind ... I just reread your post and noticed you used the word "Volume" when I read it the first time, I interprited that as "disk".|||Been doing a lot of research in the last few hours since I have finished manually replacing all the data that I destroyed. I was wondering if anyone would recommend/discourage the use of Red-Gate Software's SQL Backup (http://www.red-gate.com/products/SQL_Backup/index.htm) and SQL Log Rescue (http://www.red-gate.com/products/SQL_Log_Rescue/index.htm), which is bundled together in a $490 package called the SQL Backup & Rescue Bundle (http://www.red-gate.com/products/sql_bundles/index.htm)
1. Your database is in Full Recovery mode (check properties)
2. You have been making regular full backups of the database and these backups are stored on disk and accessible to the database server
3. You have been making regular transaction log backups of the database and these backups are stored on disk and accessible to the database server
THEN:
If the date/time of the last transaction log backup is sufficiently recent for your recovery needs, proceed to step 2:
1. Backup the committed transactions in the current transaction log:
BACKUP LOG [databasename]
TO [DEVICE]
WITH NO_TRUNCATE
2.
RESTORE DATABASE [DatabaseName]
FROM DISK = '[path to full backup]'
WITH NORECOVERY
RESTORE LOG [DatabaseName]
FROM DISK = '[path to first log backup after last full backup]'
WITH NORECOVERY
RESTORE LOG [DatabaseName]
FROM DISK = '[path to next log file in sequence]'
WITH NORECOVERY
.
.
.
RESTORE LOG [DatabaseName]
FROM DISK = '[path to last log file in sequence]'
WITH RECOVERY, STOPAT = '10/06/2005 09:05'
3. Smile confidently at your boss and say, "see, no problem!"
Alternatively, you can do this through Enterprise Manager. Right click, All Tasks, Restore Database. HOWEVER, I have had problems in the past with the Point in Time recovery feature (it just blows right past the point in time and continues processing transactions).
Best of luck and best regards,
hmscott
Don't ask, but I iadvertantly managed to delete 57,000+ records in the EM console and I need to roll back the database a few minutes to recover said information. I do not care about transactions that have occured since due to shutting the system down immediately upon realizing what I did. I have however never had the need to roll back the database before, so I have experience doing so. Any help is desperately needed and greatly appreciated!!!|||Any help is desperately needed and greatly appreciated!!!
Sure,
Here you go
http://www.dice.com/|||Sure,
Here you go...
Ouch! That's mean! :)|||Brett must have been up all night at the grindstone, sharpening his tongue. Yeouch!
-PatP|||No I'm not, I'm being a realist...or maybe Mr lindman has been rubbing off.
In either case, would you care lay the odds that this will be recoverable?
better yet, does anyone want to make a wager|||The table information difference from last night to today was roughly 100 records. I was unable to do the recover due to a failure in the process. We needed to get back up and running ASAP, so I'm manually entering the missing 100 records.
BTW, I still have a job :p|||I'm not being snide or trying to be rude, but I hope you will take this as an opportunity to review your database backup and recovery procedures. Know when the backups are made, know where they're stored, and have it all written down somewhere close at hand. Have a script to recover available (you can actually create a job to have the recovery script generated periodically with correct values for file names and such).
Then...
practice.
Practice recovering to a point in time.
Practice recovering from disk.
Practice recovering from files that have been moved to tape.
Practice recovering multiple databases.
Practice recovering an entire server.
Congrats on the save (and on keeping your job). I hope to see you again on the forum. Don't mind Brett; he just hasn't had is fourth cup of coffee yet. :-)
Regards,
hmscott
The table information difference from last night to today was roughly 100 records. I was unable to do the recover due to a failure in the process. We needed to get back up and running ASAP, so I'm manually entering the missing 100 records.
BTW, I still have a job :p|||Not that it's an excuse, but I'm not a DBA. I'm a web applications developer who just happens to be in charge of the DB for this application/server. I'm learning alot, but unforutately, sometimes we must learn from our mistakes.
Thanks for you help and input.|||Not that it's an excuse, but I'm not a DBA. I'm a web applications developer who just happens to be in charge of the DB for this application/server. I'm learning alot, but unforutately, sometimes we must learn from our mistakes.
Thanks for you help and input.
Dude, I would have put money that you didn't have nightly backups...now what you need to do is create transaction dumps say every half hour...
What is your recovery model?|||Dude, I would have put money that you didn't have nightly backups...now what you need to do is create transaction dumps say every half hour...
What is your recovery model?I'm a web applications developer who obviously knows enough SQL to get into trouble :)
I am however NOT that dumb ... I have nightly backups with Full Recovery turned on ;)
Please, educate me if you don't mind ... how do I create transaction bumps (every 15 minutes would be great if performance isn't going to be an issue).|||I'm a web applications developer who obviously knows enough SQL to get into trouble :)
I am however NOT that dumb ... I have nightly backups with Full Recovery turned on ;)
Please, educate me if you don't mind ... how do I create transaction bumps (every 15 minutes would be great if performance isn't going to be an issue).
The pros will take exception to this recommendation, but I would use the Maintenance wizard (create two wizards, one for the system databases and one for user databases). All the parameters that you need to specify for full backups and log backups are there. Skip over the optimizations and consistency checks for now. In the System maintenance plan, skip the transaction log backups (master, model and msdb should be in Simple Recovery mode).
Later on you can read about sp_sqlmaint.
I know your pain. I've been there and I've done it.
Regards,
hmscott|||Listen...as far as the state of things are now, the wizard should be used right now. How are your drives configured? Do you have a c and d partition?
Here, check this out:
http://weblogs.sqlteam.com/tarad/category/95.aspx|||Yes, I have a C and D partition. The machine is RAID 5/hotswap, so drive failure is taken care of. The DB is backed up locally and then tap backup is taken from this backup daily as well. I guess the only things lacking is my knowledge and a good solid recovery plan ... backups are being done.|||Yes, I have a C and D partition. The machine is RAID 5/hotswap, so drive failure is taken care of. The DB is backed up locally and then tap backup is taken from this backup daily as well. I guess the only things lacking is my knowledge and a good solid recovery plan ... backups are being done.
Just to verify: are C: and D: partitions of the same RAID-5 disk volume? If so, you're not fully protected in the event of failure (say, for example, failure of the RAID card). For best results (performance and recoverability) the data and the log files must be on separate physical volumes.
Regards,
hmscott|||Server has Dual everything: Dual Processor, Dual NIC cards, Dual SCSI Controller, etc. The SCSI cards operate in a load balancing capacity and if one fails, the network admin is notified while the other card handles 100% of the load until the admin can replace the failed card.
Correct me if I am wrong, but my understanding is this: in a RAID 5 setup (3 disk array in this case), they are simply partitions that take space scattered across the 3 drives and that you can't specify which partion goes on which disk ... that is the point of RAID is it not? That way, if one drive fails, you swap it out and it rebuilds the missing information from the mirrored information on the other two drives.
Of course, without a solid recovery model and appropriate planning, it doesn't matter how the data is stored :banghead:|||Nevermind ... I just reread your post and noticed you used the word "Volume" when I read it the first time, I interprited that as "disk".|||Been doing a lot of research in the last few hours since I have finished manually replacing all the data that I destroyed. I was wondering if anyone would recommend/discourage the use of Red-Gate Software's SQL Backup (http://www.red-gate.com/products/SQL_Backup/index.htm) and SQL Log Rescue (http://www.red-gate.com/products/SQL_Log_Rescue/index.htm), which is bundled together in a $490 package called the SQL Backup & Rescue Bundle (http://www.red-gate.com/products/sql_bundles/index.htm)
Sunday, February 19, 2012
Design question
According to transact-sql docs, comparing two nulls when SET ANSI_NULLS is ON, gives back an UNKNOWN, which should evaluate to FALSE in an expression... i guess.
The question is, since i need SET ANSI_NULLS ON in all my stored procedures (i need NULL = <Expr> evaluates to false), where should i instead set it? I mean, is there some database configuration flag somewhere?
Thanks a lot in advance. -julioIt really isn't clear what you are asking...if you are trying to determine if an expression is null when you have ansi_nulls ON then you can use 'IS'. If you could give some code in what you are trying it may help...|||SELECT * FROM [Table] WHERE [Field] LIKE @.Field
I'd like to be sure the evaluation fails (selects an empty table) when [Field] or @.Field are NULL...
Thanks. -julio|||"Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown."
That was in BOL. I hope it helps in understanding why I am stating what I am. What you have should evaluate to false and thus not return any records. The like operator will try to convert [Field] to a varchar so depending on the type it may fail.
David|||[Field] and @.Field are both char (nvarchar actually).
BTW, i still can't get what the answer to my original question is...
Do i need the SET ANSI_NULLS ON?
And, is there any database level configuration flag to set it globally?
Thanks for your kind help. -julio|||Yes you want to set it on. And no you can't set it at the database level. It is set when the object is created. I would also recommend using varchar instead of nvarchar unless need to store unicode data.|||Ok, got it! Thanks a lot for your help.
-julio
Subscribe to:
Posts (Atom)