Create Disaster recovery site (Seperate physical location)

Hello, let me just say thank you in advance for any responses.
I am Project Manager currently trying to architect a Disaster Recovery site.
Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
write to a database simultaneously at a totally seperate location? Open to
any hardware or software solutions as well....basically I need to be able to
flip a switch at one location and have my DR site at another location become
active with full functionality Web, App, DB, yes I know thats a tall order
but money is no object....
You have two options for the DB: You can use log shipping to get a near
real time standby site or you can use the EMC Mirrorview product to create a
standby data set. Both are somewhat complex but are not impossible to
implement.
Web and App can be load-balanced server farm that just deals with life when
half the universe disappears.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....
|||Hi
Look at EMC's SRDF. It is the solution at EMC level. We use if got all our
systems (bug banking environment)
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....

Create Disaster recovery site (Seperate physical location)

Hello, let me just say thank you in advance for any responses.
I am Project Manager currently trying to architect a Disaster Recovery site.
Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
write to a database simultaneously at a totally seperate location? Open to
any hardware or software solutions as well....basically I need to be able t
o
flip a switch at one location and have my DR site at another location become
active with full functionality Web, App, DB, yes I know thats a tall order
but money is no object....You have two options for the DB: You can use log shipping to get a near
real time standby site or you can use the EMC Mirrorview product to create a
standby data set. Both are somewhat complex but are not impossible to
implement.
Web and App can be load-balanced server farm that just deals with life when
half the universe disappears.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....|||Hi
Look at EMC's SRDF. It is the solution at EMC level. We use if got all our
systems (bug banking environment)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....

Create Disaster recovery site (Seperate physical location)

Hello, let me just say thank you in advance for any responses.
I am Project Manager currently trying to architect a Disaster Recovery site.
Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
write to a database simultaneously at a totally seperate location? Open to
any hardware or software solutions as well....basically I need to be able to
flip a switch at one location and have my DR site at another location become
active with full functionality Web, App, DB, yes I know thats a tall order
but money is no object....You have two options for the DB: You can use log shipping to get a near
real time standby site or you can use the EMC Mirrorview product to create a
standby data set. Both are somewhat complex but are not impossible to
implement.
Web and App can be load-balanced server farm that just deals with life when
half the universe disappears.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....|||Hi
Look at EMC's SRDF. It is the solution at EMC level. We use if got all our
systems (bug banking environment)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:7EA61344-6406-4CA7-8752-82CDF66CD485@.microsoft.com...
> Hello, let me just say thank you in advance for any responses.
> I am Project Manager currently trying to architect a Disaster Recovery
site.
> Is there a way in SQL 2000 (Clustered) connected to an EMC SAN to have SQL
> write to a database simultaneously at a totally seperate location? Open to
> any hardware or software solutions as well....basically I need to be able
to
> flip a switch at one location and have my DR site at another location
become
> active with full functionality Web, App, DB, yes I know thats a tall order
> but money is no object....

Create device in emulator from Management Studio

Hi,

i know i have the chance to access my mobile device (physical or emulator) from Management Studio.
I went to Connect Object Explorer, server type Sql Server Mobile but i can′t connect to my emulator and i can′t see any way to connect to a physical device either. How can i do this?

The only thing i can do is create a sql server mobile database (.sdf).

Thanks
SP

Moving to Sql Server Compact Edition forum where it has got better chance of being answered.

-Thanks,

Mohit

|||

You can do this only if your device (or emulator) is connected through active sync.

Object Explorer:

Connect->

Database file-> Select 'Browse for more' and then select 'Mobile Device' from the file browser.

If you want to create a new database on the device, then just select 'New database' instead of 'Browse'

Thanks

Pragya

CREATE DATABASE, Msg 5105 physical filename may be incorrect error

