create db

Hi
How can i create and attach a database with out using
enterprise managaer in mssql7.
hi shif,
To create a database you can make use of t-sql command "create database". If
you want to create a database without using enterprise manager you will have
to use above command with the help of application tools like "query
analyzer" OR command line utility like osql.
A typical syntax of create database will look as follows.
CREATE DATABASE <database_name>
ON
( NAME = logical_file_name ,
FILENAME = 'os_file_name_path' ,
SIZE = size_of_file ,
MAXSIZE = max_size_of_file, --max size to which file can grow.
FILEGROWTH = growth_increment_of_file )
LOG ON
( NAME = logical_file_name,
FILENAME = 'os_file_name_path',
SIZE = size_of_file ,
MAXSIZE = max_size_of_file, --max size to which file can grow.
FILEGROWTH = growth_increment_of_file )
GO
If you want to attach a database with the existing data and log files. you
can make use of clause "FOR ATTACH" in create database command. OR you can
make use of system stored procedure sp_attach_db.
See more help on both of the above commands and its explaination in Books
online
Vishal Parkar
vgparkar@.yahoo.co.in

Create Database With Attach in Restricted_user mode?

How can I attach a database and keep it in Restricted_User mode? I
cannot find a way to do this in BOL.
-- RBarryYoungTry attaching the db and then use "alter database" and set the option
"RESTRICTED_USER".
AMB
"RBarryYoung@.gmail.com" wrote:
> How can I attach a database and keep it in Restricted_User mode? I
> cannot find a way to do this in BOL.
> -- RBarryYoung
>|||No, this leaves a window of oppurtunity between the attach and the
"Alter Database". I need to "keep it" in Restricted mode, as it was
when I detached it, similar to the RESTRICTED_USER/DBO_ONLY keywords
for RETORE operations.
It is not supposed to be accessible by anyone until I am done making
the necessary changes to it.
Alejandro Mesa wrote:
> Try attaching the db and then use "alter database" and set the option
> "RESTRICTED_USER".
>
> AMB
>
> "RBarryYoung@.gmail.com" wrote:
> > How can I attach a database and keep it in Restricted_User mode? I
> > cannot find a way to do this in BOL.
> >
> > -- RBarryYoung
> >
> >|||<RBarryYoung@.gmail.com> wrote in message
news:1150144741.478126.196290@.f6g2000cwb.googlegroups.com...
> No, this leaves a window of oppurtunity between the attach and the
> "Alter Database". I need to "keep it" in Restricted mode, as it was
> when I detached it, similar to the RESTRICTED_USER/DBO_ONLY keywords
> for RETORE operations.
> It is not supposed to be accessible by anyone until I am done making
> the necessary changes to it.
Haven't tried this, but wrap the whole thing in a transaction?
> Alejandro Mesa wrote:
> > Try attaching the db and then use "alter database" and set the option
> > "RESTRICTED_USER".
> >
> >
> > AMB
> >
> >
> >
> > "RBarryYoung@.gmail.com" wrote:
> >
> > > How can I attach a database and keep it in Restricted_User mode? I
> > > cannot find a way to do this in BOL.
> > >
> > > -- RBarryYoung
> > >
> > >
>

Create Database With Attach in Restricted_user mode?

Try attaching the db and then use "alter database" and set the option
"RESTRICTED_USER".
AMB
"RBarryYoung@.gmail.com" wrote:

> How can I attach a database and keep it in Restricted_User mode? I
> cannot find a way to do this in BOL.
> -- RBarryYoung
>How can I attach a database and keep it in Restricted_User mode? I
cannot find a way to do this in BOL.
-- RBarryYoung|||Try attaching the db and then use "alter database" and set the option
"RESTRICTED_USER".
AMB
"RBarryYoung@.gmail.com" wrote:

> How can I attach a database and keep it in Restricted_User mode? I
> cannot find a way to do this in BOL.
> -- RBarryYoung
>|||No, this leaves a window of oppurtunity between the attach and the
"Alter Database". I need to "keep it" in Restricted mode, as it was
when I detached it, similar to the RESTRICTED_USER/DBO_ONLY keywords
for RETORE operations.
It is not supposed to be accessible by anyone until I am done making
the necessary changes to it.
Alejandro Mesa wrote:[vbcol=seagreen]
> Try attaching the db and then use "alter database" and set the option
> "RESTRICTED_USER".
>
> AMB
>
> "RBarryYoung@.gmail.com" wrote:
>|||No, this leaves a window of oppurtunity between the attach and the
"Alter Database". I need to "keep it" in Restricted mode, as it was
when I detached it, similar to the RESTRICTED_USER/DBO_ONLY keywords
for RETORE operations.
It is not supposed to be accessible by anyone until I am done making
the necessary changes to it.
Alejandro Mesa wrote:[vbcol=seagreen]
> Try attaching the db and then use "alter database" and set the option
> "RESTRICTED_USER".
>
> AMB
>
> "RBarryYoung@.gmail.com" wrote:
>|||<RBarryYoung@.gmail.com> wrote in message
news:1150144741.478126.196290@.f6g2000cwb.googlegroups.com...
> No, this leaves a window of oppurtunity between the attach and the
> "Alter Database". I need to "keep it" in Restricted mode, as it was
> when I detached it, similar to the RESTRICTED_USER/DBO_ONLY keywords
> for RETORE operations.
> It is not supposed to be accessible by anyone until I am done making
> the necessary changes to it.
Haven't tried this, but wrap the whole thing in a transaction?

> Alejandro Mesa wrote:
>|||<RBarryYoung@.gmail.com> wrote in message
news:1150144741.478126.196290@.f6g2000cwb.googlegroups.com...
> No, this leaves a window of oppurtunity between the attach and the
> "Alter Database". I need to "keep it" in Restricted mode, as it was
> when I detached it, similar to the RESTRICTED_USER/DBO_ONLY keywords
> for RETORE operations.
> It is not supposed to be accessible by anyone until I am done making
> the necessary changes to it.
Haven't tried this, but wrap the whole thing in a transaction?

> Alejandro Mesa wrote:
>

Create database in SQL Server 2005 and Attaching it in SQL Express

Hi,

I've created a database in sql server 2005, but now I need to detach this database and attach it in sql express. Is this possible? I keep getting an error that my database is readonly. Any help would be greatly appreciated.

thanks in advance.

Yes, it is possible.

Sometimes, when the database file is detached, the OS changes the file permissions.

Using Windows Explorer, check the file properties, and remove the Read Only checkmark if one is present. Then re-attach.

|||

or you can use Backup/Restore method. Restore the database with MOVe option. Check Restore syntax in BOL

Madhu

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?