Creating DB from scripts: files

Showing posts with label files. Show all posts

Sunday, March 25, 2012

Creating a T-SQL Stored Procedure to Truncate and Shrink ALL Log Files

Hello -
I'm hoping some T-SQL expert could help me figure out the code
necessary to implement a stored procedure that will truncate and
shrink the log files when run.
Let me give you a little background first. We are a small company
with an adequate, but not overwhelming amount of disk space. But,
with running SQL Server 2000 we do like the Recovery model set to
Full. Each week on back to back evenings (and on separate tapes) we
do a full backup with incremental backups throughout the week. After
the 1st full backup, I would like to run a procedure that would look
at each database and then truncate & shrink the log file. Then for
the 2nd full backup, I'd have much smaller logs obviously with the
ability to recover back to my original(s) via my 1st backup.
The code that works on individual databases that I now run manually
and change the parameters (@.database) accordingly is:
use master
BEGIN
BACKUP LOG @.database WITH TRUNCATE_ONLY
GO
USE @.database
DBCC SHRINKFILE (@.database + '_log')
I would like to have this placed in a stored procedure that would go
through each user database automatically. Unfortunately, I am not at
all familiar with the nuances of automatically changing databases nor
with working with stored procedures.
If anyone would/could be so kind as to provide me with the script that
would allow this to work as desired, I would be very very
appreciative. Thanks in advance for any and all help!
RichScull,
I am a little confused as to what your actually doing. You say you do a
FULL backup and then incremental but do not specify log backups at all other
than the truncate only. If you don't actually do log backups why do you
want to be in FULL recovery mode? What good does shrinking the logs do
anyway? When you backup a log (or a database) the size of the backup file
is proportional to the amount of data in the file not the size of the file
itself. So shrinking a log file will not give smaller backups. Shrinking
it and of itself is a dangerous act anyway for the reasons you mention. If
you have limited space and you shrink the file, which will certainly grow
back again, what will happen when you need more space and don't have it? It
is better to allocate the space in need (now and in the future) up front and
don't ever shrink the files. That way you ensure you always have the room
when you need it. If I misinterpreted what your doing please let me know so
we can work thru this.
Andrew J. Kelly
SQL Server MVP
"Scull" <myscullyfamily@.yahoo.com> wrote in message
news:a8a65e57.0402120523.459e3dea@.posting.google.com...
> Hello -
> I'm hoping some T-SQL expert could help me figure out the code
> necessary to implement a stored procedure that will truncate and
> shrink the log files when run.
> Let me give you a little background first. We are a small company
> with an adequate, but not overwhelming amount of disk space. But,
> with running SQL Server 2000 we do like the Recovery model set to
> Full. Each week on back to back evenings (and on separate tapes) we
> do a full backup with incremental backups throughout the week. After
> the 1st full backup, I would like to run a procedure that would look
> at each database and then truncate & shrink the log file. Then for
> the 2nd full backup, I'd have much smaller logs obviously with the
> ability to recover back to my original(s) via my 1st backup.
> The code that works on individual databases that I now run manually
> and change the parameters (@.database) accordingly is:
>
> use master
> BEGIN
> BACKUP LOG @.database WITH TRUNCATE_ONLY
> GO
> USE @.database
> DBCC SHRINKFILE (@.database + '_log')
> I would like to have this placed in a stored procedure that would go
> through each user database automatically. Unfortunately, I am not at
> all familiar with the nuances of automatically changing databases nor
> with working with stored procedures.
> If anyone would/could be so kind as to provide me with the script that
> would allow this to work as desired, I would be very very
> appreciative. Thanks in advance for any and all help!
> Rich|||Scull
Perhaps you want to use WITH INIT (after full backup database) to ovewrite
the log file.
Also,look at INFORMATION_SCHEMA.SCHEMATA in BOL.
"Scull" <myscullyfamily@.yahoo.com> wrote in message
news:a8a65e57.0402120523.459e3dea@.posting.google.com...
> Hello -
> I'm hoping some T-SQL expert could help me figure out the code
> necessary to implement a stored procedure that will truncate and
> shrink the log files when run.
> Let me give you a little background first. We are a small company
> with an adequate, but not overwhelming amount of disk space. But,
> with running SQL Server 2000 we do like the Recovery model set to
> Full. Each week on back to back evenings (and on separate tapes) we
> do a full backup with incremental backups throughout the week. After
> the 1st full backup, I would like to run a procedure that would look
> at each database and then truncate & shrink the log file. Then for
> the 2nd full backup, I'd have much smaller logs obviously with the
> ability to recover back to my original(s) via my 1st backup.
> The code that works on individual databases that I now run manually
> and change the parameters (@.database) accordingly is:
>
> use master
> BEGIN
> BACKUP LOG @.database WITH TRUNCATE_ONLY
> GO
> USE @.database
> DBCC SHRINKFILE (@.database + '_log')
> I would like to have this placed in a stored procedure that would go
> through each user database automatically. Unfortunately, I am not at
> all familiar with the nuances of automatically changing databases nor
> with working with stored procedures.
> If anyone would/could be so kind as to provide me with the script that
> would allow this to work as desired, I would be very very
> appreciative. Thanks in advance for any and all help!
> Rich|||First off, I want to thank you for your input- it's geratly appreciated!
I'm sorry I wasn't clear in terms of how we perform our backups. We use
a separate backup application for backing up all the SQL Server
databases and their respective transaction logs. We do FULL backups on
Friday and Saturday evening, and incremental backups throughout the
week.
My space issues arose when I converted all of my 7.0 databases to 2000
about 4 months ago. Rather quickly after converting the db's, I noticed
we were running out of disk space. In reviewing one of the converted
databases that was affecting my disk space - I noticed the MDF file with
a size of about 1 GIG and the transaction log with a size of 17 GIG.
Obviously, I was like "Yikes."
Anyway, at this point in time I'd would rather keep the recovery mode
set to full so that we could perform point-in-time recoveries. I figure
the best way, given my disk space concerns, for me to insure possible
point-in-time recovery is to take advantage of the fact that 2 full
backups are done each week. If after the 1st full backup, I can
truncate and shrink the transaction log before the 2nd full backup - I
will have a choice of restore points in the event something should go
awry.
Now while I know this is not necessarily the optimal solution, I do not
know of a better one at this point. I am obviously wide open to
suggestions/recommendations.
If no strategic process changes can be realized, I think I need to find
the answer to my T-SQL question concerning establishing a stored
procedure that I can schedule after the Friday backup but before the
Saturday backup - that will truncate and shrink the transaction log(s)
for each of the user databases automatically.
I will also check out the WITH INIT, etc. in the BOL.
Thanks in advance for any more feedback.
Rich
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Rich,
Your solution is to issue regular Log backups and forget about truncating
and shrinking the logs. If you want point in time recovery you have to be
in FULL mode. If your in FULL mode the log file will keep growing and
growing since it needs a log backup to properly backup the committed trans
and truncate them. If you don't issue log backups what will happen if your
disk array dies and you must restore from scratch? You will only have up to
your last full and incremental backup. You have lost your ability to
restore up to the minute or point in time. If you issue regular log backups
your tran log itself will stay at a size that it needs over a point it time
and should never be shrunk once it reaches that quiescent state. Of coarse
you log backup files will now contain the data that used to be in your log
so you need a place to store those and hopefully it's not on the same array
as the db. That should solve your disk space issue and alleviate the need
to schedule a log truncation and shrinking. Just schedule regular log
backups. The interval is up to you. Every 15 minutes or 1/2 hour should
keep your log file from growing too much and keep you relatively safe as
well. If your concerned about room to place your backup files you might
consider using a product called SQL LiteSpeed. It will reduce the time for
a backup and give you much smaller files to boot.
Andrew J. Kelly
SQL Server MVP
"Rich S" <myscullyfamily@.yahoo.com> wrote in message
news:O1Kiq8d8DHA.3112@.tk2msftngp13.phx.gbl...
> First off, I want to thank you for your input- it's geratly appreciated!
> I'm sorry I wasn't clear in terms of how we perform our backups. We use
> a separate backup application for backing up all the SQL Server
> databases and their respective transaction logs. We do FULL backups on
> Friday and Saturday evening, and incremental backups throughout the
> week.
> My space issues arose when I converted all of my 7.0 databases to 2000
> about 4 months ago. Rather quickly after converting the db's, I noticed
> we were running out of disk space. In reviewing one of the converted
> databases that was affecting my disk space - I noticed the MDF file with
> a size of about 1 GIG and the transaction log with a size of 17 GIG.
> Obviously, I was like "Yikes."
> Anyway, at this point in time I'd would rather keep the recovery mode
> set to full so that we could perform point-in-time recoveries. I figure
> the best way, given my disk space concerns, for me to insure possible
> point-in-time recovery is to take advantage of the fact that 2 full
> backups are done each week. If after the 1st full backup, I can
> truncate and shrink the transaction log before the 2nd full backup - I
> will have a choice of restore points in the event something should go
> awry.
> Now while I know this is not necessarily the optimal solution, I do not
> know of a better one at this point. I am obviously wide open to
> suggestions/recommendations.
> If no strategic process changes can be realized, I think I need to find
> the answer to my T-SQL question concerning establishing a stored
> procedure that I can schedule after the Friday backup but before the
> Saturday backup - that will truncate and shrink the transaction log(s)
> for each of the user databases automatically.
> I will also check out the WITH INIT, etc. in the BOL.
> Thanks in advance for any more feedback.
> Rich
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Rich
I suggest you setup a SQL 2000 Agent Job executing the following TSQL
command:
DBCC SHRINKFILE(2, TRUNCATEONLY)
on the DB(s) in question. Set it to run off hours, the "2" should be
for the
Log(LDF) file, a "1" should be the for MDF file.
I'm still waiting myself to see a script that will parse thru the DBs
on a server and build the DBCC above for every DB.
good luck
Steven|||You could do it vb6 or .net using
Public oSQLServer As New SQLDMO.SQLServer
For Each oDatabase In oSQLServer.Databases
'If odatabase.Status <> SQLOLEDBStat_Inaccessible Then
form1.List_Databases.AddItem oDatabase.Name
'frmExecVB.lstCat.AddItem oDatabase.Name
'End If
Next oDatabase
Then you can build up a string using filesystem object to write your
script for you.
Have a look at SQLDMO Object, it rocks !
I've even got some code to look round your network and come back with a
list of sql servers...
Tim Heap
Software & Database Manager
POSTAR Ltd
www.postar.co.uk
tim@.postar.co.uk
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Thanks for your reply. I have still been working on a solution and have
not had the opportunity to write back. I work for a relatively small
company where I were a number of 'hats' of which being the DBA is just
one of them.
Anyway, I setup transaction log backups hourly throughout the day (I
don't have that many transactions - but do have a lot of databases) and
that is working fine. In tracking the transaction log size, I was able
to summize that it grew immensely on late Saturday evenings - which is
the time I kick off my database maintenance plan which includes
rebuilding indexes and optimizing the databases.
For example, one particular database who MDF is 540 mb and had a
transaction log of 52 mb throughout the week - had it's transaction log
grow to 468 mb after running the maintenance plan.
I am thinking that I should then 1) backup the transaction log with
truncate only, then 2) run the SHRINKFILE command to reduce the
transaction log to say... 50 mb. 3) Then backup the entire database.
If that works, I would need to incorporate that into all of my db plans.
Any thoughts/concerns? Thanks again for everyone's assitance!!!
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||try the following to truncate TLogs:
--drop procedure xxx_SQL_Logspace_truncate_all
--CREATE procedure xxx_SQL_Logspace_truncate_all as
USE Master
DECLARE @.logname varchar(20)
DECLARE @.query varchar(255)
DECLARE LogList_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
ORDER BY name
OPEN LogList_cursor
FETCH NEXT FROM LogList_cursor INTO @.logname
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- add DB's you want to skip to the following list
IF RTRIM(@.logname) = "master" or
RTRIM(@.logname) = "model" or
RTRIM(@.logname) = "msdb" or
RTRIM(@.logname) = "pubs" or
RTRIM(@.logname) = "Northwind"
print @.logname + ' skipped'
ELSE
begin
Set @.query = "DBCC SHRINKFILE("+ RTRIM(@.logname) + ".LDF, TRUNCATEONLY)"
select @.query
end
FETCH NEXT FROM LogList_cursor into @.logname
END
-- following will list all DB's Logspace stats
DBCC SQLPERF(LOGSPACE)
CLOSE LogList_cursor
DEALLOCATE LogList_cursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Creating a T-SQL Stored Procedure to Truncate and Shrink ALL Log Files