I'm reading the book "Programming Microsoft SQL Server 2000 with Microsoft
Visual Basic.Net" (quick review on the book: I laughed; I cried; right now,
I'm doing lots of crying.) When I try to create the database required for
exercises, I receive the error:
"Server: Msg 5105, Level 16, State 2, Line 3
Device activation error. The physical file name
'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
I copied the physical file name location from Windows Explorer so it should
be ok.
I checked the MSKB for any bugs and worked around all that are listed
including the default directory malady.
Any ideas on how to install the database?
Hi
It sounds like you should be attaching the files rather than creating them?
John
"New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
> I'm reading the book "Programming Microsoft SQL Server 2000 with Microsoft
> Visual Basic.Net" (quick review on the book: I laughed; I cried; right
> now,
> I'm doing lots of crying.) When I try to create the database required for
> exercises, I receive the error:
> "Server: Msg 5105, Level 16, State 2, Line 3
> Device activation error. The physical file name
> 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
> I copied the physical file name location from Windows Explorer so it
> should
> be ok.
> I checked the MSKB for any bugs and worked around all that are listed
> including the default directory malady.
> Any ideas on how to install the database?
>

CREATE DATABASE, Msg 5105 physical filename may be incorrect error

I'm reading the book "Programming Microsoft SQL Server 2000 with Microsoft
Visual Basic.Net" (quick review on the book: I laughed; I cried; right now,
I'm doing lots of crying.) When I try to create the database required for
exercises, I receive the error:
"Server: Msg 5105, Level 16, State 2, Line 3
Device activation error. The physical file name
'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
I copied the physical file name location from Windows Explorer so it should
be ok.
I checked the MSKB for any bugs and worked around all that are listed
including the default directory malady.
Any ideas on how to install the database?Hi
It sounds like you should be attaching the files rather than creating them?
John
"New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
> I'm reading the book "Programming Microsoft SQL Server 2000 with Microsoft
> Visual Basic.Net" (quick review on the book: I laughed; I cried; right
> now,
> I'm doing lots of crying.) When I try to create the database required for
> exercises, I receive the error:
> "Server: Msg 5105, Level 16, State 2, Line 3
> Device activation error. The physical file name
> 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
> I copied the physical file name location from Windows Explorer so it
> should
> be ok.
> I checked the MSKB for any bugs and worked around all that are listed
> including the default directory malady.
> Any ideas on how to install the database?
>|||Thanks for the reply, John. The code creates a db for each chapter of the
book and brings in the data and log files for each chapter. It would seem
that the code from the book should run or else I could find lots of compaints
around the web. Since I'm new to the game of SQL Server, I wanted to
discover what missteps that I am taking. Could you look at the code for me
to see if there is any unmentioned setup that I need to do before running
this? Here's the chapter 2 sample:
--CreateSampleDB
--Execute statements from the master database.
USE master
GO
--Drop any prior version of Chapter02 database.
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME = N'Chapter02')
DROP DATABASE Chapter02
GO
--Create new version of Chapter02 database.
CREATE DATABASE Chapter02
ON
(NAME = Chapter02_dat,
FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
SIZE = 1)
LOG ON
(NAME = Chapter02_log,
FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
SIZE = 1,
MAXSIZE = 5)
GO
"John Bell" wrote:
> Hi
> It sounds like you should be attaching the files rather than creating them?
> John
> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
> > I'm reading the book "Programming Microsoft SQL Server 2000 with Microsoft
> > Visual Basic.Net" (quick review on the book: I laughed; I cried; right
> > now,
> > I'm doing lots of crying.) When I try to create the database required for
> > exercises, I receive the error:
> >
> > "Server: Msg 5105, Level 16, State 2, Line 3
> > Device activation error. The physical file name
> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
> >
> > I copied the physical file name location from Windows Explorer so it
> > should
> > be ok.
> > I checked the MSKB for any bugs and worked around all that are listed
> > including the default directory malady.
> >
> > Any ideas on how to install the database?
> >
>
>|||The create database statement looks fine, provided of course that the file
named in the create db does NOT exist at the time you are creating the db.
Create database will try and create the underlying database files, so like
John if the book comes with example database files I would expect to copy
them off the cd from the book and then attach them using sp_attachdb. Have
you a file of the name used in the create database statement already?
Mike John
"New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
news:6913EAAA-948D-443B-A034-C7E9BB391575@.microsoft.com...
> Thanks for the reply, John. The code creates a db for each chapter of the
> book and brings in the data and log files for each chapter. It would seem
> that the code from the book should run or else I could find lots of
> compaints
> around the web. Since I'm new to the game of SQL Server, I wanted to
> discover what missteps that I am taking. Could you look at the code for
> me
> to see if there is any unmentioned setup that I need to do before running
> this? Here's the chapter 2 sample:
> --CreateSampleDB
> --Execute statements from the master database.
> USE master
> GO
> --Drop any prior version of Chapter02 database.
> IF EXISTS (SELECT *
> FROM INFORMATION_SCHEMA.SCHEMATA
> WHERE CATALOG_NAME = N'Chapter02')
> DROP DATABASE Chapter02
> GO
> --Create new version of Chapter02 database.
> CREATE DATABASE Chapter02
> ON
> (NAME = Chapter02_dat,
> FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> SIZE = 1)
> LOG ON
> (NAME = Chapter02_log,
> FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
> SIZE = 1,
> MAXSIZE = 5)
> GO
>
> "John Bell" wrote:
>> Hi
>> It sounds like you should be attaching the files rather than creating
>> them?
>> John
>> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
>> message
>> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
>> > I'm reading the book "Programming Microsoft SQL Server 2000 with
>> > Microsoft
>> > Visual Basic.Net" (quick review on the book: I laughed; I cried; right
>> > now,
>> > I'm doing lots of crying.) When I try to create the database required
>> > for
>> > exercises, I receive the error:
>> >
>> > "Server: Msg 5105, Level 16, State 2, Line 3
>> > Device activation error. The physical file name
>> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
>> >
>> > I copied the physical file name location from Windows Explorer so it
>> > should
>> > be ok.
>> > I checked the MSKB for any bugs and worked around all that are listed
>> > including the default directory malady.
>> >
>> > Any ideas on how to install the database?
>> >
>>|||Hi
This version of the create database statement would error if the file
existed, but your error seems to imply
that the directory does not exist or that your permissions are incorrect.
Even if the file is read only (which you should change if you copied it from
CD!) the file existing would give the same error.
Check out sp_attach_db in Books online.
John
"New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
news:6913EAAA-948D-443B-A034-C7E9BB391575@.microsoft.com...
> Thanks for the reply, John. The code creates a db for each chapter of the
> book and brings in the data and log files for each chapter. It would seem
> that the code from the book should run or else I could find lots of
> compaints
> around the web. Since I'm new to the game of SQL Server, I wanted to
> discover what missteps that I am taking. Could you look at the code for
> me
> to see if there is any unmentioned setup that I need to do before running
> this? Here's the chapter 2 sample:
> --CreateSampleDB
> --Execute statements from the master database.
> USE master
> GO
> --Drop any prior version of Chapter02 database.
> IF EXISTS (SELECT *
> FROM INFORMATION_SCHEMA.SCHEMATA
> WHERE CATALOG_NAME = N'Chapter02')
> DROP DATABASE Chapter02
> GO
> --Create new version of Chapter02 database.
> CREATE DATABASE Chapter02
> ON
> (NAME = Chapter02_dat,
> FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> SIZE = 1)
> LOG ON
> (NAME = Chapter02_log,
> FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
> SIZE = 1,
> MAXSIZE = 5)
> GO
>
> "John Bell" wrote:
>> Hi
>> It sounds like you should be attaching the files rather than creating
>> them?
>> John
>> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
>> message
>> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
>> > I'm reading the book "Programming Microsoft SQL Server 2000 with
>> > Microsoft
>> > Visual Basic.Net" (quick review on the book: I laughed; I cried; right
>> > now,
>> > I'm doing lots of crying.) When I try to create the database required
>> > for
>> > exercises, I receive the error:
>> >
>> > "Server: Msg 5105, Level 16, State 2, Line 3
>> > Device activation error. The physical file name
>> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
>> >
>> > I copied the physical file name location from Windows Explorer so it
>> > should
>> > be ok.
>> > I checked the MSKB for any bugs and worked around all that are listed
>> > including the default directory malady.
>> >
>> > Any ideas on how to install the database?
>> >
>>|||Thanks all for the thoughts. I discovered script below that attaches the
files (unfortunately, I still receive the same Msg 5105 that started this
thread).
I double-checked the mdf and ldf files and they are not set to read only;
however, the file folder is read-only and will not change. Ideas?
New script:--
--AttachSampleDB
--Run the script from the master database.
USE master
--Update the paths for the data and log files so they
--are appropriate for your computer.
EXEC sp_attach_db @.dbname = N'Chapter02',
@.filename1 = N'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
@.filename2 = N'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf'
"John Bell" wrote:
> Hi
> This version of the create database statement would error if the file
> existed, but your error seems to imply
> that the directory does not exist or that your permissions are incorrect.
> Even if the file is read only (which you should change if you copied it from
> CD!) the file existing would give the same error.
> Check out sp_attach_db in Books online.
> John
> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
> news:6913EAAA-948D-443B-A034-C7E9BB391575@.microsoft.com...
> > Thanks for the reply, John. The code creates a db for each chapter of the
> > book and brings in the data and log files for each chapter. It would seem
> > that the code from the book should run or else I could find lots of
> > compaints
> > around the web. Since I'm new to the game of SQL Server, I wanted to
> > discover what missteps that I am taking. Could you look at the code for
> > me
> > to see if there is any unmentioned setup that I need to do before running
> > this? Here's the chapter 2 sample:
> >
> > --CreateSampleDB
> > --Execute statements from the master database.
> > USE master
> > GO
> >
> > --Drop any prior version of Chapter02 database.
> > IF EXISTS (SELECT *
> > FROM INFORMATION_SCHEMA.SCHEMATA
> > WHERE CATALOG_NAME = N'Chapter02')
> > DROP DATABASE Chapter02
> > GO
> >
> > --Create new version of Chapter02 database.
> > CREATE DATABASE Chapter02
> > ON
> > (NAME = Chapter02_dat,
> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> > SIZE = 1)
> > LOG ON
> > (NAME = Chapter02_log,
> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
> > SIZE = 1,
> > MAXSIZE = 5)
> > GO
> >
> >
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> It sounds like you should be attaching the files rather than creating
> >> them?
> >>
> >> John
> >>
> >> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
> >> message
> >> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
> >> > I'm reading the book "Programming Microsoft SQL Server 2000 with
> >> > Microsoft
> >> > Visual Basic.Net" (quick review on the book: I laughed; I cried; right
> >> > now,
> >> > I'm doing lots of crying.) When I try to create the database required
> >> > for
> >> > exercises, I receive the error:
> >> >
> >> > "Server: Msg 5105, Level 16, State 2, Line 3
> >> > Device activation error. The physical file name
> >> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
> >> >
> >> > I copied the physical file name location from Windows Explorer so it
> >> > should
> >> > be ok.
> >> > I checked the MSKB for any bugs and worked around all that are listed
> >> > including the default directory malady.
> >> >
> >> > Any ideas on how to install the database?
> >> >
> >>
> >>
> >>
>
>|||Hi
Both the create and the attach scripts assume you have a directory called
C:\SQLVBNET\Chapter_02, in which the new database files will be put.
Do you have this directory already?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
news:269E8CC4-8FD8-4A81-AE18-B43F972878A0@.microsoft.com...
> Thanks all for the thoughts. I discovered script below that attaches the
> files (unfortunately, I still receive the same Msg 5105 that started this
> thread).
> I double-checked the mdf and ldf files and they are not set to read only;
> however, the file folder is read-only and will not change. Ideas?
> New script:--
> --AttachSampleDB
> --Run the script from the master database.
> USE master
> --Update the paths for the data and log files so they
> --are appropriate for your computer.
> EXEC sp_attach_db @.dbname = N'Chapter02',
> @.filename1 => N'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> @.filename2 => N'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf'
>
> "John Bell" wrote:
>> Hi
>> This version of the create database statement would error if the file
>> existed, but your error seems to imply
>> that the directory does not exist or that your permissions are incorrect.
>> Even if the file is read only (which you should change if you copied it
>> from
>> CD!) the file existing would give the same error.
>> Check out sp_attach_db in Books online.
>> John
>> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
>> message
>> news:6913EAAA-948D-443B-A034-C7E9BB391575@.microsoft.com...
>> > Thanks for the reply, John. The code creates a db for each chapter of
>> > the
>> > book and brings in the data and log files for each chapter. It would
>> > seem
>> > that the code from the book should run or else I could find lots of
>> > compaints
>> > around the web. Since I'm new to the game of SQL Server, I wanted to
>> > discover what missteps that I am taking. Could you look at the code
>> > for
>> > me
>> > to see if there is any unmentioned setup that I need to do before
>> > running
>> > this? Here's the chapter 2 sample:
>> >
>> > --CreateSampleDB
>> > --Execute statements from the master database.
>> > USE master
>> > GO
>> >
>> > --Drop any prior version of Chapter02 database.
>> > IF EXISTS (SELECT *
>> > FROM INFORMATION_SCHEMA.SCHEMATA
>> > WHERE CATALOG_NAME = N'Chapter02')
>> > DROP DATABASE Chapter02
>> > GO
>> >
>> > --Create new version of Chapter02 database.
>> > CREATE DATABASE Chapter02
>> > ON
>> > (NAME = Chapter02_dat,
>> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
>> > SIZE = 1)
>> > LOG ON
>> > (NAME = Chapter02_log,
>> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
>> > SIZE = 1,
>> > MAXSIZE = 5)
>> > GO
>> >
>> >
>> >
>> > "John Bell" wrote:
>> >
>> >> Hi
>> >>
>> >> It sounds like you should be attaching the files rather than creating
>> >> them?
>> >>
>> >> John
>> >>
>> >> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
>> >> > I'm reading the book "Programming Microsoft SQL Server 2000 with
>> >> > Microsoft
>> >> > Visual Basic.Net" (quick review on the book: I laughed; I cried;
>> >> > right
>> >> > now,
>> >> > I'm doing lots of crying.) When I try to create the database
>> >> > required
>> >> > for
>> >> > exercises, I receive the error:
>> >> >
>> >> > "Server: Msg 5105, Level 16, State 2, Line 3
>> >> > Device activation error. The physical file name
>> >> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
>> >> >
>> >> > I copied the physical file name location from Windows Explorer so it
>> >> > should
>> >> > be ok.
>> >> > I checked the MSKB for any bugs and worked around all that are
>> >> > listed
>> >> > including the default directory malady.
>> >> >
>> >> > Any ideas on how to install the database?
>> >> >
>> >>
>> >>
>> >>
>>|||Thank you all for your help. As my name dictates I am really new to the game
and discovered that I transposed the directory names. I feel quite foolish.
Kalen's simple answer made me realize my grievous error.
Thank you.
"Kalen Delaney" wrote:
> Hi
> Both the create and the attach scripts assume you have a directory called
> C:\SQLVBNET\Chapter_02, in which the new database files will be put.
> Do you have this directory already?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
> news:269E8CC4-8FD8-4A81-AE18-B43F972878A0@.microsoft.com...
> > Thanks all for the thoughts. I discovered script below that attaches the
> > files (unfortunately, I still receive the same Msg 5105 that started this
> > thread).
> >
> > I double-checked the mdf and ldf files and they are not set to read only;
> > however, the file folder is read-only and will not change. Ideas?
> >
> > New script:--
> > --AttachSampleDB
> > --Run the script from the master database.
> > USE master
> >
> > --Update the paths for the data and log files so they
> > --are appropriate for your computer.
> > EXEC sp_attach_db @.dbname = N'Chapter02',
> > @.filename1 => > N'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> > @.filename2 => > N'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf'
> >
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> This version of the create database statement would error if the file
> >> existed, but your error seems to imply
> >> that the directory does not exist or that your permissions are incorrect.
> >> Even if the file is read only (which you should change if you copied it
> >> from
> >> CD!) the file existing would give the same error.
> >>
> >> Check out sp_attach_db in Books online.
> >>
> >> John
> >>
> >> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
> >> message
> >> news:6913EAAA-948D-443B-A034-C7E9BB391575@.microsoft.com...
> >> > Thanks for the reply, John. The code creates a db for each chapter of
> >> > the
> >> > book and brings in the data and log files for each chapter. It would
> >> > seem
> >> > that the code from the book should run or else I could find lots of
> >> > compaints
> >> > around the web. Since I'm new to the game of SQL Server, I wanted to
> >> > discover what missteps that I am taking. Could you look at the code
> >> > for
> >> > me
> >> > to see if there is any unmentioned setup that I need to do before
> >> > running
> >> > this? Here's the chapter 2 sample:
> >> >
> >> > --CreateSampleDB
> >> > --Execute statements from the master database.
> >> > USE master
> >> > GO
> >> >
> >> > --Drop any prior version of Chapter02 database.
> >> > IF EXISTS (SELECT *
> >> > FROM INFORMATION_SCHEMA.SCHEMATA
> >> > WHERE CATALOG_NAME = N'Chapter02')
> >> > DROP DATABASE Chapter02
> >> > GO
> >> >
> >> > --Create new version of Chapter02 database.
> >> > CREATE DATABASE Chapter02
> >> > ON
> >> > (NAME = Chapter02_dat,
> >> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf',
> >> > SIZE = 1)
> >> > LOG ON
> >> > (NAME = Chapter02_log,
> >> > FILENAME = 'C:\SQLVBNET\Chapter_02\Chapter02_log.ldf',
> >> > SIZE = 1,
> >> > MAXSIZE = 5)
> >> > GO
> >> >
> >> >
> >> >
> >> > "John Bell" wrote:
> >> >
> >> >> Hi
> >> >>
> >> >> It sounds like you should be attaching the files rather than creating
> >> >> them?
> >> >>
> >> >> John
> >> >>
> >> >> "New to the game" <Newtothegame@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
> >> >> > I'm reading the book "Programming Microsoft SQL Server 2000 with
> >> >> > Microsoft
> >> >> > Visual Basic.Net" (quick review on the book: I laughed; I cried;
> >> >> > right
> >> >> > now,
> >> >> > I'm doing lots of crying.) When I try to create the database
> >> >> > required
> >> >> > for
> >> >> > exercises, I receive the error:
> >> >> >
> >> >> > "Server: Msg 5105, Level 16, State 2, Line 3
> >> >> > Device activation error. The physical file name
> >> >> > 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
> >> >> >
> >> >> > I copied the physical file name location from Windows Explorer so it
> >> >> > should
> >> >> > be ok.
> >> >> > I checked the MSKB for any bugs and worked around all that are
> >> >> > listed
> >> >> > including the default directory malady.
> >> >> >
> >> >> > Any ideas on how to install the database?
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

