Logging query errors: files

Showing posts with label files. Show all posts

Friday, February 24, 2012

Logical & Physical files relationships

I have two sql backup files, a and b, of the same database but at different
stages of development.
So, I restore backup a, OK. I then restore backup b, the logical name is the
same as a but the physical name is different.
1. will logical b overwrite logical a?
2. if not, can someone please explain the relationship between sql
server, logical and physical files
Thank you
Frank AshleyI suspect that logical a would disappear when you restore logical b, because
the database is deleted and re-created.
When you create a database, you will have at least 2 physical files. Each of
the files has a logical name. The logical name is used to refer to the files
when using the Alter database modify file commands.(ie Alter database prod
modify file(name=mydatafile, size = 20)
..You may add more physical fles, and delete files as well. At some point in
time your databases was probably dropped and re-created with a different
physical location for the files, using the same logical file names.
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
> I have two sql backup files, a and b, of the same database but at
different
> stages of development.
> So, I restore backup a, OK. I then restore backup b, the logical name is
the
> same as a but the physical name is different.
> 1. will logical b overwrite logical a?
> 2. if not, can someone please explain the relationship between sql
> server, logical and physical files
> Thank you
> Frank Ashley
>|||In addition to Wayne's post:
You can have two databases with the same logical database file names. Logical file named need to be
unique *per database* only.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
>I have two sql backup files, a and b, of the same database but at different
> stages of development.
> So, I restore backup a, OK. I then restore backup b, the logical name is the
> same as a but the physical name is different.
> 1. will logical b overwrite logical a?
> 2. if not, can someone please explain the relationship between sql
> server, logical and physical files
> Thank you
> Frank Ashley
>|||So Wayne's premise that
'I suspect that logical a would disappear when you restore logical b,
because
the database is deleted and re-created.'
is incorrect?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ecIybMUrEHA.2024@.TK2MSFTNGP10.phx.gbl...
> In addition to Wayne's post:
> You can have two databases with the same logical database file names.
Logical file named need to be
> unique *per database* only.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
> news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
> >I have two sql backup files, a and b, of the same database but at
different
> > stages of development.
> >
> > So, I restore backup a, OK. I then restore backup b, the logical name is
the
> > same as a but the physical name is different.
> >
> > 1. will logical b overwrite logical a?
> > 2. if not, can someone please explain the relationship between sql
> > server, logical and physical files
> >
> > Thank you
> > Frank Ashley
> >
> >
>|||It looks to me like Wayne assumes you are overwriting an existing database
during the restore. In this case, he is correct that the existing database
will be overwritten, including logical file names. Understand that this
will not affect other existing databases because the scope of logical file
names is per-database. You can restore to a different database name and it
can have the same logical file names as existing databases.
>> > 2. if not, can someone please explain the relationship between sql
>> > server, logical and physical files
In summary:
1) a restored database is exactly like the backed-up database except that
the database name and physical file names may be changed during the restore
2) database names must be unique per SQL Server instance
3) logical file names must be unique per database
4) physical file names must be unique per server
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:OAqSAnVrEHA.756@.TK2MSFTNGP11.phx.gbl...
> So Wayne's premise that
> 'I suspect that logical a would disappear when you restore logical b,
> because
> the database is deleted and re-created.'
> is incorrect?
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:ecIybMUrEHA.2024@.TK2MSFTNGP10.phx.gbl...
>> In addition to Wayne's post:
>> You can have two databases with the same logical database file names.
> Logical file named need to be
>> unique *per database* only.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
>> news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
>> >I have two sql backup files, a and b, of the same database but at
> different
>> > stages of development.
>> >
>> > So, I restore backup a, OK. I then restore backup b, the logical name
>> > is
> the
>> > same as a but the physical name is different.
>> >
>> > 1. will logical b overwrite logical a?
>> > 2. if not, can someone please explain the relationship between sql
>> > server, logical and physical files
>> >
>> > Thank you
>> > Frank Ashley
>> >
>> >
>>
>|||Thanks Dan,
That's exactly what I was after (and suspected).
I had always assumed though that SQL Server used its logical names to
'look-up' the physical files, which is why I was confused as to the
'uniqueness' of logical files in a SQl Server instance. Could you shed any
light on the matter for me? How does SQL Server map its instance and logical
and physical files?
Thank you
Frank Ashley
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uvqkfKirEHA.736@.tk2msftngp13.phx.gbl...
> It looks to me like Wayne assumes you are overwriting an existing database
> during the restore. In this case, he is correct that the existing
database
> will be overwritten, including logical file names. Understand that this
> will not affect other existing databases because the scope of logical file
> names is per-database. You can restore to a different database name and
it
> can have the same logical file names as existing databases.
> >> > 2. if not, can someone please explain the relationship between sql
> >> > server, logical and physical files
> In summary:
> 1) a restored database is exactly like the backed-up database except that
> the database name and physical file names may be changed during the
restore
> 2) database names must be unique per SQL Server instance
> 3) logical file names must be unique per database
> 4) physical file names must be unique per server
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
> news:OAqSAnVrEHA.756@.TK2MSFTNGP11.phx.gbl...
> > So Wayne's premise that
> >
> > 'I suspect that logical a would disappear when you restore logical b,
> > because
> > the database is deleted and re-created.'
> >
> > is incorrect?
> >
> >
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> > in
> > message news:ecIybMUrEHA.2024@.TK2MSFTNGP10.phx.gbl...
> >> In addition to Wayne's post:
> >>
> >> You can have two databases with the same logical database file names.
> > Logical file named need to be
> >> unique *per database* only.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
> >> news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
> >> >I have two sql backup files, a and b, of the same database but at
> > different
> >> > stages of development.
> >> >
> >> > So, I restore backup a, OK. I then restore backup b, the logical name
> >> > is
> > the
> >> > same as a but the physical name is different.
> >> >
> >> > 1. will logical b overwrite logical a?
> >> > 2. if not, can someone please explain the relationship between sql
> >> > server, logical and physical files
> >> >
> >> > Thank you
> >> > Frank Ashley
> >> >
> >> >
> >>
> >>
> >
> >
>|||Logical file names are only used in Transact-SQL statements (e.g. RESTORE,
DBCC, CREATE/ALTER DATABASE, etc.) as user-friendly names to 'look-up'
information in the system tables. These Transact-SQL statements are
database-specific so this is essentially why logical names only need to be
unique within the context of a specific database.
The logical file name and fileid both uniquely identify a file within a
database. This information is stored in the master database sysaltfiles
table along with the database id. The combination of dbid and either
logical name or fileid uniquely identify a file in the sysaltfiles table.
Database file information is also stored in each database in the sysfiles
system table, with a row for each file belonging to that database.
See the Books Online <Books\architec.chm::/8_ar_da2_9sab.htm>.for more
information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:%23Gs6XFtrEHA.1232@.TK2MSFTNGP11.phx.gbl...
> Thanks Dan,
> That's exactly what I was after (and suspected).
> I had always assumed though that SQL Server used its logical names to
> 'look-up' the physical files, which is why I was confused as to the
> 'uniqueness' of logical files in a SQl Server instance. Could you shed any
> light on the matter for me? How does SQL Server map its instance and
> logical
> and physical files?
>
> Thank you
> Frank Ashley
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uvqkfKirEHA.736@.tk2msftngp13.phx.gbl...
>> It looks to me like Wayne assumes you are overwriting an existing
>> database
>> during the restore. In this case, he is correct that the existing
> database
>> will be overwritten, including logical file names. Understand that this
>> will not affect other existing databases because the scope of logical
>> file
>> names is per-database. You can restore to a different database name and
> it
>> can have the same logical file names as existing databases.
>> >> > 2. if not, can someone please explain the relationship between sql
>> >> > server, logical and physical files
>> In summary:
>> 1) a restored database is exactly like the backed-up database except
>> that
>> the database name and physical file names may be changed during the
> restore
>> 2) database names must be unique per SQL Server instance
>> 3) logical file names must be unique per database
>> 4) physical file names must be unique per server
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
>> news:OAqSAnVrEHA.756@.TK2MSFTNGP11.phx.gbl...
>> > So Wayne's premise that
>> >
>> > 'I suspect that logical a would disappear when you restore logical
>> > b,
>> > because
>> > the database is deleted and re-created.'
>> >
>> > is incorrect?
>> >
>> >
>> >
>> >
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> > wrote
>> > in
>> > message news:ecIybMUrEHA.2024@.TK2MSFTNGP10.phx.gbl...
>> >> In addition to Wayne's post:
>> >>
>> >> You can have two databases with the same logical database file names.
>> > Logical file named need to be
>> >> unique *per database* only.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
>> >> news:%23rqQASSrEHA.3428@.TK2MSFTNGP11.phx.gbl...
>> >> >I have two sql backup files, a and b, of the same database but at
>> > different
>> >> > stages of development.
>> >> >
>> >> > So, I restore backup a, OK. I then restore backup b, the logical
>> >> > name
>> >> > is
>> > the
>> >> > same as a but the physical name is different.
>> >> >
>> >> > 1. will logical b overwrite logical a?
>> >> > 2. if not, can someone please explain the relationship between
>> >> > sql
>> >> > server, logical and physical files
>> >> >
>> >> > Thank you
>> >> > Frank Ashley
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