Sunday, March 11, 2012

Creating a new instance of SQL

Hi, We have a SQL server and I have installed a new instance for some
specific application database.
The installation go sucessfully coped all files and I can see the 2
instances on my Program Files folder and the 2 services running.
My question is : How is suppose I swich or see the 2 instances ? I have
open Entrerprise and I can't see the new instance?
I need to install from the CD others componentes like : Entreprise for the
new instances?
Thanks
Andrea
As far as the new instance is a "new server" ou have to register it within
the EM. Register the server with that naming convention
[NameoftheServer]\[InstanceName]. To list the available server you can issue
the following command at the command prompt: OSQL -L
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Colores" <Colores@.discussions.microsoft.com> schrieb im Newsbeitrag
news:6BC321C2-4DF0-4B34-B018-5683536148C7@.microsoft.com...
> Hi, We have a SQL server and I have installed a new instance for some
> specific application database.
> The installation go sucessfully coped all files and I can see the 2
> instances on my Program Files folder and the 2 services running.
> My question is : How is suppose I swich or see the 2 instances ? I have
> open Entrerprise and I can't see the new instance?
> I need to install from the CD others componentes like : Entreprise for the
> new instances?
> Thanks
> Andrea
|||Hi,
No need to install the client components again. You could register the new
server name inside enterprise manager or in query analyzer you
could type the Hostname\sql servername (See the new sql server error logs
for the exact server name).
Thanks
Hari
SQL Server Mvp
"Colores" <Colores@.discussions.microsoft.com> wrote in message
news:6BC321C2-4DF0-4B34-B018-5683536148C7@.microsoft.com...
> Hi, We have a SQL server and I have installed a new instance for some
> specific application database.
> The installation go sucessfully coped all files and I can see the 2
> instances on my Program Files folder and the 2 services running.
> My question is : How is suppose I swich or see the 2 instances ? I have
> open Entrerprise and I can't see the new instance?
> I need to install from the CD others componentes like : Entreprise for the
> new instances?
> Thanks
> Andrea