CREATE DATABASE, Msg 5105 physical filename may be incorrect error

I'm reading the book "Programming Microsoft SQL Server 2000 with Microsoft
Visual Basic.Net" (quick review on the book: I laughed; I cried; right now,
I'm doing lots of crying.) When I try to create the database required for
exercises, I receive the error:
"Server: Msg 5105, Level 16, State 2, Line 3
Device activation error. The physical file name
'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
I copied the physical file name location from Windows Explorer so it should
be ok.
I checked the MSKB for any bugs and worked around all that are listed
including the default directory malady.
Any ideas on how to install the database?Hi
It sounds like you should be attaching the files rather than creating them?
John
"New to the game" <Newtothegame@.discussions.microsoft.com> wrote in message
news:41ED1DE0-799F-4C1F-AE80-D87D52616FCB@.microsoft.com...
> I'm reading the book "Programming Microsoft SQL Server 2000 with Microsoft
> Visual Basic.Net" (quick review on the book: I laughed; I cried; right
> now,
> I'm doing lots of crying.) When I try to create the database required for
> exercises, I receive the error:
> "Server: Msg 5105, Level 16, State 2, Line 3
> Device activation error. The physical file name
> 'C:\SQLVBNET\Chapter_02\Chapter02_dat.mdf' may be incorrect."
> I copied the physical file name location from Windows Explorer so it
> should
> be ok.
> I checked the MSKB for any bugs and worked around all that are listed
> including the default directory malady.
> Any ideas on how to install the database?
>