Monday, February 20, 2012

logging the DDL stmts

Hi :

I am using .cmd files to execute .sql files.

sqlcmd is used in .cmd files to execute the .sql stmts.

In .cmd file the sqlcmd line of code is as follows:

sqlcmd -i .\..\..\sql\tables\create_employee_table.sql

In .sql file the ddl stmt is as follows:

CREATE TABLE [Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[NationalIDNumber] [nvarchar](15) COLLATE Latin1_General_CS_AI NOT NULL,
[ContactID] [int] NOT NULL,
[LoginID] [nvarchar](256) COLLATE Latin1_General_CS_AI NOT NULL,
[ManagerID] [int] NULL,
DF_Employee_rowguid] DEFAULT (newid()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

DDL used are create and drop of tables and indexes.

How do i log all the ddl stmts executed into some .log file (xyz.log)?

The log file should read something like,

employee table created sucessfully

employee table dropped sucessfully.

...................

sqlcmd -o c:\log\xyz.log , gives me only the output for dml stmts (like select * from emp).. anything other than this will be very helpful.

Any solutions will be of great help.

This is a broader SQL question than just SQL Express so I'm moving it to the Database Engine forum; I think you'll find a better answer there.

In looking around a bit, I found information about DDL Triggers which would seem to do what you suggest. The folks in the other forum can validate my guess.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

From the sounds of it you just want output in a text file to know whether or not your create table statement succeeded or not. DDL triggers can do this on the server, however if you have malformed sql (as in your post) or just want to log out to a text file directly from sqlcmd have a look at the -r parameter

sqlcmd -icreate_employee_table.sql -S. -E -o create_employee_table.log -r1

The file "create_employee_table.log" will contain output like this:

Msg 102, Level 15, State 1, Server name, Line 7
Incorrect syntax near ']'.
Msg 319, Level 15, State 1, Server name, Line 11
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

To print out success messages you'll need to actually check the value of the @.@.error in the script and print out an appropriate message.

CREATE TABLE [Employee](
[EmployeeID] int IDENTITY(1,1) NOT NULL,
[NationalIDNumber] nvarchar(15) COLLATE Latin1_General_CS_AI NOT NULL,
[ContactID] int NOT NULL,
[LoginID] nvarchar(256) COLLATE Latin1_General_CS_AI NOT NULL,
[ManagerID] int NULL,
[DF_Employee_rowguid] UNIQUEIDENTIFIER DEFAULT (newid()),
[ModifiedDate] datetime NOT NULL CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

if @.@.error = 0
print 'employee table created sucessfully'

Friday, February 24, 2012

Logical & Physical files relationships

Logical & Physical files relationships

Monday, February 20, 2012

logging the DDL stmts

Logging query errors

Blog Archive

About Me