Creating a new database

I created a database using the following command :

create database krish on (Name='krish', filename='C:\Program Files\Microsoft SQL Server\MSSQL\data\krish.mdf', size=25, maxsize=50,filegrowth=5%)

This actually created the database. Now, I wanted to view this database info being stored inside the SQL Server system tables. I looked at "sysdatabases" table and found an entry as expected for "krish" but I could not trace where the info corresponding to the size of the database was stored ie.25MB . (I looked ad "sysdevices" but couldn't find any entry for the newly created database).

In which table is it stored ?

Any help is appreciated.select size
from krish.dbo.sysfiles|||Thanks Hans. That worked

Thursday, March 8, 2012

creating a linked server

I am trying to use linked servers as a means of accessing files in an Access
database. I have unsuccessfully tried 2 methods:
1) using sp_addlinkedserver. I am told that I cannot create a linked server
for Access tables, unless the Access file exists on the same server as SQL.
This seems odd to me. Is this true? Can I create a linked server for an
.mdb file that exists on a different server? And can I do this using
sp_addlinkedserver?
2) using OPENROWSET. I get an "Authentication failed" error message with
the following:
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\temp\z.mdb';'';'', Untitled)
Do I need to specify some kind of security? What am I missing?
My knowledge on security issues is very limited. Any help on getting me in
the right direction would be appreciated. I am using SQL 8.00.194
Thanks
DionI would recommend looking at this thread in the
microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL". We
just reviewed it there.
--
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> I am trying to use linked servers as a means of accessing files in an
Access
> database. I have unsuccessfully tried 2 methods:
> 1) using sp_addlinkedserver. I am told that I cannot create a linked
server
> for Access tables, unless the Access file exists on the same server as
SQL.
> This seems odd to me. Is this true? Can I create a linked server for an
> .mdb file that exists on a different server? And can I do this using
> sp_addlinkedserver?
> 2) using OPENROWSET. I get an "Authentication failed" error message with
> the following:
> SELECT *
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\temp\z.mdb';'';'', Untitled)
> Do I need to specify some kind of security? What am I missing?
> My knowledge on security issues is very limited. Any help on getting me
in
> the right direction would be appreciated. I am using SQL 8.00.194
> Thanks
> Dion|||So the Access file needs to be either on the local drive, or on the same
server as sql, right? Thanks much for the thread.
Dion
"Simon Worth" wrote:
> I would recommend looking at this thread in the
> microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL". We
> just reviewed it there.
> --
> Simon Worth
>
> "Dion" <Dion@.discussions.microsoft.com> wrote in message
> news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > I am trying to use linked servers as a means of accessing files in an
> Access
> > database. I have unsuccessfully tried 2 methods:
> > 1) using sp_addlinkedserver. I am told that I cannot create a linked
> server
> > for Access tables, unless the Access file exists on the same server as
> SQL.
> > This seems odd to me. Is this true? Can I create a linked server for an
> > .mdb file that exists on a different server? And can I do this using
> > sp_addlinkedserver?
> > 2) using OPENROWSET. I get an "Authentication failed" error message with
> > the following:
> > SELECT *
> > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > 'c:\temp\z.mdb';'';'', Untitled)
> > Do I need to specify some kind of security? What am I missing?
> >
> > My knowledge on security issues is very limited. Any help on getting me
> in
> > the right direction would be appreciated. I am using SQL 8.00.194
> >
> > Thanks
> > Dion
>
>|||The .mdb database file must reside on the server. data_source is evaluated
on the server, not the client, and the path must be valid on the server.
I didn't try it with the access db on the local drive. I assume that won't
work, as it's counter to what BOL says. But try it out, and see if it
works.
--
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> So the Access file needs to be either on the local drive, or on the same
> server as sql, right? Thanks much for the thread.
> Dion
>
> "Simon Worth" wrote:
> > I would recommend looking at this thread in the
> > microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL".
We
> > just reviewed it there.
> > --
> > Simon Worth
> >
> >
> > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > > I am trying to use linked servers as a means of accessing files in an
> > Access
> > > database. I have unsuccessfully tried 2 methods:
> > > 1) using sp_addlinkedserver. I am told that I cannot create a linked
> > server
> > > for Access tables, unless the Access file exists on the same server as
> > SQL.
> > > This seems odd to me. Is this true? Can I create a linked server
for an
> > > .mdb file that exists on a different server? And can I do this using
> > > sp_addlinkedserver?
> > > 2) using OPENROWSET. I get an "Authentication failed" error message
with
> > > the following:
> > > SELECT *
> > > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > > 'c:\temp\z.mdb';'';'', Untitled)
> > > Do I need to specify some kind of security? What am I missing?
> > >
> > > My knowledge on security issues is very limited. Any help on getting
me
> > in
> > > the right direction would be appreciated. I am using SQL 8.00.194
> > >
> > > Thanks
> > > Dion
> >
> >
> >|||Sorry, Simon, but if I can ask one last question (my hardware knowledge is
pretty limited). When you say "database file must reside on the server,"
you mean the Access file needs to be on the SAME server as SQL, right? In
other words, my probelm is I am trying to access one server from another
server.
Thanks
Rick
"Simon Worth" wrote:
> The .mdb database file must reside on the server. data_source is evaluated
> on the server, not the client, and the path must be valid on the server.
> I didn't try it with the access db on the local drive. I assume that won't
> work, as it's counter to what BOL says. But try it out, and see if it
> works.
> --
> Simon Worth
>
> "Dion" <Dion@.discussions.microsoft.com> wrote in message
> news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> > So the Access file needs to be either on the local drive, or on the same
> > server as sql, right? Thanks much for the thread.
> >
> > Dion
> >
> >
> > "Simon Worth" wrote:
> >
> > > I would recommend looking at this thread in the
> > > microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL".
> We
> > > just reviewed it there.
> > > --
> > > Simon Worth
> > >
> > >
> > > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > > news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > > > I am trying to use linked servers as a means of accessing files in an
> > > Access
> > > > database. I have unsuccessfully tried 2 methods:
> > > > 1) using sp_addlinkedserver. I am told that I cannot create a linked
> > > server
> > > > for Access tables, unless the Access file exists on the same server as
> > > SQL.
> > > > This seems odd to me. Is this true? Can I create a linked server
> for an
> > > > .mdb file that exists on a different server? And can I do this using
> > > > sp_addlinkedserver?
> > > > 2) using OPENROWSET. I get an "Authentication failed" error message
> with
> > > > the following:
> > > > SELECT *
> > > > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > > > 'c:\temp\z.mdb';'';'', Untitled)
> > > > Do I need to specify some kind of security? What am I missing?
> > > >
> > > > My knowledge on security issues is very limited. Any help on getting
> me
> > > in
> > > > the right direction would be appreciated. I am using SQL 8.00.194
> > > >
> > > > Thanks
> > > > Dion
> > >
> > >
> > >
>
>|||So here's the thing.
You can add an Access DB .mdb file to SQL Server as a linked server. You
can do so if the file is on a network share somewhere. However (and this
may not always be the case - I have limited knowledge about access linked
servers) you will only be able to query the linked access db server from the
server itself if it's on a network share. If you want to connect to the
access linked server from another sql server - the access db must reside on
a local drive of the server you added the link to.
example
I have a desktop computer and I'm running SQL Server 2000 dev edition.
I have an access db on a network drive (\\prodserv1\accessdbs\MyDB.mdb)
I can add \\prodserv1\accessdbs\MyDB.mdb to my desktop computer running sql
server, and can query it from Query analyzer.
But, if I log into my desktop computer running SQL Server from another
desktop computer using Query Analyzer, I cannot query the linked access
server anymore (select * from LinkedServerName...TableName). I get an error
message.
Now, if I copy \\prodserv1\accessdbs\MyDB.mdb to C:\Temp\MyDB.mdb, and
change the data source in the linked server from
\\prodserv1\accessdbs\MyDB.mdb to C:\Temp\MyDB.mdb - I can then query the
linked server that I defined on my desktop computer from another desktop
computer.
--
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:3769D201-5F4C-465E-A79A-0D88918F5EF7@.microsoft.com...
> Sorry, Simon, but if I can ask one last question (my hardware knowledge is
> pretty limited). When you say "database file must reside on the server,"
> you mean the Access file needs to be on the SAME server as SQL, right? In
> other words, my probelm is I am trying to access one server from another
> server.
> Thanks
> Rick
> "Simon Worth" wrote:
> > The .mdb database file must reside on the server. data_source is
evaluated
> > on the server, not the client, and the path must be valid on the server.
> >
> > I didn't try it with the access db on the local drive. I assume that
won't
> > work, as it's counter to what BOL says. But try it out, and see if it
> > works.
> >
> > --
> > Simon Worth
> >
> >
> > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> > > So the Access file needs to be either on the local drive, or on the
same
> > > server as sql, right? Thanks much for the thread.
> > >
> > > Dion
> > >
> > >
> > > "Simon Worth" wrote:
> > >
> > > > I would recommend looking at this thread in the
> > > > microsoft.public.sqlserver.dts newsgroup with subject "Access and
SQL".
> > We
> > > > just reviewed it there.
> > > > --
> > > > Simon Worth
> > > >
> > > >
> > > > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > > > news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > > > > I am trying to use linked servers as a means of accessing files in
an
> > > > Access
> > > > > database. I have unsuccessfully tried 2 methods:
> > > > > 1) using sp_addlinkedserver. I am told that I cannot create a
linked
> > > > server
> > > > > for Access tables, unless the Access file exists on the same
server as
> > > > SQL.
> > > > > This seems odd to me. Is this true? Can I create a linked
server
> > for an
> > > > > .mdb file that exists on a different server? And can I do this
using
> > > > > sp_addlinkedserver?
> > > > > 2) using OPENROWSET. I get an "Authentication failed" error
message
> > with
> > > > > the following:
> > > > > SELECT *
> > > > > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > > > > 'c:\temp\z.mdb';'';'', Untitled)
> > > > > Do I need to specify some kind of security? What am I missing?
> > > > >
> > > > > My knowledge on security issues is very limited. Any help on
getting
> > me
> > > > in
> > > > > the right direction would be appreciated. I am using SQL 8.00.194
> > > > >
> > > > > Thanks
> > > > > Dion
> > > >
> > > >
> > > >
> >
> >
> >