Create Database ... For Attach

Has anyone ever used the CREATE DATABASE statement with the FOR ATTACH clause?

I have a database with something like 128 physical data files which I have to move (detach, move the physical files and then re-attach). Since the limit for sp_attach_db is 16 files, I am forced to use the FOR ATTACH option. I was wondering if anyone had ever done this and if there were any hints and/or recommendations you might be willing to share...

Regards,

hmscott128 files?

Dude...how big is this thing?

what's the hardware config?

No I haven't done this...|||Sigh, it's another of my "inherited" databases. Worse, it's supposedly a "vendor supported" solution. Let's just not go there.

There are actually two databases on this one server (one has a mere 75 files, the other has 128). The bigger of the two is used to store images of...um...people.

The bigger database is ~240 GB. According to the vendor it will grow by approximately 50 - 80 GB per year. We are in the process of moving it from an "all on board" disk solution to a CX 500 SAN.

Oh, well, I am going to create a sandbox version of the db with ~32 files and play around with detaching an re-attaching it. As always thanks for your response.

Regards,

hmscott

128 files?

Dude...how big is this thing?

what's the hardware config?

No I haven't done this...|||um...people...just images...no mpgs?|||The images are of people you would probably rather not routinely associate with.

hmscott

PS. I just test the process on a bunch of empty files; it's a cinch. Here's the SQL..

