I've read in some threads that it's possible to backup a database on a network shared folder using this..
Backup Database temp to disk = "\\servername\shared_folder\temp.bkp" with init
I have tried this first using a local folder and it succeeds but when I try to do the backup on a network shared folder, the backup fails saying unable to find backup device. I already made sure that the shared folder gives read/write access to 'Everyone"
on the sharing option. By the way, the pcs are configured only as workgroup. I read in one thread that you have be in a domain to be able to do this.
hope to get help from you guys
thanks,
ace
Hi,
Try doing this:-
1. Have a common OS user in both systems, Say name of the OS user is
backupuser.
2. Start the MSSQL Server and SQl Agent service using backupuser
How to do that:
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option. There you give a the backupuser
and password to start the service.
Now stop and start the MSSQL Serevr service.
3. Give the necessary privileges (write) in the remote machine directory
and share.
4. After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to
Disk='\\RemoteServername\share\folder\dbname.bak' with init ( With init will
overwrite the backup file every time)
I think this will definitely work out.
Thanks
Hari
MCDBA
"ace rojas" <acerojas3@.yahoo.com> wrote in message
news:B588771A-EB3A-4E0A-A438-238BB7D7102D@.microsoft.com...
> I've read in some threads that it's possible to backup a database on a
network shared folder using this..
> Backup Database temp to disk = "\\servername\shared_folder\temp.bkp" with
init
> I have tried this first using a local folder and it succeeds but when I
try to do the backup on a network shared folder, the backup fails saying
unable to find backup device. I already made sure that the shared folder
gives read/write access to 'Everyone" on the sharing option. By the way, the
pcs are configured only as workgroup. I read in one thread that you have be
in a domain to be able to do this.
> hope to get help from you guys
> thanks,
> ace
|||The problem is still permissions... Since you are in a workgroup, you will
either have to add the SQL Server login account or SQL AGent login account
as having permissions on the Other server.
The file permissions needed for backup are those for the login that sql
server service uses (when the backup is being done in SEM or Query
Analyzer), and the permissions are those that SQL Agent uses when the backup
is scheduled...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OlVlyMGSEHA.3140@.tk2msftngp13.phx.gbl...
> Hi,
> Try doing this:-
> 1. Have a common OS user in both systems, Say name of the OS user is
> backupuser.
> 2. Start the MSSQL Server and SQl Agent service using backupuser
> How to do that:
> So go to Control Panel -- Admin Tools -- Services -- MSSQL Server
sercice--
> Double click and select the "Log on" option. There you give a the
backupuser
> and password to start the service.
> Now stop and start the MSSQL Serevr service.
> 3. Give the necessary privileges (write) in the remote machine directory
> and share.
> 4. After this you try to execute the Backup database command in Query
> Analyzer:-
> Backup database <dbname> to
> Disk='\\RemoteServername\share\folder\dbname.bak' with init ( With init
will[vbcol=seagreen]
> overwrite the backup file every time)
> I think this will definitely work out.
> Thanks
> Hari
> MCDBA
>
> "ace rojas" <acerojas3@.yahoo.com> wrote in message
> news:B588771A-EB3A-4E0A-A438-238BB7D7102D@.microsoft.com...
> network shared folder using this..
with
> init
> try to do the backup on a network shared folder, the backup fails saying
> unable to find backup device. I already made sure that the shared folder
> gives read/write access to 'Everyone" on the sharing option. By the way,
the
> pcs are configured only as workgroup. I read in one thread that you have
be
> in a domain to be able to do this.
>
|||ace rojas
alliance technologies corp, phil
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Thanks guys, I did it and it worked even if the two pcs belong to
different workgroups. As long as there is common user and password for
the Sqlserver NTservice (logon as) and the PC sharing the folder on
where i'll do the backup.
thanks again
ace rojas
alliance technologies corp, phil
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Thursday, March 22, 2012
Sunday, March 11, 2012
Database and log file size
I've been reading SQL Server High Availability and went thru some recent
webcasts by Kimberly Tripp and from what I've found out, I think we need to
do some adjustments to our database. It is SQL Server 2000 back end for an
ecommerce site. When it was set up, we just went with default options, but
now we need to do some changing. When I did dbcc loginfo, it came back with
275 vlfs. The database size after about a year and a half is 3.9 GB, it says
space available 1.4 GB.
Space Allocated is
Data File 2.4 gb
Transaction log 1.4 GB
It is running in full recovery mode. Transaction logs are backed up every 2
hours. Database is backed up once a day.
I'm afraid that some of these properties are causing slowdowns and deadlocks.
What would be a reasonable size to set the db and transaction log to at this
growth rate? And grow it by what % or MB? I don't really know what to even
start with and I don't want to do something that will make performance worse.
Thanks for any help.
Judy
Judy,
These settings basically look fine and are probably not associated with
slowdowns and especially not deadlocks. You should always have plenty of
free space in the data and log files. It sounds like the log file is fine
but I can't tell from that how much free space you have in the datafile.
Right click on the database node in EM and choose "view - Task Pad" to see
the breakdown for each file.The growth should be a fixed size in MB. The
size should be small enough so that it can grow in about 10 seconds or less
but not too small that it would grow over and over.
Andrew J. Kelly SQL MVP
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
> I've been reading SQL Server High Availability and went thru some recent
> webcasts by Kimberly Tripp and from what I've found out, I think we need
> to
> do some adjustments to our database. It is SQL Server 2000 back end for an
> ecommerce site. When it was set up, we just went with default options, but
> now we need to do some changing. When I did dbcc loginfo, it came back
> with
> 275 vlfs. The database size after about a year and a half is 3.9 GB, it
> says
> space available 1.4 GB.
> Space Allocated is
> Data File 2.4 gb
> Transaction log 1.4 GB
> It is running in full recovery mode. Transaction logs are backed up every
> 2
> hours. Database is backed up once a day.
> I'm afraid that some of these properties are causing slowdowns and
> deadlocks.
>
> What would be a reasonable size to set the db and transaction log to at
> this
> growth rate? And grow it by what % or MB? I don't really know what to even
> start with and I don't want to do something that will make performance
> worse.
> Thanks for any help.
> --
> Judy
|||The database shows 2438.75MB allocated with 2372.06MB used.
The transaction log shows .1441.3 MB allocated with 104.9 used after a log
backup an hour and 1/2 previously.
The growth is fixed at 100MB for both database and log files.
Judy
"Andrew J. Kelly" wrote:
> Judy,
> These settings basically look fine and are probably not associated with
> slowdowns and especially not deadlocks. You should always have plenty of
> free space in the data and log files. It sounds like the log file is fine
> but I can't tell from that how much free space you have in the datafile.
> Right click on the database node in EM and choose "view - Task Pad" to see
> the breakdown for each file.The growth should be a fixed size in MB. The
> size should be small enough so that it can grow in about 10 seconds or less
> but not too small that it would grow over and over.
> --
> Andrew J. Kelly SQL MVP
>
> "Judy" <Judy@.discussions.microsoft.com> wrote in message
> news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
>
>
|||You will want more free space in the data file. The log seems fine.
Andrew J. Kelly SQL MVP
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:0EF8EC72-769A-4304-8107-36ECB56B465B@.microsoft.com...[vbcol=seagreen]
> The database shows 2438.75MB allocated with 2372.06MB used.
> The transaction log shows .1441.3 MB allocated with 104.9 used after a log
> backup an hour and 1/2 previously.
> The growth is fixed at 100MB for both database and log files.
> --
> Judy
>
> "Andrew J. Kelly" wrote:
|||The idea log size is the smallest that you can make it without it having to
grow before you back it up. If the transaction log really reaches 1.4GB used
before a backup, then that is your ideal size - well...maybe a little bit of
room to spare...like perhaps 2GB.
So to follow Kimberly's suggestion, backup the transcaction log, shrink it,
and then set it's size back to 1.4GB. The idea is that having to grow the
log is what you want to avoid.
As for the database file grow amounts, i personally like the 20% grow
amount. Every time the database grows, 20% allocates a lot of space (A 2MB
file growing by 20% is a lot less space added compared to a 2GB file growing
by 20%)
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
> I've been reading SQL Server High Availability and went thru some recent
> webcasts by Kimberly Tripp and from what I've found out, I think we need
> to
> do some adjustments to our database. It is SQL Server 2000 back end for an
> ecommerce site. When it was set up, we just went with default options, but
> now we need to do some changing. When I did dbcc loginfo, it came back
> with
> 275 vlfs. The database size after about a year and a half is 3.9 GB, it
> says
> space available 1.4 GB.
> Space Allocated is
> Data File 2.4 gb
> Transaction log 1.4 GB
> It is running in full recovery mode. Transaction logs are backed up every
> 2
> hours. Database is backed up once a day.
> I'm afraid that some of these properties are causing slowdowns and
> deadlocks.
>
> What would be a reasonable size to set the db and transaction log to at
> this
> growth rate? And grow it by what % or MB? I don't really know what to even
> start with and I don't want to do something that will make performance
> worse.
> Thanks for any help.
> --
> Judy
webcasts by Kimberly Tripp and from what I've found out, I think we need to
do some adjustments to our database. It is SQL Server 2000 back end for an
ecommerce site. When it was set up, we just went with default options, but
now we need to do some changing. When I did dbcc loginfo, it came back with
275 vlfs. The database size after about a year and a half is 3.9 GB, it says
space available 1.4 GB.
Space Allocated is
Data File 2.4 gb
Transaction log 1.4 GB
It is running in full recovery mode. Transaction logs are backed up every 2
hours. Database is backed up once a day.
I'm afraid that some of these properties are causing slowdowns and deadlocks.
What would be a reasonable size to set the db and transaction log to at this
growth rate? And grow it by what % or MB? I don't really know what to even
start with and I don't want to do something that will make performance worse.
Thanks for any help.
Judy
Judy,
These settings basically look fine and are probably not associated with
slowdowns and especially not deadlocks. You should always have plenty of
free space in the data and log files. It sounds like the log file is fine
but I can't tell from that how much free space you have in the datafile.
Right click on the database node in EM and choose "view - Task Pad" to see
the breakdown for each file.The growth should be a fixed size in MB. The
size should be small enough so that it can grow in about 10 seconds or less
but not too small that it would grow over and over.
Andrew J. Kelly SQL MVP
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
> I've been reading SQL Server High Availability and went thru some recent
> webcasts by Kimberly Tripp and from what I've found out, I think we need
> to
> do some adjustments to our database. It is SQL Server 2000 back end for an
> ecommerce site. When it was set up, we just went with default options, but
> now we need to do some changing. When I did dbcc loginfo, it came back
> with
> 275 vlfs. The database size after about a year and a half is 3.9 GB, it
> says
> space available 1.4 GB.
> Space Allocated is
> Data File 2.4 gb
> Transaction log 1.4 GB
> It is running in full recovery mode. Transaction logs are backed up every
> 2
> hours. Database is backed up once a day.
> I'm afraid that some of these properties are causing slowdowns and
> deadlocks.
>
> What would be a reasonable size to set the db and transaction log to at
> this
> growth rate? And grow it by what % or MB? I don't really know what to even
> start with and I don't want to do something that will make performance
> worse.
> Thanks for any help.
> --
> Judy
|||The database shows 2438.75MB allocated with 2372.06MB used.
The transaction log shows .1441.3 MB allocated with 104.9 used after a log
backup an hour and 1/2 previously.
The growth is fixed at 100MB for both database and log files.
Judy
"Andrew J. Kelly" wrote:
> Judy,
> These settings basically look fine and are probably not associated with
> slowdowns and especially not deadlocks. You should always have plenty of
> free space in the data and log files. It sounds like the log file is fine
> but I can't tell from that how much free space you have in the datafile.
> Right click on the database node in EM and choose "view - Task Pad" to see
> the breakdown for each file.The growth should be a fixed size in MB. The
> size should be small enough so that it can grow in about 10 seconds or less
> but not too small that it would grow over and over.
> --
> Andrew J. Kelly SQL MVP
>
> "Judy" <Judy@.discussions.microsoft.com> wrote in message
> news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
>
>
|||You will want more free space in the data file. The log seems fine.
Andrew J. Kelly SQL MVP
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:0EF8EC72-769A-4304-8107-36ECB56B465B@.microsoft.com...[vbcol=seagreen]
> The database shows 2438.75MB allocated with 2372.06MB used.
> The transaction log shows .1441.3 MB allocated with 104.9 used after a log
> backup an hour and 1/2 previously.
> The growth is fixed at 100MB for both database and log files.
> --
> Judy
>
> "Andrew J. Kelly" wrote:
|||The idea log size is the smallest that you can make it without it having to
grow before you back it up. If the transaction log really reaches 1.4GB used
before a backup, then that is your ideal size - well...maybe a little bit of
room to spare...like perhaps 2GB.
So to follow Kimberly's suggestion, backup the transcaction log, shrink it,
and then set it's size back to 1.4GB. The idea is that having to grow the
log is what you want to avoid.
As for the database file grow amounts, i personally like the 20% grow
amount. Every time the database grows, 20% allocates a lot of space (A 2MB
file growing by 20% is a lot less space added compared to a 2GB file growing
by 20%)
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
> I've been reading SQL Server High Availability and went thru some recent
> webcasts by Kimberly Tripp and from what I've found out, I think we need
> to
> do some adjustments to our database. It is SQL Server 2000 back end for an
> ecommerce site. When it was set up, we just went with default options, but
> now we need to do some changing. When I did dbcc loginfo, it came back
> with
> 275 vlfs. The database size after about a year and a half is 3.9 GB, it
> says
> space available 1.4 GB.
> Space Allocated is
> Data File 2.4 gb
> Transaction log 1.4 GB
> It is running in full recovery mode. Transaction logs are backed up every
> 2
> hours. Database is backed up once a day.
> I'm afraid that some of these properties are causing slowdowns and
> deadlocks.
>
> What would be a reasonable size to set the db and transaction log to at
> this
> growth rate? And grow it by what % or MB? I don't really know what to even
> start with and I don't want to do something that will make performance
> worse.
> Thanks for any help.
> --
> Judy
Thursday, March 8, 2012
Database Access Error?
I got this error and ive no idea whey, whenever i click on a database in Visual Studio 2005, to use it (i was on my way to do stored procedures) i get that error.
Also, when the web application is running i get this error:
"Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.Login failed for user 'DOUGAL\dougal.m*******'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below."
I stared out my 2nd name. I realise this seems to be a failure to login to the database, however, they are file databases and i'm not aware of having ever set a password for either of them!
I also tried deleting the databases to add them again (they have no data at the moment) and i get the same error in Visual Studio and it doesnt let me.
Any suggestions will be greatly appreciatedLog in your system as DOUGAL\dougal.m*******, and go to the directory D:\www\Flougal\App_Data and see if you can first, even get there, and if you can, see if you can even see the database.mdf file, I'm guessing you can't. It's not a SQL Permissions error, it's an OS error (it mentions that in the error). That user can't open that file (in fact, you that user can't even SEE that file).|||i'm afraid i actually can see the file logged in as that user in the error|||Managed to fix the problem.
It turns out a file database cant be attached to a database server in the SQL Server Management Studio aswell as being used in VS2005.
Silly me.![Embarrassed [:$]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_vTazbGeoH9cAFIbzHT_VDkg04b_vIdxE9nz2h6jF-N0YyCnLJlGTGQ7Bg2WPYUSa4GRLDumX0UX3Cbhl6aOoBaAYicKx3f2R-sML5qNicBeBy4SpM7Jw=s0-d)
Saturday, February 25, 2012
Data Versioning Transactional Replication
Hi,
I've got a requirement to know when some data has been propagated to
all my (read-only) subscribers. This is because the data will be
cleaned, and the subscribers shouldn't "use" the new version for
certain queries until I know that the new data is there.
I've created a model where I have a "version" table with an identity,
that gets updated by the log reader every time it reads. I check the
version at the time the data changes are finished. Then, when i save
the version with the query that i want to run at the subscriber, and
wait until the version number is greater than or equal to my version
number.
This model seems to work, it requires me to add some steps to the
distribution job, but I'm wondering if there is a better way? Ie,
some form of replication that doesn't require me to custom code the
versioning scheme of the data.
Thx,
Doug
If the data is updated on the publisher in discrete batches you can always
wait for the distribution agent to complete. Other than that the scheme you
have implemented sounds like the best way to go. SQL 2005 will have tracer
tokens that might help with your requirement.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Douglas Tarr" <douglas.tarr@.gmail.com> wrote in message
news:c33eac8b.0409070825.1654dcb3@.posting.google.c om...
> Hi,
> I've got a requirement to know when some data has been propagated to
> all my (read-only) subscribers. This is because the data will be
> cleaned, and the subscribers shouldn't "use" the new version for
> certain queries until I know that the new data is there.
> I've created a model where I have a "version" table with an identity,
> that gets updated by the log reader every time it reads. I check the
> version at the time the data changes are finished. Then, when i save
> the version with the query that i want to run at the subscriber, and
> wait until the version number is greater than or equal to my version
> number.
> This model seems to work, it requires me to add some steps to the
> distribution job, but I'm wondering if there is a better way? Ie,
> some form of replication that doesn't require me to custom code the
> versioning scheme of the data.
> Thx,
> Doug
I've got a requirement to know when some data has been propagated to
all my (read-only) subscribers. This is because the data will be
cleaned, and the subscribers shouldn't "use" the new version for
certain queries until I know that the new data is there.
I've created a model where I have a "version" table with an identity,
that gets updated by the log reader every time it reads. I check the
version at the time the data changes are finished. Then, when i save
the version with the query that i want to run at the subscriber, and
wait until the version number is greater than or equal to my version
number.
This model seems to work, it requires me to add some steps to the
distribution job, but I'm wondering if there is a better way? Ie,
some form of replication that doesn't require me to custom code the
versioning scheme of the data.
Thx,
Doug
If the data is updated on the publisher in discrete batches you can always
wait for the distribution agent to complete. Other than that the scheme you
have implemented sounds like the best way to go. SQL 2005 will have tracer
tokens that might help with your requirement.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Douglas Tarr" <douglas.tarr@.gmail.com> wrote in message
news:c33eac8b.0409070825.1654dcb3@.posting.google.c om...
> Hi,
> I've got a requirement to know when some data has been propagated to
> all my (read-only) subscribers. This is because the data will be
> cleaned, and the subscribers shouldn't "use" the new version for
> certain queries until I know that the new data is there.
> I've created a model where I have a "version" table with an identity,
> that gets updated by the log reader every time it reads. I check the
> version at the time the data changes are finished. Then, when i save
> the version with the query that i want to run at the subscriber, and
> wait until the version number is greater than or equal to my version
> number.
> This model seems to work, it requires me to add some steps to the
> distribution job, but I'm wondering if there is a better way? Ie,
> some form of replication that doesn't require me to custom code the
> versioning scheme of the data.
> Thx,
> Doug
Friday, February 17, 2012
Data type
I`m creating a database which will be use to store huge news data. Whatis the perfect data type for the news content, because I`ve tried allthe data types but they can`t store very long news. Thank you very much.
The Text data type will do what you are looking to do.
The Text data type will do what you are looking to do.
Subscribe to:
Posts (Atom)