Hi All,
We seem to have an issue with our backup routine for one of the databases.
On a daily basis, we have one of our databases back up to disk. However, I
noticed that our backup file is huge (39GB!). I checked the database, and
it says it is only using 586MB of space. I also checked the transaction
log, and it says it is using 100MB of space.
We have a weekly backup of the database, and it only takes up 921MB, so I
can't see why the daily backup takes up so much room. Does it append to the
existing backup? If so, how can I selectively remove old backups from it?
I looked at the backup routine, and here's the SQL that is run:
BACKUP DATABASE [NAPROD] TO DISK = N'D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\NAPROD daily production backup.BAK' WITH NOINIT ,
NOUNLOAD , NAME = N'NAPROD daily production backup ', NOSKIP , STATS = 10, DESCRIPTION = N'Navision production database backup ', NOFORMAT
Looking at it, I can't see any problem. I'd really like to get this file
down to something more manageable, as it is killing our backup program.
Any ideas would be appreciated! :)
Thanks!
Brian.Change the NOINIT to INIT.
--
Andrew J. Kelly
SQL Server MVP
"Brian Piotrowski" <bpiotrowski@.simcoeparts.com> wrote in message
news:%23xbCmNvRDHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> We seem to have an issue with our backup routine for one of the databases.
> On a daily basis, we have one of our databases back up to disk. However,
I
> noticed that our backup file is huge (39GB!). I checked the database, and
> it says it is only using 586MB of space. I also checked the transaction
> log, and it says it is using 100MB of space.
> We have a weekly backup of the database, and it only takes up 921MB, so I
> can't see why the daily backup takes up so much room. Does it append to
the
> existing backup? If so, how can I selectively remove old backups from it?
> I looked at the backup routine, and here's the SQL that is run:
> BACKUP DATABASE [NAPROD] TO DISK = N'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\NAPROD daily production backup.BAK' WITH NOINIT ,
> NOUNLOAD , NAME = N'NAPROD daily production backup ', NOSKIP , STATS => 10, DESCRIPTION = N'Navision production database backup ', NOFORMAT
> Looking at it, I can't see any problem. I'd really like to get this file
> down to something more manageable, as it is killing our backup program.
> Any ideas would be appreciated! :)
> Thanks!
> Brian.
>
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Tuesday, March 27, 2012
Ability to import Data into SQL Server Express Edition
I am using SQL Server 2005 Express Edition and Server Mangement Studio
Express. I can connect to the server and create databases/tables just fine
but I can't seem to fine a way to import data into the table. The Data
Transformation wizard does not seem to be in the Management studio. Can
anybody give me some help in how to import data into express edition? Thanks,
Using the SQL 2000 version I use bcp (bulk copy) command line tool.
Don't know if the command is included in the 2005 version.
Express. I can connect to the server and create databases/tables just fine
but I can't seem to fine a way to import data into the table. The Data
Transformation wizard does not seem to be in the Management studio. Can
anybody give me some help in how to import data into express edition? Thanks,
Using the SQL 2000 version I use bcp (bulk copy) command line tool.
Don't know if the command is included in the 2005 version.
Ability to import Data into SQL Server Express Edition
I am using SQL Server 2005 Express Edition and Server Mangement Studio
Express. I can connect to the server and create databases/tables just fine
but I can't seem to fine a way to import data into the table. The Data
Transformation wizard does not seem to be in the Management studio. Can
anybody give me some help in how to import data into express edition? Thank
s,Using the SQL 2000 version I use bcp (bulk copy) command line tool.
Don't know if the command is included in the 2005 version.
Express. I can connect to the server and create databases/tables just fine
but I can't seem to fine a way to import data into the table. The Data
Transformation wizard does not seem to be in the Management studio. Can
anybody give me some help in how to import data into express edition? Thank
s,Using the SQL 2000 version I use bcp (bulk copy) command line tool.
Don't know if the command is included in the 2005 version.
Ability to import Data into SQL Server Express Edition
I am using SQL Server 2005 Express Edition and Server Mangement Studio
Express. I can connect to the server and create databases/tables just fine
but I can't seem to fine a way to import data into the table. The Data
Transformation wizard does not seem to be in the Management studio. Can
anybody give me some help in how to import data into express edition? Thanks,Using the SQL 2000 version I use bcp (bulk copy) command line tool.
Don't know if the command is included in the 2005 version.
Express. I can connect to the server and create databases/tables just fine
but I can't seem to fine a way to import data into the table. The Data
Transformation wizard does not seem to be in the Management studio. Can
anybody give me some help in how to import data into express edition? Thanks,Using the SQL 2000 version I use bcp (bulk copy) command line tool.
Don't know if the command is included in the 2005 version.
Thursday, March 22, 2012
A View That Join 2 Tables in different Databases
Hi Everyone
Could i make a view or stored procedure that join between Two Tables in
Different DataBases in the Same Server Machine?
& If Yes How Can I Do it??
Thx in Adv.
Yes you can.
Here's an example code:
select * from DatabaseA..Orders
Union
select * from DatabaseB..Orders
Hope it can help you.
Regards,
Robert Lie
Mariame wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it??
> Thx in Adv.
>
|||select column_list
from db1.owner.table1 a
(inner) join
db2.owner.table2 b
on a.join_columns = b.join_columns
hth
Quentin
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:OT$cp2$dFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it??
> Thx in Adv.
>
|||Sure.
Example:
use northwind
go
select customerid, companyname
into pubs.dbo.t1
from dbo.customers
go
create index ix_nc_u_t1_customerid on pubs.dbo.t1(customerid asc)
go
create view dbo.vw_v1
as
select
oh.customerid, c.companyname, oh.orderid, oh.orderdate
from
dbo.orders as oh
inner join
pubs.dbo.t1 as c
on oh.customerid = c.customerid
go
select customerid, companyname, orderid, orderdate
from dbo.vw_v1
where customerid = 'alfki'
go
drop view dbo.vw_v1
go
drop table pubs.dbo.t1
go
AMB
"Mariame" wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it??
> Thx in Adv.
>
>
|||Yes. Sure we will link Other databases
ex:
select Table1.Column1 , Table2.Column1
from Table1 , OtherDB..Table2 Table2
where < Give Condition >
Hope this will help
Herbert
"Mariame" wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it??
> Thx in Adv.
>
>
Could i make a view or stored procedure that join between Two Tables in
Different DataBases in the Same Server Machine?
& If Yes How Can I Do it??
Thx in Adv.
Yes you can.
Here's an example code:
select * from DatabaseA..Orders
Union
select * from DatabaseB..Orders
Hope it can help you.
Regards,
Robert Lie
Mariame wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it??
> Thx in Adv.
>
|||select column_list
from db1.owner.table1 a
(inner) join
db2.owner.table2 b
on a.join_columns = b.join_columns
hth
Quentin
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:OT$cp2$dFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it??
> Thx in Adv.
>
|||Sure.
Example:
use northwind
go
select customerid, companyname
into pubs.dbo.t1
from dbo.customers
go
create index ix_nc_u_t1_customerid on pubs.dbo.t1(customerid asc)
go
create view dbo.vw_v1
as
select
oh.customerid, c.companyname, oh.orderid, oh.orderdate
from
dbo.orders as oh
inner join
pubs.dbo.t1 as c
on oh.customerid = c.customerid
go
select customerid, companyname, orderid, orderdate
from dbo.vw_v1
where customerid = 'alfki'
go
drop view dbo.vw_v1
go
drop table pubs.dbo.t1
go
AMB
"Mariame" wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it??
> Thx in Adv.
>
>
|||Yes. Sure we will link Other databases
ex:
select Table1.Column1 , Table2.Column1
from Table1 , OtherDB..Table2 Table2
where < Give Condition >
Hope this will help
Herbert
"Mariame" wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it??
> Thx in Adv.
>
>
A View That Join 2 Tables in different Databases
Hi Everyone
Could i make a view or stored procedure that join between Two Tables in
Different DataBases in the Same Server Machine?
& If Yes How Can I Do it'?
Thx in Adv.Yes you can.
Here's an example code:
select * from DatabaseA..Orders
Union
select * from DatabaseB..Orders
Hope it can help you.
Regards,
Robert Lie
Mariame wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>|||select column_list
from db1.owner.table1 a
(inner) join
db2.owner.table2 b
on a.join_columns = b.join_columns
hth
Quentin
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:OT$cp2$dFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>|||Sure.
Example:
use northwind
go
select customerid, companyname
into pubs.dbo.t1
from dbo.customers
go
create index ix_nc_u_t1_customerid on pubs.dbo.t1(customerid asc)
go
create view dbo.vw_v1
as
select
oh.customerid, c.companyname, oh.orderid, oh.orderdate
from
dbo.orders as oh
inner join
pubs.dbo.t1 as c
on oh.customerid = c.customerid
go
select customerid, companyname, orderid, orderdate
from dbo.vw_v1
where customerid = 'alfki'
go
drop view dbo.vw_v1
go
drop table pubs.dbo.t1
go
AMB
"Mariame" wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>
>|||Yes. Sure we will link Other databases
ex:
select Table1.Column1 , Table2.Column1
from Table1 , OtherDB..Table2 Table2
where < Give Condition >
Hope this will help
Herbert
"Mariame" wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>
>sql
Could i make a view or stored procedure that join between Two Tables in
Different DataBases in the Same Server Machine?
& If Yes How Can I Do it'?
Thx in Adv.Yes you can.
Here's an example code:
select * from DatabaseA..Orders
Union
select * from DatabaseB..Orders
Hope it can help you.
Regards,
Robert Lie
Mariame wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>|||select column_list
from db1.owner.table1 a
(inner) join
db2.owner.table2 b
on a.join_columns = b.join_columns
hth
Quentin
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:OT$cp2$dFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>|||Sure.
Example:
use northwind
go
select customerid, companyname
into pubs.dbo.t1
from dbo.customers
go
create index ix_nc_u_t1_customerid on pubs.dbo.t1(customerid asc)
go
create view dbo.vw_v1
as
select
oh.customerid, c.companyname, oh.orderid, oh.orderdate
from
dbo.orders as oh
inner join
pubs.dbo.t1 as c
on oh.customerid = c.customerid
go
select customerid, companyname, orderid, orderdate
from dbo.vw_v1
where customerid = 'alfki'
go
drop view dbo.vw_v1
go
drop table pubs.dbo.t1
go
AMB
"Mariame" wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>
>|||Yes. Sure we will link Other databases
ex:
select Table1.Column1 , Table2.Column1
from Table1 , OtherDB..Table2 Table2
where < Give Condition >
Hope this will help
Herbert
"Mariame" wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>
>sql
A View That Join 2 Tables in different Databases
Hi Everyone
Could i make a view or stored procedure that join between Two Tables in
Different DataBases in the Same Server Machine?
& If Yes How Can I Do it'?
Thx in Adv.Yes you can.
Here's an example code:
select * from DatabaseA..Orders
Union
select * from DatabaseB..Orders
Hope it can help you.
Regards,
Robert Lie
Mariame wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>|||select column_list
from db1.owner.table1 a
(inner) join
db2.owner.table2 b
on a.join_columns = b.join_columns
hth
Quentin
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:OT$cp2$dFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>|||Sure.
Example:
use northwind
go
select customerid, companyname
into pubs.dbo.t1
from dbo.customers
go
create index ix_nc_u_t1_customerid on pubs.dbo.t1(customerid asc)
go
create view dbo.vw_v1
as
select
oh.customerid, c.companyname, oh.orderid, oh.orderdate
from
dbo.orders as oh
inner join
pubs.dbo.t1 as c
on oh.customerid = c.customerid
go
select customerid, companyname, orderid, orderdate
from dbo.vw_v1
where customerid = 'alfki'
go
drop view dbo.vw_v1
go
drop table pubs.dbo.t1
go
AMB
"Mariame" wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>
>|||Yes. Sure we will link Other databases
ex:
select Table1.Column1 , Table2.Column1
from Table1 , OtherDB..Table2 Table2
where < Give Condition >
Hope this will help
Herbert
"Mariame" wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>
>
Could i make a view or stored procedure that join between Two Tables in
Different DataBases in the Same Server Machine?
& If Yes How Can I Do it'?
Thx in Adv.Yes you can.
Here's an example code:
select * from DatabaseA..Orders
Union
select * from DatabaseB..Orders
Hope it can help you.
Regards,
Robert Lie
Mariame wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>|||select column_list
from db1.owner.table1 a
(inner) join
db2.owner.table2 b
on a.join_columns = b.join_columns
hth
Quentin
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:OT$cp2$dFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>|||Sure.
Example:
use northwind
go
select customerid, companyname
into pubs.dbo.t1
from dbo.customers
go
create index ix_nc_u_t1_customerid on pubs.dbo.t1(customerid asc)
go
create view dbo.vw_v1
as
select
oh.customerid, c.companyname, oh.orderid, oh.orderdate
from
dbo.orders as oh
inner join
pubs.dbo.t1 as c
on oh.customerid = c.customerid
go
select customerid, companyname, orderid, orderdate
from dbo.vw_v1
where customerid = 'alfki'
go
drop view dbo.vw_v1
go
drop table pubs.dbo.t1
go
AMB
"Mariame" wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>
>|||Yes. Sure we will link Other databases
ex:
select Table1.Column1 , Table2.Column1
from Table1 , OtherDB..Table2 Table2
where < Give Condition >
Hope this will help
Herbert
"Mariame" wrote:
> Hi Everyone
> Could i make a view or stored procedure that join between Two Tables in
> Different DataBases in the Same Server Machine?
> & If Yes How Can I Do it'?
> Thx in Adv.
>
>
A vexing issue
Strange problem that I just noticed. I use a handful of databases in SQL2005
Express for development. All of these databases except the one I've been
using most recently "cannot be opened due to inaccessible files or
insufficient memory'.
All of the databases show "recovery_pending" for status.
I tried to set them online with alter database, but get the message:
"File activation failure. The physical file name "...\mydb_log.ldf" may be
incorrect."
Looking at all of the "recovery_pending" databases, I see that all of the
log files are showing as "may be incorrect."
sp_helpdb gives me the message: "No permission to access database <mydb>"
DBCC CheckDB gives me the same message.
Thinking back through what has occured in the last few months (since these
were last used), I've done some minor system maintenance such as defrag and
Windows Updates, but nothing else has been changed. And the files still
reside in the directory where the log file is pointed to.
I'm baffled as to how to get these out of recovery, so any advice is
appreciated.
Thanks for the ideas Tibor. Nothing I could do would get those DBs out of
"recovery pending". But tonight, on the off-chance that it might work, I
went ahead and tried to detach. It gave me an error message that it could
not close the files, but in fact, once I refreshed, it HAD detached the
files! Just to confirm that they were actually detached, I created a new
directory and moved the .mdf and .ldf files in there with no problem. I then
re-attached and the previously in-recovery databases are functioning
normally.
It certainly seems strange that Microsoft did not provide an escape hatch
for this "Recovery Pending", i.e., in limbo, scenario.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:6B5D9B92-E6AC-485B-9053-E9BF2B109A94@.microsoft.com...
> Recovery Pending means that SQL Server need to do recovery for the
> database to bring it to a consistent state. Recovery consists of REDO and
> UNDO, both parts are based on the entries in the transaction log (ldf
> file). SQL Server feel that the ldf files are fishy or something similar,
> and hence it cannot do recovery.
> In most cases when I see something similar, it was somebody who deleted
> the log file or used some other tool to reduce file size. If you are 100%
> certain that something like this didn't happen, then you should carefully
> investigate the SQL Server errorlog file for all related error messages.
> Also investigate the Windows eventlog for clues to what has happened to
> these files. Int he end, if you cannot get these files back to healthy
> state, you are in for RESTORE DATABASE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Earl" <earl@.nospam.com> wrote in message
> news:uiesseQjIHA.5160@.TK2MSFTNGP05.phx.gbl...
>
Express for development. All of these databases except the one I've been
using most recently "cannot be opened due to inaccessible files or
insufficient memory'.
All of the databases show "recovery_pending" for status.
I tried to set them online with alter database, but get the message:
"File activation failure. The physical file name "...\mydb_log.ldf" may be
incorrect."
Looking at all of the "recovery_pending" databases, I see that all of the
log files are showing as "may be incorrect."
sp_helpdb gives me the message: "No permission to access database <mydb>"
DBCC CheckDB gives me the same message.
Thinking back through what has occured in the last few months (since these
were last used), I've done some minor system maintenance such as defrag and
Windows Updates, but nothing else has been changed. And the files still
reside in the directory where the log file is pointed to.
I'm baffled as to how to get these out of recovery, so any advice is
appreciated.
Thanks for the ideas Tibor. Nothing I could do would get those DBs out of
"recovery pending". But tonight, on the off-chance that it might work, I
went ahead and tried to detach. It gave me an error message that it could
not close the files, but in fact, once I refreshed, it HAD detached the
files! Just to confirm that they were actually detached, I created a new
directory and moved the .mdf and .ldf files in there with no problem. I then
re-attached and the previously in-recovery databases are functioning
normally.
It certainly seems strange that Microsoft did not provide an escape hatch
for this "Recovery Pending", i.e., in limbo, scenario.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:6B5D9B92-E6AC-485B-9053-E9BF2B109A94@.microsoft.com...
> Recovery Pending means that SQL Server need to do recovery for the
> database to bring it to a consistent state. Recovery consists of REDO and
> UNDO, both parts are based on the entries in the transaction log (ldf
> file). SQL Server feel that the ldf files are fishy or something similar,
> and hence it cannot do recovery.
> In most cases when I see something similar, it was somebody who deleted
> the log file or used some other tool to reduce file size. If you are 100%
> certain that something like this didn't happen, then you should carefully
> investigate the SQL Server errorlog file for all related error messages.
> Also investigate the Windows eventlog for clues to what has happened to
> these files. Int he end, if you cannot get these files back to healthy
> state, you are in for RESTORE DATABASE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Earl" <earl@.nospam.com> wrote in message
> news:uiesseQjIHA.5160@.TK2MSFTNGP05.phx.gbl...
>
A vexing issue
Strange problem that I just noticed. I use a handful of databases in SQL2005
Express for development. All of these databases except the one I've been
using most recently "cannot be opened due to inaccessible files or
insufficient memory'.
All of the databases show "recovery_pending" for status.
I tried to set them online with alter database, but get the message:
"File activation failure. The physical file name "...\mydb_log.ldf" may be
incorrect."
Looking at all of the "recovery_pending" databases, I see that all of the
log files are showing as "may be incorrect."
sp_helpdb gives me the message: "No permission to access database <mydb>"
DBCC CheckDB gives me the same message.
Thinking back through what has occured in the last few months (since these
were last used), I've done some minor system maintenance such as defrag and
Windows Updates, but nothing else has been changed. And the files still
reside in the directory where the log file is pointed to.
I'm baffled as to how to get these out of recovery, so any advice is
appreciated.Recovery Pending means that SQL Server need to do recovery for the database to bring it to a
consistent state. Recovery consists of REDO and UNDO, both parts are based on the entries in the
transaction log (ldf file). SQL Server feel that the ldf files are fishy or something similar, and
hence it cannot do recovery.
In most cases when I see something similar, it was somebody who deleted the log file or used some
other tool to reduce file size. If you are 100% certain that something like this didn't happen, then
you should carefully investigate the SQL Server errorlog file for all related error messages. Also
investigate the Windows eventlog for clues to what has happened to these files. Int he end, if you
cannot get these files back to healthy state, you are in for RESTORE DATABASE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Earl" <earl@.nospam.com> wrote in message news:uiesseQjIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Strange problem that I just noticed. I use a handful of databases in SQL2005 Express for
> development. All of these databases except the one I've been using most recently "cannot be opened
> due to inaccessible files or insufficient memory'.
> All of the databases show "recovery_pending" for status.
> I tried to set them online with alter database, but get the message:
> "File activation failure. The physical file name "...\mydb_log.ldf" may be incorrect."
> Looking at all of the "recovery_pending" databases, I see that all of the log files are showing as
> "may be incorrect."
> sp_helpdb gives me the message: "No permission to access database <mydb>"
> DBCC CheckDB gives me the same message.
> Thinking back through what has occured in the last few months (since these were last used), I've
> done some minor system maintenance such as defrag and Windows Updates, but nothing else has been
> changed. And the files still reside in the directory where the log file is pointed to.
> I'm baffled as to how to get these out of recovery, so any advice is appreciated.
>
>|||Thanks for the ideas Tibor. Nothing I could do would get those DBs out of
"recovery pending". But tonight, on the off-chance that it might work, I
went ahead and tried to detach. It gave me an error message that it could
not close the files, but in fact, once I refreshed, it HAD detached the
files! Just to confirm that they were actually detached, I created a new
directory and moved the .mdf and .ldf files in there with no problem. I then
re-attached and the previously in-recovery databases are functioning
normally.
It certainly seems strange that Microsoft did not provide an escape hatch
for this "Recovery Pending", i.e., in limbo, scenario.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:6B5D9B92-E6AC-485B-9053-E9BF2B109A94@.microsoft.com...
> Recovery Pending means that SQL Server need to do recovery for the
> database to bring it to a consistent state. Recovery consists of REDO and
> UNDO, both parts are based on the entries in the transaction log (ldf
> file). SQL Server feel that the ldf files are fishy or something similar,
> and hence it cannot do recovery.
> In most cases when I see something similar, it was somebody who deleted
> the log file or used some other tool to reduce file size. If you are 100%
> certain that something like this didn't happen, then you should carefully
> investigate the SQL Server errorlog file for all related error messages.
> Also investigate the Windows eventlog for clues to what has happened to
> these files. Int he end, if you cannot get these files back to healthy
> state, you are in for RESTORE DATABASE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Earl" <earl@.nospam.com> wrote in message
> news:uiesseQjIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Strange problem that I just noticed. I use a handful of databases in
>> SQL2005 Express for development. All of these databases except the one
>> I've been using most recently "cannot be opened due to inaccessible files
>> or insufficient memory'.
>> All of the databases show "recovery_pending" for status.
>> I tried to set them online with alter database, but get the message:
>> "File activation failure. The physical file name "...\mydb_log.ldf" may
>> be incorrect."
>> Looking at all of the "recovery_pending" databases, I see that all of the
>> log files are showing as "may be incorrect."
>> sp_helpdb gives me the message: "No permission to access database <mydb>"
>> DBCC CheckDB gives me the same message.
>> Thinking back through what has occured in the last few months (since
>> these were last used), I've done some minor system maintenance such as
>> defrag and Windows Updates, but nothing else has been changed. And the
>> files still reside in the directory where the log file is pointed to.
>> I'm baffled as to how to get these out of recovery, so any advice is
>> appreciated.
>>
>|||> It certainly seems strange that Microsoft did not provide an escape hatch for this "Recovery
> Pending", i.e., in limbo, scenario.
What would the escape hatch be?
It is important to differentiate between a "real" Recovery Pending (RP) situation and a "false" one.
It seems you had a false RP situation. By "false" I mean that SQL server did already complete
recovery work, which is why you could detach and attach the databases without SQL Server
complaining. If you had a "real" RP situation, then SQL Server would *not* allow you to attach a
database for which recovery cannot complete. Why you experienced this false RP, I don't know.
Sometimes I see users who don't refresh the GUI, quite simply. There could be other things as well,
of course, I can't say. One option would have been to open a case with MS and have them sort out
whether it was a real or false RP case and if it was a false one sort out the bug in the product
that lead to this false RP situation.
There is a reason why there isn't an escape hatch for a *real* RP situation (almost, see end of this
paragraph) . It would leave the database in an inconsistent state. The data is inconsistent from a
logical viewpoint (foreign key and other constraints cannot be trusted, half-completed transactions
etc). But also from a physical viewpoint (system tables modifications half-done). Basically you
would have a useless database. This last two weeks I have worked with such a database. I've spent
two weeks to get inconsistent data out of 3 tables (yep, only 3 tables over two weeks). And I petty
the soul who will try to re-integrate this crap into the production database (which was restored
from a 1 month old backup). As you can imagine, this isn't something that MS want to expose to
normal users out there. If you do feel adventurous, you can read up on "Emergency mode". This is
your escape hatch. This should not be used unless you are en expert in SQL server, and you prefer
inconsistent data over your most recent backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Earl" <earl@.nospam.com> wrote in message news:OYbl276jIHA.4940@.TK2MSFTNGP02.phx.gbl...
> Thanks for the ideas Tibor. Nothing I could do would get those DBs out of "recovery pending". But
> tonight, on the off-chance that it might work, I went ahead and tried to detach. It gave me an
> error message that it could not close the files, but in fact, once I refreshed, it HAD detached
> the files! Just to confirm that they were actually detached, I created a new directory and moved
> the .mdf and .ldf files in there with no problem. I then re-attached and the previously
> in-recovery databases are functioning normally.
> It certainly seems strange that Microsoft did not provide an escape hatch for this "Recovery
> Pending", i.e., in limbo, scenario.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:6B5D9B92-E6AC-485B-9053-E9BF2B109A94@.microsoft.com...
>> Recovery Pending means that SQL Server need to do recovery for the database to bring it to a
>> consistent state. Recovery consists of REDO and UNDO, both parts are based on the entries in the
>> transaction log (ldf file). SQL Server feel that the ldf files are fishy or something similar,
>> and hence it cannot do recovery.
>> In most cases when I see something similar, it was somebody who deleted the log file or used some
>> other tool to reduce file size. If you are 100% certain that something like this didn't happen,
>> then you should carefully investigate the SQL Server errorlog file for all related error
>> messages. Also investigate the Windows eventlog for clues to what has happened to these files.
>> Int he end, if you cannot get these files back to healthy state, you are in for RESTORE DATABASE.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Earl" <earl@.nospam.com> wrote in message news:uiesseQjIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Strange problem that I just noticed. I use a handful of databases in SQL2005 Express for
>> development. All of these databases except the one I've been using most recently "cannot be
>> opened due to inaccessible files or insufficient memory'.
>> All of the databases show "recovery_pending" for status.
>> I tried to set them online with alter database, but get the message:
>> "File activation failure. The physical file name "...\mydb_log.ldf" may be incorrect."
>> Looking at all of the "recovery_pending" databases, I see that all of the log files are showing
>> as "may be incorrect."
>> sp_helpdb gives me the message: "No permission to access database <mydb>"
>> DBCC CheckDB gives me the same message.
>> Thinking back through what has occured in the last few months (since these were last used), I've
>> done some minor system maintenance such as defrag and Windows Updates, but nothing else has been
>> changed. And the files still reside in the directory where the log file is pointed to.
>> I'm baffled as to how to get these out of recovery, so any advice is appreciated.
>>
>>
>sql
Express for development. All of these databases except the one I've been
using most recently "cannot be opened due to inaccessible files or
insufficient memory'.
All of the databases show "recovery_pending" for status.
I tried to set them online with alter database, but get the message:
"File activation failure. The physical file name "...\mydb_log.ldf" may be
incorrect."
Looking at all of the "recovery_pending" databases, I see that all of the
log files are showing as "may be incorrect."
sp_helpdb gives me the message: "No permission to access database <mydb>"
DBCC CheckDB gives me the same message.
Thinking back through what has occured in the last few months (since these
were last used), I've done some minor system maintenance such as defrag and
Windows Updates, but nothing else has been changed. And the files still
reside in the directory where the log file is pointed to.
I'm baffled as to how to get these out of recovery, so any advice is
appreciated.Recovery Pending means that SQL Server need to do recovery for the database to bring it to a
consistent state. Recovery consists of REDO and UNDO, both parts are based on the entries in the
transaction log (ldf file). SQL Server feel that the ldf files are fishy or something similar, and
hence it cannot do recovery.
In most cases when I see something similar, it was somebody who deleted the log file or used some
other tool to reduce file size. If you are 100% certain that something like this didn't happen, then
you should carefully investigate the SQL Server errorlog file for all related error messages. Also
investigate the Windows eventlog for clues to what has happened to these files. Int he end, if you
cannot get these files back to healthy state, you are in for RESTORE DATABASE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Earl" <earl@.nospam.com> wrote in message news:uiesseQjIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Strange problem that I just noticed. I use a handful of databases in SQL2005 Express for
> development. All of these databases except the one I've been using most recently "cannot be opened
> due to inaccessible files or insufficient memory'.
> All of the databases show "recovery_pending" for status.
> I tried to set them online with alter database, but get the message:
> "File activation failure. The physical file name "...\mydb_log.ldf" may be incorrect."
> Looking at all of the "recovery_pending" databases, I see that all of the log files are showing as
> "may be incorrect."
> sp_helpdb gives me the message: "No permission to access database <mydb>"
> DBCC CheckDB gives me the same message.
> Thinking back through what has occured in the last few months (since these were last used), I've
> done some minor system maintenance such as defrag and Windows Updates, but nothing else has been
> changed. And the files still reside in the directory where the log file is pointed to.
> I'm baffled as to how to get these out of recovery, so any advice is appreciated.
>
>|||Thanks for the ideas Tibor. Nothing I could do would get those DBs out of
"recovery pending". But tonight, on the off-chance that it might work, I
went ahead and tried to detach. It gave me an error message that it could
not close the files, but in fact, once I refreshed, it HAD detached the
files! Just to confirm that they were actually detached, I created a new
directory and moved the .mdf and .ldf files in there with no problem. I then
re-attached and the previously in-recovery databases are functioning
normally.
It certainly seems strange that Microsoft did not provide an escape hatch
for this "Recovery Pending", i.e., in limbo, scenario.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:6B5D9B92-E6AC-485B-9053-E9BF2B109A94@.microsoft.com...
> Recovery Pending means that SQL Server need to do recovery for the
> database to bring it to a consistent state. Recovery consists of REDO and
> UNDO, both parts are based on the entries in the transaction log (ldf
> file). SQL Server feel that the ldf files are fishy or something similar,
> and hence it cannot do recovery.
> In most cases when I see something similar, it was somebody who deleted
> the log file or used some other tool to reduce file size. If you are 100%
> certain that something like this didn't happen, then you should carefully
> investigate the SQL Server errorlog file for all related error messages.
> Also investigate the Windows eventlog for clues to what has happened to
> these files. Int he end, if you cannot get these files back to healthy
> state, you are in for RESTORE DATABASE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Earl" <earl@.nospam.com> wrote in message
> news:uiesseQjIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Strange problem that I just noticed. I use a handful of databases in
>> SQL2005 Express for development. All of these databases except the one
>> I've been using most recently "cannot be opened due to inaccessible files
>> or insufficient memory'.
>> All of the databases show "recovery_pending" for status.
>> I tried to set them online with alter database, but get the message:
>> "File activation failure. The physical file name "...\mydb_log.ldf" may
>> be incorrect."
>> Looking at all of the "recovery_pending" databases, I see that all of the
>> log files are showing as "may be incorrect."
>> sp_helpdb gives me the message: "No permission to access database <mydb>"
>> DBCC CheckDB gives me the same message.
>> Thinking back through what has occured in the last few months (since
>> these were last used), I've done some minor system maintenance such as
>> defrag and Windows Updates, but nothing else has been changed. And the
>> files still reside in the directory where the log file is pointed to.
>> I'm baffled as to how to get these out of recovery, so any advice is
>> appreciated.
>>
>|||> It certainly seems strange that Microsoft did not provide an escape hatch for this "Recovery
> Pending", i.e., in limbo, scenario.
What would the escape hatch be?
It is important to differentiate between a "real" Recovery Pending (RP) situation and a "false" one.
It seems you had a false RP situation. By "false" I mean that SQL server did already complete
recovery work, which is why you could detach and attach the databases without SQL Server
complaining. If you had a "real" RP situation, then SQL Server would *not* allow you to attach a
database for which recovery cannot complete. Why you experienced this false RP, I don't know.
Sometimes I see users who don't refresh the GUI, quite simply. There could be other things as well,
of course, I can't say. One option would have been to open a case with MS and have them sort out
whether it was a real or false RP case and if it was a false one sort out the bug in the product
that lead to this false RP situation.
There is a reason why there isn't an escape hatch for a *real* RP situation (almost, see end of this
paragraph) . It would leave the database in an inconsistent state. The data is inconsistent from a
logical viewpoint (foreign key and other constraints cannot be trusted, half-completed transactions
etc). But also from a physical viewpoint (system tables modifications half-done). Basically you
would have a useless database. This last two weeks I have worked with such a database. I've spent
two weeks to get inconsistent data out of 3 tables (yep, only 3 tables over two weeks). And I petty
the soul who will try to re-integrate this crap into the production database (which was restored
from a 1 month old backup). As you can imagine, this isn't something that MS want to expose to
normal users out there. If you do feel adventurous, you can read up on "Emergency mode". This is
your escape hatch. This should not be used unless you are en expert in SQL server, and you prefer
inconsistent data over your most recent backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Earl" <earl@.nospam.com> wrote in message news:OYbl276jIHA.4940@.TK2MSFTNGP02.phx.gbl...
> Thanks for the ideas Tibor. Nothing I could do would get those DBs out of "recovery pending". But
> tonight, on the off-chance that it might work, I went ahead and tried to detach. It gave me an
> error message that it could not close the files, but in fact, once I refreshed, it HAD detached
> the files! Just to confirm that they were actually detached, I created a new directory and moved
> the .mdf and .ldf files in there with no problem. I then re-attached and the previously
> in-recovery databases are functioning normally.
> It certainly seems strange that Microsoft did not provide an escape hatch for this "Recovery
> Pending", i.e., in limbo, scenario.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:6B5D9B92-E6AC-485B-9053-E9BF2B109A94@.microsoft.com...
>> Recovery Pending means that SQL Server need to do recovery for the database to bring it to a
>> consistent state. Recovery consists of REDO and UNDO, both parts are based on the entries in the
>> transaction log (ldf file). SQL Server feel that the ldf files are fishy or something similar,
>> and hence it cannot do recovery.
>> In most cases when I see something similar, it was somebody who deleted the log file or used some
>> other tool to reduce file size. If you are 100% certain that something like this didn't happen,
>> then you should carefully investigate the SQL Server errorlog file for all related error
>> messages. Also investigate the Windows eventlog for clues to what has happened to these files.
>> Int he end, if you cannot get these files back to healthy state, you are in for RESTORE DATABASE.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Earl" <earl@.nospam.com> wrote in message news:uiesseQjIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Strange problem that I just noticed. I use a handful of databases in SQL2005 Express for
>> development. All of these databases except the one I've been using most recently "cannot be
>> opened due to inaccessible files or insufficient memory'.
>> All of the databases show "recovery_pending" for status.
>> I tried to set them online with alter database, but get the message:
>> "File activation failure. The physical file name "...\mydb_log.ldf" may be incorrect."
>> Looking at all of the "recovery_pending" databases, I see that all of the log files are showing
>> as "may be incorrect."
>> sp_helpdb gives me the message: "No permission to access database <mydb>"
>> DBCC CheckDB gives me the same message.
>> Thinking back through what has occured in the last few months (since these were last used), I've
>> done some minor system maintenance such as defrag and Windows Updates, but nothing else has been
>> changed. And the files still reside in the directory where the log file is pointed to.
>> I'm baffled as to how to get these out of recovery, so any advice is appreciated.
>>
>>
>sql
Monday, March 19, 2012
A suggestion that can help SQL Server community
I have noticed that the area of writing stored procedures for muti-user databases is a very specialised field and requires knowledge that's much more than the locking topics covered in 'online books' . I am sure there are some standard tips and tricks that are used in mutil-user databases for writing to tables.Most books have a chapter or two on locking, but I think this topic should be dealt withseparately in a dedicated book to locking with extensive examples on locking. Does anyone know of such a dedicated book out there?
The person who I think goes under SQL Server transaction is Dusan Petkovic, his books are by no means Beginner's books but Osborne gave them that title but your understanding of SQL Server transaction will improve after you read his chapter and do the questions at the end of the chapter.
He also covered ANSI SQL transaction features SQL Server implements but is not documented. Try the link below for his books I have not read the SQL Server 2005 version.
http://www.amazon.com/gp/product/007212587X/102-0765109-8072934?v=glance&n=283155
http://books.mcgraw-hill.com/getbook.php?isbn=0072260939&template
Tuesday, March 6, 2012
A script to get properties of full text catalog?
Hey, all--
My organization is having a problem with MS-SQL's sporadically stopping full-text catalog population on one of our databases. New content is added to that database constantly, so we have an incremental population schedule set up to update the index once an hour. This works fine for a while and then stops for no apparent reason; we don't notice it having stopped until someone comes along to tell us they can't find something.
We've been trying different things to fix it, but in the meanwhile, is there a way to expose the properties (particularly the Last Population Date) of a full-text catalog to an outside script, so that we could incorporate a check of the catalog status into our daily server health scripts? Or, alternatively, a way to have the database server send out an email when the last incremental update is more than an hour old? It's no good to have our users being the ones to tell us when the indexing has failed.
Thanks much.Hello,
Try
use DatabaseName
select FulltextCatalogProperty(N'CatalogName', N'PopulateCompletionAge')
Its in seconds since 12:00:00 A.M., January 1, 1990.
I'll get you more info after i walk downstairs and get some starbucks :)|||Oh, that's perfect. Thanks so much.
For the reference of future readers of this topic, that first param is actually the catalog name, not the database name. Full Microsoft documentation is here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_7x15.asp . It's amazing what you can find when you know what you're looking for... thanks again!|||Select dateadd(s,FulltextCatalogProperty(N'CatalogName', N'PopulateCompletionAge'),'1/1/1990')
Alot of times, I'll do a incremental right after the insert. That will only be helpful in certain situations.
Thanks for the note on the Catalog name. It looked fishy but I didnt look into it. I guess my catalog name is the same as my database name ;)
My organization is having a problem with MS-SQL's sporadically stopping full-text catalog population on one of our databases. New content is added to that database constantly, so we have an incremental population schedule set up to update the index once an hour. This works fine for a while and then stops for no apparent reason; we don't notice it having stopped until someone comes along to tell us they can't find something.
We've been trying different things to fix it, but in the meanwhile, is there a way to expose the properties (particularly the Last Population Date) of a full-text catalog to an outside script, so that we could incorporate a check of the catalog status into our daily server health scripts? Or, alternatively, a way to have the database server send out an email when the last incremental update is more than an hour old? It's no good to have our users being the ones to tell us when the indexing has failed.
Thanks much.Hello,
Try
use DatabaseName
select FulltextCatalogProperty(N'CatalogName', N'PopulateCompletionAge')
Its in seconds since 12:00:00 A.M., January 1, 1990.
I'll get you more info after i walk downstairs and get some starbucks :)|||Oh, that's perfect. Thanks so much.
For the reference of future readers of this topic, that first param is actually the catalog name, not the database name. Full Microsoft documentation is here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_7x15.asp . It's amazing what you can find when you know what you're looking for... thanks again!|||Select dateadd(s,FulltextCatalogProperty(N'CatalogName', N'PopulateCompletionAge'),'1/1/1990')
Alot of times, I'll do a incremental right after the insert. That will only be helpful in certain situations.
Thanks for the note on the Catalog name. It looked fishy but I didnt look into it. I guess my catalog name is the same as my database name ;)
A script to capture Orphan userids
Hello
We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
Is it possible to use a script to capture(or select) orphan ids?
Thanks in advance,
Do.
Message posted via http://www.sqlmonster.com
Hi do Park,
You can use the stored procedure below to get a report
exec sp_change_users_login 'Report'
Details in the following article:
http://support.microsoft.com/default...b;en-us;314546
Yih-Yoon Lee
Do Park via SQLMonster.com wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
>
|||There is a script to do this at www.sqlservercentral.com in their script
library...
Brad Feaker
Ex nihilo, nihil fit
"Do Park via SQLMonster.com" wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>
|||As Yih-Yoon Lee has posted, the solution is to use sp_change_users_login
I have found this article helpful. It contains a couple of stored
procedures that allow you to script logins and their SID. You can use these
to create logins on "downstream" (non-production) servers. As long as users
are created somewhere and their SIDs are carried forward you should not end
up with orphaned users.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
Keith
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9ba509aa5f3a4496ba55516802843699@.SQLMonster.c om...
> Hello
> We've had problems about orphan userids in Development SQL server.
Application DBAs restore databases from Production SQL server a lot of time.
So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com
We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
Is it possible to use a script to capture(or select) orphan ids?
Thanks in advance,
Do.
Message posted via http://www.sqlmonster.com
Hi do Park,
You can use the stored procedure below to get a report
exec sp_change_users_login 'Report'
Details in the following article:
http://support.microsoft.com/default...b;en-us;314546
Yih-Yoon Lee
Do Park via SQLMonster.com wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
>
|||There is a script to do this at www.sqlservercentral.com in their script
library...
Brad Feaker
Ex nihilo, nihil fit
"Do Park via SQLMonster.com" wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>
|||As Yih-Yoon Lee has posted, the solution is to use sp_change_users_login
I have found this article helpful. It contains a couple of stored
procedures that allow you to script logins and their SID. You can use these
to create logins on "downstream" (non-production) servers. As long as users
are created somewhere and their SIDs are carried forward you should not end
up with orphaned users.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
Keith
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9ba509aa5f3a4496ba55516802843699@.SQLMonster.c om...
> Hello
> We've had problems about orphan userids in Development SQL server.
Application DBAs restore databases from Production SQL server a lot of time.
So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com
A script to capture Orphan userids
Hello
We've had problems about orphan userids in Development SQL server. Applicati
on DBAs restore databases from Production SQL server a lot of time. So I'd l
ike to set up an automation using a script to catch orphan ids.
Is it possible to use a script to capture(or select) orphan ids?
Thanks in advance,
Do.
Message posted via http://www.droptable.comHi do Park,
You can use the stored procedure below to get a report
exec sp_change_users_login 'Report'
Details in the following article:
http://support.microsoft.com/defaul...kb;en-us;314546
Yih-Yoon Lee
Do Park via droptable.com wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Applica
tion DBAs restore databases from Production SQL server a lot of time. So I'd
like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
>|||There is a script to do this at www.sqlservercentral.com in their script
library...
Brad Feaker
Ex nihilo, nihil fit
"Do Park via droptable.com" wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Applica
tion DBAs restore databases from Production SQL server a lot of time. So I'd
like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.droptable.com
>|||As Yih-Yoon Lee has posted, the solution is to use sp_change_users_login
I have found this article helpful. It contains a couple of stored
procedures that allow you to script logins and their SID. You can use these
to create logins on "downstream" (non-production) servers. As long as users
are created somewhere and their SIDs are carried forward you should not end
up with orphaned users.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
Keith
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:9ba509aa5f3a4496ba55516802843699@.SQ
droptable.com...
> Hello
> We've had problems about orphan userids in Development SQL server.
Application DBAs restore databases from Production SQL server a lot of time.
So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.droptable.com
We've had problems about orphan userids in Development SQL server. Applicati
on DBAs restore databases from Production SQL server a lot of time. So I'd l
ike to set up an automation using a script to catch orphan ids.
Is it possible to use a script to capture(or select) orphan ids?
Thanks in advance,
Do.
Message posted via http://www.droptable.comHi do Park,
You can use the stored procedure below to get a report
exec sp_change_users_login 'Report'
Details in the following article:
http://support.microsoft.com/defaul...kb;en-us;314546
Yih-Yoon Lee
Do Park via droptable.com wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Applica
tion DBAs restore databases from Production SQL server a lot of time. So I'd
like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
>|||There is a script to do this at www.sqlservercentral.com in their script
library...
Brad Feaker
Ex nihilo, nihil fit
"Do Park via droptable.com" wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Applica
tion DBAs restore databases from Production SQL server a lot of time. So I'd
like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.droptable.com
>|||As Yih-Yoon Lee has posted, the solution is to use sp_change_users_login
I have found this article helpful. It contains a couple of stored
procedures that allow you to script logins and their SID. You can use these
to create logins on "downstream" (non-production) servers. As long as users
are created somewhere and their SIDs are carried forward you should not end
up with orphaned users.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
Keith
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:9ba509aa5f3a4496ba55516802843699@.SQ
droptable.com...
> Hello
> We've had problems about orphan userids in Development SQL server.
Application DBAs restore databases from Production SQL server a lot of time.
So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.droptable.com
A script to capture Orphan userids
Hello
We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
Is it possible to use a script to capture(or select) orphan ids?
Thanks in advance,
Do.
--
Message posted via http://www.sqlmonster.comHi do Park,
You can use the stored procedure below to get a report
exec sp_change_users_login 'Report'
Details in the following article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
Yih-Yoon Lee
Do Park via SQLMonster.com wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
>|||There is a script to do this at www.sqlservercentral.com in their script
library...
Brad Feaker
Ex nihilo, nihil fit
"Do Park via SQLMonster.com" wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>|||As Yih-Yoon Lee has posted, the solution is to use sp_change_users_login
I have found this article helpful. It contains a couple of stored
procedures that allow you to script logins and their SID. You can use these
to create logins on "downstream" (non-production) servers. As long as users
are created somewhere and their SIDs are carried forward you should not end
up with orphaned users.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
--
Keith
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9ba509aa5f3a4496ba55516802843699@.SQLMonster.com...
> Hello
> We've had problems about orphan userids in Development SQL server.
Application DBAs restore databases from Production SQL server a lot of time.
So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com
We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
Is it possible to use a script to capture(or select) orphan ids?
Thanks in advance,
Do.
--
Message posted via http://www.sqlmonster.comHi do Park,
You can use the stored procedure below to get a report
exec sp_change_users_login 'Report'
Details in the following article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
Yih-Yoon Lee
Do Park via SQLMonster.com wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
>|||There is a script to do this at www.sqlservercentral.com in their script
library...
Brad Feaker
Ex nihilo, nihil fit
"Do Park via SQLMonster.com" wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>|||As Yih-Yoon Lee has posted, the solution is to use sp_change_users_login
I have found this article helpful. It contains a couple of stored
procedures that allow you to script logins and their SID. You can use these
to create logins on "downstream" (non-production) servers. As long as users
are created somewhere and their SIDs are carried forward you should not end
up with orphaned users.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
--
Keith
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9ba509aa5f3a4496ba55516802843699@.SQLMonster.com...
> Hello
> We've had problems about orphan userids in Development SQL server.
Application DBAs restore databases from Production SQL server a lot of time.
So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com
Friday, February 24, 2012
A query to locate a specific column in various tables?
I have over 65 databases, all with various numbers of tables. Within these
tables I need to see which tables contain a specific field called vendor_id
Is there a query that can be ran for this?
Thanks
Jeff
Message posted via http://www.droptable.com
SELECT Table_name from INFORMATION_SCHEMA.COLUMNS Where Column_Name LIKE
'YADAYADA'
HTH, Jens Suessmeyer
tables I need to see which tables contain a specific field called vendor_id
Is there a query that can be ran for this?
Thanks
Jeff
Message posted via http://www.droptable.com
SELECT Table_name from INFORMATION_SCHEMA.COLUMNS Where Column_Name LIKE
'YADAYADA'
HTH, Jens Suessmeyer
A query to locate a specific column in various tables?
I have over 65 databases, all with various numbers of tables. Within these
tables I need to see which tables contain a specific field called vendor_id
Is there a query that can be ran for this?
Thanks
Jeff
--
Message posted via http://www.sqlmonster.comSELECT Table_name from INFORMATION_SCHEMA.COLUMNS Where Column_Name LIKE
'YADAYADA'
HTH, Jens Suessmeyer
tables I need to see which tables contain a specific field called vendor_id
Is there a query that can be ran for this?
Thanks
Jeff
--
Message posted via http://www.sqlmonster.comSELECT Table_name from INFORMATION_SCHEMA.COLUMNS Where Column_Name LIKE
'YADAYADA'
HTH, Jens Suessmeyer
A query to locate a specific column in various tables?
I have over 65 databases, all with various numbers of tables. Within these
tables I need to see which tables contain a specific field called vendor_id
Is there a query that can be ran for this?
Thanks
Jeff
Message posted via http://www.droptable.comSELECT Table_name from INFORMATION_SCHEMA.COLUMNS Where Column_Name LIKE
'YADAYADA'
HTH, Jens Suessmeyer
tables I need to see which tables contain a specific field called vendor_id
Is there a query that can be ran for this?
Thanks
Jeff
Message posted via http://www.droptable.comSELECT Table_name from INFORMATION_SCHEMA.COLUMNS Where Column_Name LIKE
'YADAYADA'
HTH, Jens Suessmeyer
Thursday, February 16, 2012
A newbie question
Hi all,
I am an experienced developer but a TOTAL newbie when it comes to databases. I am creating a new Visual Basic app for which I need a database and I'm using SQL Server Express with VS 2005.
The problem I'm having is that even after perusing this forum and a lot of MSDN articles, I don't understand something basic... how do I create a database and modify the tables and columns of it (the schema, I mean) while I'm developing this application, without losing all the data every time I make a change?
To clarify, I don't know the entire schema of the database yet; I'm still working it out. I want to develop some forms that display data from some of the tables that I do have, but it seems that no matter what I do, my app wants to connect to the .mdf file in the \bin\debug folder of my project, instead of the one in the project folder itself. So it seems like I have two choices:
1) I leave "Copy to Output Directory" set to "Copy always" which means the .mdf will have the updated schema, but I lose all the data I put in the database every time I run the app, which is no good.
2) I change "Copy to Output Directory" to "Do not copy" which means my data will be preserved but when I change the table schema the database in the \bin\debug folder will still have the old schema.
So what is the correct approach here? Am I supposed to use option #1 above, and then, before exiting the application I write all my data into a separate file that I can read back into the database the next time I run the app (since the old data gets nuked by the "Copy Always" option)? Or is there some way to work directly on the schema of the database with the saved data in it?
The other thing I don't understand is why does VB always look for the db in the \bin\debug folder? How are you supposed to connect to an existing database that lives somewhere else on your drives? When I first created the connection (under "Data Connections" in the Server Explorer) I got a popup window that asked if I wanted to copy the database over, so I said no. So why wouldn't it connect to the original db at the path I specified? Why is still looking for \bin\debug\mydb.mdf?
Sorry for such basic questions, but I'm really confused.
Thanks!
Phil
hi Phil,
first and always, [IMVHO]
pwinant wrote:
The problem I'm having is that even after perusing this forum and a lot of MSDN articles, I don't understand something basic... how do I create a database and modify the tables and columns of it (the schema, I mean) while I'm developing this application, without losing all the data every time I make a change?
To clarify, I don't know the entire schema of the database yet;
bad sentence, this one... this mean no design has been actually implemented... just a brute force "write through" approach...
ok.. that can be an experience as well.. and you see some of the effects when using User Instances feature ... personally I do not use that paradigm so this trouble is not included in my day to day problems list
I'm still working it out. I want to develop some forms that display data from some of the tables that I do have, but it seems that no matter what I do, my app wants to connect to the .mdf file in the \bin\debug folder of my project, instead of the one in the project folder itself. So it seems like I have two choices: 1) I leave "Copy to Output Directory" set to "Copy always" which means the .mdf will have the updated schema, but I lose all the data I put in the database every time I run the app, which is no good.
2) I change "Copy to Output Directory" to "Do not copy" which means my data will be preserved but when I change the table schema the database in the \bin\debug folder will still have the old schema.
So what is the correct approach here? Am I supposed to use option #1 above, and then, before exiting the application I write all my data into a separate file that I can read back into the database the next time I run the app (since the old data gets nuked by the "Copy Always" option)? Or is there some way to work directly on the schema of the database with the saved data in it?
what if you copy back your actual mdf + ldf files in the project folder when you are finished executing/debugging the app, so that you can continue modifying it at design time and maintain the inserted data?
The other thing I don't understand is why does VB always look for the db in the \bin\debug folder? How are you supposed to connect to an existing database that lives somewhere else on your drives? When I first created the connection (under "Data Connections" in the Server Explorer) I got a popup window that asked if I wanted to copy the database over, so I said no. So why wouldn't it connect to the original db at the path I specified? Why is still looking for \bin\debug\mydb.mdf?
my answer is, again, not to use User Instance feature and connect to a "traditional" instance of SQL Server/SQLExpress providing traditional connection parameters... you connect that way to the instance present in your (usually) \Program Files\Microsoft SQL Server\MSSQL.x folder, and it's database are usually in the \MSSQL\Data subfolder...
so you provide InstaceName, InitialCatalog, and authentication info (trusted or standard SQL Server login's credentials) parameters like
connectionString = "Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"
or
connectionString = "Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;"
but, if you use an Express edition of Visual Studio, you loose the possibility to use the integrated designers as they only work with User Instances implementations..
you can code (manually code) your app to connect to "standard" SQL Server\SQLExpress editions, but not via the designers...
[/IMVHO]
regards
|||Hi Phil,
I am not the most experienced guy in this forum but I believe I can make a suggestion. I believe you are updating your DB schema in VS IDE. I suggest you use of SQL Management Studio Express (It's a free download from Microsoft). In object explorer window of SQL MSE you can right click on databases node and select attach option. This will allow you to select an mdf file from somewhere on your computer and attach it to your DB. I hope it helps.
FlatWhite
|||Hi Phil,
Check out my last post in this thread. In the post I discuss user instances and how they work and point to a white paper that explains them in more detail. The behavior you're seeing is the result of how VS works with files and is by design. When you include your database as part of your project, it gets moved around just like any project file; when you are debuging (F5) you are actually running your application from the .\bin\debug directly, not from your project directory, which is why you see this behavior. This behavior is required for ClickOnce to work, but is targeted as single user applications. If you're creating a multi-user application you shouldn't be using user instances, you should put your database on a central server and connect to it through tat.
There is no one right way to deal with the whole "lost data" issue caused by the VS behavior related to embedded databases. I've described a couple ways, here and here and MF mentions a variation in the post I've already linked to above. There are others as well, it's all about how you want to deploy your database.
Mike
|||Hi all,
I've received three emails now saying that different people have replied to my post, but when I click on the link in the email it says there's no such post, and I don't see anything here in the forum except my original post. Not sure why that's happening (I hope this post will show up!). Anyway, thanks to all those who are trying to answer my question. :-) Hopefully your posts will actually show up for me at some point so I can read your entire message!
Phil
Edit: ok, now that I've replied I can see the other posts! Yay!
hi Phil,
actually I do not see your last post as well ... does this one shows up?
let's see..
Subscribe to:
Posts (Atom)