Create the initial database on 32 data files and two log files:

-- =============================================
-- Create database on multiple data and transaction log files
-- =============================================
IF EXISTS (SELECT *
FROM master..sysdatabases
WHERE name = N'MultiFile')
DROP DATABASE MultiFile
GO

CREATE DATABASE MultiFile
ON PRIMARY
( NAME = FileName1,
FILENAME = N'e:\MSSQL\Data\FileName1.mdf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName2,
FILENAME = N'e:\MSSQL\Data\FileName2.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName3,
FILENAME = N'e:\MSSQL\Data\FileName3.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName4,
FILENAME = N'e:\MSSQL\Data\FileName4.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName5,
FILENAME = N'e:\MSSQL\Data\FileName5.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName6,
FILENAME = N'e:\MSSQL\Data\FileName6.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName7,
FILENAME = N'e:\MSSQL\Data\FileName7.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName8,
FILENAME = N'e:\MSSQL\Data\FileName8.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName9,
FILENAME = N'e:\MSSQL\Data\FileName9.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName10,
FILENAME = N'e:\MSSQL\Data\FileName10.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName11,
FILENAME = N'e:\MSSQL\Data\FileName11.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName12,
FILENAME = N'e:\MSSQL\Data\FileName12.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName13,
FILENAME = N'e:\MSSQL\Data\FileName13.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName14,
FILENAME = N'e:\MSSQL\Data\FileName14.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName15,
FILENAME = N'e:\MSSQL\Data\FileName15.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName16,
FILENAME = N'e:\MSSQL\Data\FileName16.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName17,
FILENAME = N'e:\MSSQL\Data\FileName17.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName18,
FILENAME = N'e:\MSSQL\Data\FileName18.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName19,
FILENAME = N'e:\MSSQL\Data\FileName19.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName20,
FILENAME = N'e:\MSSQL\Data\FileName20.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName21,
FILENAME = N'e:\MSSQL\Data\FileName21.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName23,
FILENAME = N'e:\MSSQL\Data\FileName23.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName24,
FILENAME = N'e:\MSSQL\Data\FileName24.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName25,
FILENAME = N'e:\MSSQL\Data\FileName25.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName26,
FILENAME = N'e:\MSSQL\Data\FileName26.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName27,
FILENAME = N'e:\MSSQL\Data\FileName27.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName28,
FILENAME = N'e:\MSSQL\Data\FileName28.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName29,
FILENAME = N'e:\MSSQL\Data\FileName29.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName30,
FILENAME = N'e:\MSSQL\Data\FileName30.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName31,
FILENAME = N'e:\MSSQL\Data\FileName31.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName32,
FILENAME = N'e:\MSSQL\Data\FileName32.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)

LOG ON
( NAME = FileName_Log1,
FILENAME = N'e:\MSSQL\Data\FileName_Log1.ldf',
SIZE = 512KB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),

( NAME = FileName_Log2,
FILENAME = N'e:\MSSQL\Data\FileName_Log2.ldf',
SIZE = 512KB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)
GO

Now detach the database and use windows explorer to move the data files and log files to a new directory (in this case e:\test\data).

Now use CREATE DATABASE ... FOR ATTACH to re-attach the database:

CREATE DATABASE MultiFile
ON PRIMARY
(FILENAME = N'e:\Test\Data\FileName1.mdf'),
(FILENAME = N'e:\Test\Data\FileName2.ndf'),
(FILENAME = N'e:\Test\Data\FileName3.ndf'),
(FILENAME = N'e:\Test\Data\FileName4.ndf'),
(FILENAME = N'e:\Test\Data\FileName5.ndf'),
(FILENAME = N'e:\Test\Data\FileName6.ndf'),
(FILENAME = N'e:\Test\Data\FileName7.ndf'),
(FILENAME = N'e:\Test\Data\FileName8.ndf'),
(FILENAME = N'e:\Test\Data\FileName9.ndf'),
(FILENAME = N'e:\Test\Data\FileName10.ndf'),
(FILENAME = N'e:\Test\Data\FileName11.ndf'),
(FILENAME = N'e:\Test\Data\FileName12.ndf'),
(FILENAME = N'e:\Test\Data\FileName13.ndf'),
(FILENAME = N'e:\Test\Data\FileName14.ndf'),
(FILENAME = N'e:\Test\Data\FileName15.ndf'),
(FILENAME = N'e:\Test\Data\FileName16.ndf'),
(FILENAME = N'e:\Test\Data\FileName17.ndf'),
(FILENAME = N'e:\Test\Data\FileName18.ndf'),
(FILENAME = N'e:\Test\Data\FileName19.ndf'),
(FILENAME = N'e:\Test\Data\FileName20.ndf'),
(FILENAME = N'e:\Test\Data\FileName21.ndf'),
(FILENAME = N'e:\Test\Data\FileName23.ndf'),
(FILENAME = N'e:\Test\Data\FileName24.ndf'),
(FILENAME = N'e:\Test\Data\FileName25.ndf'),
(FILENAME = N'e:\Test\Data\FileName26.ndf'),
(FILENAME = N'e:\Test\Data\FileName27.ndf'),
(FILENAME = N'e:\Test\Data\FileName28.ndf'),
(FILENAME = N'e:\Test\Data\FileName29.ndf'),
(FILENAME = N'e:\Test\Data\FileName30.ndf'),
(FILENAME = N'e:\Test\Data\FileName31.ndf'),
(FILENAME = N'e:\Test\Data\FileName32.ndf')

LOG ON
(FILENAME = N'e:\Test\Data\FileName_Log1.ldf'),
(FILENAME = N'e:\Test\Data\FileName_Log2.ldf')

FOR ATTACH
GO

um...people...just images...no mpgs?|||Very nice...so it's not an adult site?|||Nope, not an adult site (heehee).

Things went well this weekend. Moved both databases successfully and then re-attached them with the FOR ATTACH option. It was surprisingly painless all considered.

Regards,

hmscott
Very nice...so it's not an adult site?