Wednesday, March 7, 2012

Creating a DB User not on current Machine

During my development phase, I publish updates to my testing server
environment by literally detaching and copying my SQL Server DB files to tha
t
machine and re-attaching them. This works fine and is very convenient. The
only problem is that each time I do it, I have to create a user in my DB
after I have copied it to the destination server which matches the username
under which IIS is running web access. This is a user which does not exist o
n
the instance where I do my development.
I was hoping that there was a way to create a user in the database with a
windows login which deosn't exist on that machine, but it fails when I try t
o
do that, saying that that user doesn't exist. Is there *any* way to get a
Windows Authentication-style DB user to exist in my database where that user
doesn't exist in on the current server so that when I publish the database
files, I won't have to go in and create that user in the database each time?
Does this question make sense?
AlexOn Jun 11, 10:11 am, Alex Maghen <AlexMag...@.newsgroup.nospam> wrote:
> During my development phase, I publish updates to my testing server
> environment by literally detaching and copying my SQL Server DB files to t
hat
> machine and re-attaching them. This works fine and is very convenient. The
> only problem is that each time I do it, I have to create a user in my DB
> after I have copied it to the destination server which matches the usernam
e
> under which IIS is running web access. This is a user which does not exist
on
> the instance where I do my development.
> I was hoping that there was a way to create a user in the database with a
> windows login which deosn't exist on that machine, but it fails when I try
to
> do that, saying that that user doesn't exist. Is there *any* way to get a
> Windows Authentication-style DB user to exist in my database where that us
er
> doesn't exist in on the current server so that when I publish the database
> files, I won't have to go in and create that user in the database each tim
e?
> Does this question make sense?
> Alex
If windows login is a domain user , you can create that login in your
database server. If not , create a windows login in your database
server with the same name and (may be same password).Provide proper
acces to login .
Database user should also map to proper windows login .
After restoring the database run the sp on the restored database
exec sp_change_users_login 'auto_fix','database username'
this will link login with user|||Hello Alex,
From your description, I understand that you'd like to create a DB user to
exist in your database matched to a not existing Windows login before you
detach/attach the database to a new server.
I'm afraid this option is not available due to the behavior of SQL
Server.When you move a database from one server that is running SQL Server
to another server that is running SQL Server, a mismatch may occur between
the security identification numbers (SIDs) of the logins in the master
database and the users in the user database.
By default, SQL Server 7.0, SQL Server 2000, and SQL Server 2005 provide
the sp_change_users_login system stored procedure to map these mismatched
users. However, you can only use the sp_change_users_login stored procedure
to map standard SQL Server logins and you must perform these mapping for
one user at a time.
In SQL Server 7.0 or later versions, you can maintain the mapping between
the logins in the master database and the users in the user database by
using the SIDs. This mapping is required to maintain correct permissions
for the logins in the user databases. When a login on the new server does
not have matched SID of one of the user on the database moved, you have to
create a new database base user to map them. Also, it's not possible to
create a local user on a different machine.
The best option is that you use a domain user so that the login exist on
the both SQL Servers. After you move the database, the SID is matched
automatically and you don't need to re-create the DB user for the login.
You may want to see the following article for more details:
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/kb/246133
If anything is unclear or you have further comments, please feel free to
post back. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Just one bit of confusion I have about all of this: Creating a user in the
database which is based on a Windows User is no problem: I can create the
same username on both my Dev and Staging machines. But I *think* the problem
is that the username that you need to provide in SQL Server has the machine
name built into it:
<Machine Name>\<User Name>
So even if I have exactly the same user name on both machines, will I be
able to do what I want? By the way, this is SQL Server 2005.
Thanks again.
Alex
""Peter YangMSFT]"" wrote:

> Hello Alex,
> From your description, I understand that you'd like to create a DB user to
> exist in your database matched to a not existing Windows login before you
> detach/attach the database to a new server.
> I'm afraid this option is not available due to the behavior of SQL
> Server.When you move a database from one server that is running SQL Server
> to another server that is running SQL Server, a mismatch may occur between
> the security identification numbers (SIDs) of the logins in the master
> database and the users in the user database.
> By default, SQL Server 7.0, SQL Server 2000, and SQL Server 2005 provide
> the sp_change_users_login system stored procedure to map these mismatched
> users. However, you can only use the sp_change_users_login stored procedur
e
> to map standard SQL Server logins and you must perform these mapping for
> one user at a time.
> In SQL Server 7.0 or later versions, you can maintain the mapping between
> the logins in the master database and the users in the user database by
> using the SIDs. This mapping is required to maintain correct permissions
> for the logins in the user databases. When a login on the new server does
> not have matched SID of one of the user on the database moved, you have to
> create a new database base user to map them. Also, it's not possible to
> create a local user on a different machine.
>
> The best option is that you use a domain user so that the login exist on
> the both SQL Servers. After you move the database, the SID is matched
> automatically and you don't need to re-create the DB user for the login.
> You may want to see the following article for more details:
>
> 314546 HOW TO: Move Databases Between Computers That Are Running SQL Serve
r
> http://support.microsoft.com/?id=314546
> 240872 HOW TO: Resolve Permission Issues When You Move a Database Between
> http://support.microsoft.com/?id=240872
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://support.microsoft.com/kb/246133
> If anything is unclear or you have further comments, please feel free to
> post back. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hello Alex,
As I mentioined, the database user is mapped to the SQL login by SID. Even
the user name on both machines are the same, the SIDs are the different.
Therefore, I don't think it could meet your requriement.
However, if you use a domin user such as domain1\user, you could add the
user as login on both server, and map to the database user at one server
for a database could be mapped to properly if the database is moved to
another database.
You could find the SIDs of logins and database users from the following
queries:
select * from master..syslogins
select * from sysusers
As Srinivas mentioned, sp_change_users_login could help in the situation if
you don't want to map the user/login yourself.
Please keep in mind that sp_change_users_login 'auto_fix' corrects an
orphan user by either:
1. If a matching login does not exist -> create it with a blank password.
2. If a matching login exists -> update SID
If you have further questions or comments on the issue, please feel free to
let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Sunday, March 25, 2012

Creating a T-SQL Stored Procedure to Truncate and Shrink ALL Log Files

Creating a T-SQL Stored Procedure to Truncate and Shrink ALL Log Files

Sunday, March 11, 2012

Creating a new instance of SQL

Creating a new database

Thursday, March 8, 2012

creating a linked server

creating a linked server

creating a linked server

Wednesday, March 7, 2012

Creating a DB User not on current Machine

Creating DB from scripts

Blog Archive

About Me