Hello,
I'm trying to automate a prod to test restore to run weekly on a SQL 2000
sp3a server. Its on the same machine so I can't use the copy db wizard.
Should i use DTS, replication or what. I don't want to deal with orphaned
users and the like. It should be as seamless as possible.
TIA,
jj
jj
Have you looked at BACKUP\RESTORE commands?
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OmwojWWNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm trying to automate a prod to test restore to run weekly on a SQL 2000
> sp3a server. Its on the same machine so I can't use the copy db wizard.
> Should i use DTS, replication or what. I don't want to deal with orphaned
> users and the like. It should be as seamless as possible.
> TIA,
> jj
>
Showing posts with label machine. Show all posts
Showing posts with label machine. Show all posts
Thursday, March 29, 2012
automating restore
Hello,
I'm trying to automate a prod to test restore to run weekly on a SQL 2000
sp3a server. Its on the same machine so I can't use the copy db wizard.
Should i use DTS, replication or what. I don't want to deal with orphaned
users and the like. It should be as seamless as possible.
TIA,
jjjj
Have you looked at BACKUP\RESTORE commands?
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OmwojWWNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm trying to automate a prod to test restore to run weekly on a SQL 2000
> sp3a server. Its on the same machine so I can't use the copy db wizard.
> Should i use DTS, replication or what. I don't want to deal with orphaned
> users and the like. It should be as seamless as possible.
> TIA,
> jj
>
I'm trying to automate a prod to test restore to run weekly on a SQL 2000
sp3a server. Its on the same machine so I can't use the copy db wizard.
Should i use DTS, replication or what. I don't want to deal with orphaned
users and the like. It should be as seamless as possible.
TIA,
jjjj
Have you looked at BACKUP\RESTORE commands?
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OmwojWWNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm trying to automate a prod to test restore to run weekly on a SQL 2000
> sp3a server. Its on the same machine so I can't use the copy db wizard.
> Should i use DTS, replication or what. I don't want to deal with orphaned
> users and the like. It should be as seamless as possible.
> TIA,
> jj
>
automating restore
Hello,
I'm trying to automate a prod to test restore to run weekly on a SQL 2000
sp3a server. Its on the same machine so I can't use the copy db wizard.
Should i use DTS, replication or what. I don't want to deal with orphaned
users and the like. It should be as seamless as possible.
TIA,
jjjj
Have you looked at BACKUP\RESTORE commands?
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OmwojWWNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm trying to automate a prod to test restore to run weekly on a SQL 2000
> sp3a server. Its on the same machine so I can't use the copy db wizard.
> Should i use DTS, replication or what. I don't want to deal with orphaned
> users and the like. It should be as seamless as possible.
> TIA,
> jj
>
I'm trying to automate a prod to test restore to run weekly on a SQL 2000
sp3a server. Its on the same machine so I can't use the copy db wizard.
Should i use DTS, replication or what. I don't want to deal with orphaned
users and the like. It should be as seamless as possible.
TIA,
jjjj
Have you looked at BACKUP\RESTORE commands?
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OmwojWWNFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm trying to automate a prod to test restore to run weekly on a SQL 2000
> sp3a server. Its on the same machine so I can't use the copy db wizard.
> Should i use DTS, replication or what. I don't want to deal with orphaned
> users and the like. It should be as seamless as possible.
> TIA,
> jj
>
Tuesday, March 27, 2012
Automating Data Extension Deployment
Hi,
I have written a Data Processing extension for my application and can deploy it on my development machine no problems. My question is: what is the "correct" way of deploying an extension to an end user's machine? Do I have to write a special program to find and modify the Reporting Server config files and copy the extension over. Surely many developers have the same need so there must be a generic solution to this problem, however, I haven't managed to find one.
I first came across this issue in SQL2000 and I thought/hoped it would be rectified with SQL2005 but it appears not to be (unless I'm missing something).
Any ideas would be greatly appreciated.
Thanks in advance,
Tim
Hi Tim,
I don't have an answer for you, but I share your situation. I'll let you know if I find anything, and would appreciate the same.
TIA, Mike.
sqlAutomating Data Extension Deployment
Hi,
I have written a Data Processing extension for my application and can deploy it on my development machine no problems. My question is: what is the "correct" way of deploying an extension to an end user's machine? Do I have to write a special program to find and modify the Reporting Server config files and copy the extension over. Surely many developers have the same need so there must be a generic solution to this problem, however, I haven't managed to find one.
I first came across this issue in SQL2000 and I thought/hoped it would be rectified with SQL2005 but it appears not to be (unless I'm missing something).
Any ideas would be greatly appreciated.
Thanks in advance,
Tim
Hi Tim,
I don't have an answer for you, but I share your situation. I'll let you know if I find anything, and would appreciate the same.
TIA, Mike.
automatically send a notice after 6 months (was "Date Question")
Hello,
I'm trying to write a query that will do the following... I'll give a quick background:
When a machine is installed, I record an installation date. We would like to automatically send a notice to our customers 6 months past this install date regarding scheduled maintenance.
Right now all I have is the notice, which is a report in Access with a query attached that pulls all machine sales that have install dates.
I'm not sure how to reference this situation... or how to start addressing the problem.
Basically:
Customer(CustomerID, CustomerName, CustomerEmail)
MachineDetail(DetailID, CustomerID, ModelID)
MachineModel(ModelID,ModelName)
InstallInfo(InstallID, DetailID, InstallComplete)
Please help :( Thanks,
CherishTry looking here (http://www.1keydata.com/sql/sql.html) or here (http://www.geocities.com/SiliconValley/Vista/2207/sql1.html) or here (http://www.w3schools.com/sql/default.asp).
;)|||use the DATEADD function
select ...
from ...
where InstallComplete
between dateadd("m",-6,date())
and dateadd("m",-5,date()) this will pull all installs between 5 and 6 months old
that way you can prepare the notices manually
sorry, no idea how to set up access to do this automatically
perhaps ask in the access forum|||Create a stored procedure starting with the code posted by r937, that selects the customers you want to send a notice to and either send an e-mail or write the notices to a file.
Create a batch script to execute this procedure ONCE per month, depending on your OS use:
1) Dos cmd and Win2K(XP) task scheduler or 2) Unix KSH and crontab or 3) Oracle jobs.
There have been many posts in this and other forums on how to send e-mail from either Oracle or MS SQL.
:rolleyes:
PS: Ooops, just noticed you mentioned Access...
I beleive you can e-mail the notices by having Access 'write' to Outlook.
Good Luck.|||LKBrwn_DBA, Access doesn't support stored procs|||LKBrwn_DBA, Access doesn't support stored procs
Yeah, I just noticed it was Access, but he can create a VB 'Procedure' or something like that. :(|||PS: Check these links:
MS Email FAQ (http://www.granite.ab.ca/access/email.htm) and MS ACCESS VB script (http://www.febooti.com/products/command-line-email/online-help/send-email-ms-access.html)
;)|||She, and thanks for all your help, I'll try out the suggestions today :)
I'm trying to write a query that will do the following... I'll give a quick background:
When a machine is installed, I record an installation date. We would like to automatically send a notice to our customers 6 months past this install date regarding scheduled maintenance.
Right now all I have is the notice, which is a report in Access with a query attached that pulls all machine sales that have install dates.
I'm not sure how to reference this situation... or how to start addressing the problem.
Basically:
Customer(CustomerID, CustomerName, CustomerEmail)
MachineDetail(DetailID, CustomerID, ModelID)
MachineModel(ModelID,ModelName)
InstallInfo(InstallID, DetailID, InstallComplete)
Please help :( Thanks,
CherishTry looking here (http://www.1keydata.com/sql/sql.html) or here (http://www.geocities.com/SiliconValley/Vista/2207/sql1.html) or here (http://www.w3schools.com/sql/default.asp).
;)|||use the DATEADD function
select ...
from ...
where InstallComplete
between dateadd("m",-6,date())
and dateadd("m",-5,date()) this will pull all installs between 5 and 6 months old
that way you can prepare the notices manually
sorry, no idea how to set up access to do this automatically
perhaps ask in the access forum|||Create a stored procedure starting with the code posted by r937, that selects the customers you want to send a notice to and either send an e-mail or write the notices to a file.
Create a batch script to execute this procedure ONCE per month, depending on your OS use:
1) Dos cmd and Win2K(XP) task scheduler or 2) Unix KSH and crontab or 3) Oracle jobs.
There have been many posts in this and other forums on how to send e-mail from either Oracle or MS SQL.
:rolleyes:
PS: Ooops, just noticed you mentioned Access...
I beleive you can e-mail the notices by having Access 'write' to Outlook.
Good Luck.|||LKBrwn_DBA, Access doesn't support stored procs|||LKBrwn_DBA, Access doesn't support stored procs
Yeah, I just noticed it was Access, but he can create a VB 'Procedure' or something like that. :(|||PS: Check these links:
MS Email FAQ (http://www.granite.ab.ca/access/email.htm) and MS ACCESS VB script (http://www.febooti.com/products/command-line-email/online-help/send-email-ms-access.html)
;)|||She, and thanks for all your help, I'll try out the suggestions today :)
Sunday, March 25, 2012
Automatically migratring metadata between instances of SQL Server
I want to move meta data between two copies of the same database running
on different machines. I do most of my work on one machine, but
sometimes have to work on a slow laptop. If I need to migrate metadata
(updated or new Stored Procedures and tables) from one machine to my
laptop, or back, is there a way I can automate the process? Is there
some way to say: hey sync this stored procedure definition in one
instance of SQL server with that in another instance?
Another way of asking the same question, but in more specific terms, is
whether there is a good way of backing up the metadata for a Stored
procedure or Table to a text file that can be placed in a source control
tool such as StarTeam or CVS? I know I can click on this and select that
and save the metadata, and then load that in on the other machine by
clicking on that and selecting this. But I want to get away from all
that laborious clicking and just run a script that automates the
process. For instance, how can I script the process of right clicking on
a table and then selecting "All Tasks | Generate SQL Script".
Thanks for your help or any hints you can offer.
- Charlie
Hi,
Try dbMaestro. It's a product that allows comparison, migration and archiving of database schema and data.
You can find it here:
http://www.extreme.co.il
"mark baekdal" wrote:
> check out www.dbghost.com for comparison, updating
> straight from your source code.
> database running
> machine, but
> migrate metadata
> machine to my
> process? Is there
> definition in one
> specific terms, is
> for a Stored
> a source control
> and select that
> other machine by
> away from all
> automates the
> right clicking on
> Script".
>
|||Hi,
Try dbMaestro. It's a product that allows comparison, migration and archiving of database schema and data.
You can find it here:
http://www.extreme.co.il
"mark baekdal" wrote:
> check out www.dbghost.com for comparison, updating
> straight from your source code.
> database running
> machine, but
> migrate metadata
> machine to my
> process? Is there
> definition in one
> specific terms, is
> for a Stored
> a source control
> and select that
> other machine by
> away from all
> automates the
> right clicking on
> Script".
>
sql
on different machines. I do most of my work on one machine, but
sometimes have to work on a slow laptop. If I need to migrate metadata
(updated or new Stored Procedures and tables) from one machine to my
laptop, or back, is there a way I can automate the process? Is there
some way to say: hey sync this stored procedure definition in one
instance of SQL server with that in another instance?
Another way of asking the same question, but in more specific terms, is
whether there is a good way of backing up the metadata for a Stored
procedure or Table to a text file that can be placed in a source control
tool such as StarTeam or CVS? I know I can click on this and select that
and save the metadata, and then load that in on the other machine by
clicking on that and selecting this. But I want to get away from all
that laborious clicking and just run a script that automates the
process. For instance, how can I script the process of right clicking on
a table and then selecting "All Tasks | Generate SQL Script".
Thanks for your help or any hints you can offer.
- Charlie
Hi,
Try dbMaestro. It's a product that allows comparison, migration and archiving of database schema and data.
You can find it here:
http://www.extreme.co.il
"mark baekdal" wrote:
> check out www.dbghost.com for comparison, updating
> straight from your source code.
> database running
> machine, but
> migrate metadata
> machine to my
> process? Is there
> definition in one
> specific terms, is
> for a Stored
> a source control
> and select that
> other machine by
> away from all
> automates the
> right clicking on
> Script".
>
|||Hi,
Try dbMaestro. It's a product that allows comparison, migration and archiving of database schema and data.
You can find it here:
http://www.extreme.co.il
"mark baekdal" wrote:
> check out www.dbghost.com for comparison, updating
> straight from your source code.
> database running
> machine, but
> migrate metadata
> machine to my
> process? Is there
> definition in one
> specific terms, is
> for a Stored
> a source control
> and select that
> other machine by
> away from all
> automates the
> right clicking on
> Script".
>
sql
Thursday, March 22, 2012
automatically copy database to another pc
When I create a setup program for my vb.net 1.0 app which has embedded sql express database, it successfully copies the database to the new machine alone with the upgraded app.
However, if I just copy the vb.exe app to the other pc and also copy the mdf, ldf files, I get an error opening the sql database.
I am presuming that the setup program does some kind of backup restore or detach, attach to copy in the .mdf.
If the user cannot do this from the management studio (or if they do not have the management studio installed), is there any way I can create some method to have this copying done automatically via some code by the user?
Thanks
SM Haig
Yes, you can run a restore script using the sqlcmd program. You can read more about the restore command here:
http://msdn2.microsoft.com/en-us/library/ms186858.aspx
Buck Woody
Tuesday, March 20, 2012
automatic sql server backup to networked machine
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV
1. Make sure the SQL Server service is running on a domain account.
2. use UNC name for the designation file, eg:
\\MYSERVER\MYSHARE\MyFolder\MyFile.bak.
3. Make sure the account in #1 has read/write privileges on the share.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV
|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
|||Just so happens there is a community article on that very subject.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV
1. Make sure the SQL Server service is running on a domain account.
2. use UNC name for the designation file, eg:
\\MYSERVER\MYSHARE\MyFolder\MyFile.bak.
3. Make sure the account in #1 has read/write privileges on the share.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV
|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
|||Just so happens there is a community article on that very subject.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
automatic sql server backup to networked machine
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV1. Make sure the SQL Server service is running on a domain account.
2. use UNC name for the designation file, eg:
\\MYSERVER\MYSHARE\MyFolder\MyFile.bak.
3. Make sure the account in #1 has read/write privileges on the share.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV|||Just so happens there is a community article on that very subject.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV1. Make sure the SQL Server service is running on a domain account.
2. use UNC name for the designation file, eg:
\\MYSERVER\MYSHARE\MyFolder\MyFile.bak.
3. Make sure the account in #1 has read/write privileges on the share.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV|||Just so happens there is a community article on that very subject.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
automatic sql server backup to networked machine
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV
1. Make sure the SQL Server service is running on a domain account.
2. use UNC name for the designation file, eg:
\\MYSERVER\MYSHARE\MyFolder\MyFile.bak.
3. Make sure the account in #1 has read/write privileges on the share.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV
|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
|||Just so happens there is a community article on that very subject.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
sql
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV
1. Make sure the SQL Server service is running on a domain account.
2. use UNC name for the designation file, eg:
\\MYSERVER\MYSHARE\MyFolder\MyFile.bak.
3. Make sure the account in #1 has read/write privileges on the share.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV
|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
|||Just so happens there is a community article on that very subject.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
sql
automatic sql server backup to networked machine
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV1. Make sure the SQL Server service is running on a domain account.
2. use UNC name for the designation file, eg:
\\MYSERVER\MYSHARE\MyFolder\MyFile.bak.
3. Make sure the account in #1 has read/write privileges on the share.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV|||Just so happens there is a community article on that very subject.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV1. Make sure the SQL Server service is running on a domain account.
2. use UNC name for the designation file, eg:
\\MYSERVER\MYSHARE\MyFolder\MyFile.bak.
3. Make sure the account in #1 has read/write privileges on the share.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV|||Just so happens there is a community article on that very subject.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
Monday, March 19, 2012
Automatic Log expansion
SQL Server 2000 SP4
On an underpowered machine (mostly an old RAID 5 controller/drives), hosting
Visnetic MailFlow we had a .LDF file that was 10 GB (already dealt with).
When the .LDF file filled, SQL Server did a 10% expansion and the entire
server locked up for several minutes.
Is it normal for the system to refuse connections when expanding the .LDF
file? My guess is yes, because it has nowhere to put log entries (though I
would have hoped it would do the expansion before it filled, thus allowing
continued use).
How about when the .MDF/.NDF is expanded?
Thanks,
JayA 10 gig TLog? Wow :)
Is not it too big mate? You have an issue which is more important than
"expanding" thing... You better take a look at the following documentation:
Optimizing Transaction Log Performance
http://msdn2.microsoft.com/en-us/library/aa178420(SQL.80).aspx
Transaction Logs
http://msdn2.microsoft.com/en-us/library/aa933065(SQL.80).aspx
Managing the Transaction Log (Applies to SQL Server 2005, however, it's
gonna give you precious ideas I believe)
http://msdn2.microsoft.com/en-us/library/ms345382.aspx
--
Ekrem Önsoy
"Jay" <nospan@.nospam.org> wrote in message
news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000 SP4
> On an underpowered machine (mostly an old RAID 5 controller/drives),
> hosting Visnetic MailFlow we had a .LDF file that was 10 GB (already dealt
> with). When the .LDF file filled, SQL Server did a 10% expansion and the
> entire server locked up for several minutes.
> Is it normal for the system to refuse connections when expanding the .LDF
> file? My guess is yes, because it has nowhere to put log entries (though I
> would have hoped it would do the expansion before it filled, thus allowing
> continued use).
> How about when the .MDF/.NDF is expanded?
> Thanks,
> Jay
>|||Sounds like you have Full recovery mode enabled and you are not backing up
your tlog. Common mistake.
You should size tlog appropriately for your workload, data size and backup
plan. It should NEVER grow automatically - you should control this if
needed during periods of minimal activity. Autogrowth should only fire if
something unexpected happens.
"Jay" <nospan@.nospam.org> wrote in message
news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000 SP4
> On an underpowered machine (mostly an old RAID 5 controller/drives),
> hosting Visnetic MailFlow we had a .LDF file that was 10 GB (already dealt
> with). When the .LDF file filled, SQL Server did a 10% expansion and the
> entire server locked up for several minutes.
> Is it normal for the system to refuse connections when expanding the .LDF
> file? My guess is yes, because it has nowhere to put log entries (though I
> would have hoped it would do the expansion before it filled, thus allowing
> continued use).
> How about when the .MDF/.NDF is expanded?
> Thanks,
> Jay
>|||Jay,
I wouldn't expect SQL Server to to refuse connections. But perhaps this was perceived so, because
the app tries to do some modification when it connects. Even the smallest modifications would
require a log records and if the log is full, well...
No, database files are not expended in advance, that is your job. ;-) This is one of the reasons why
we only use autogrow as a sort of backup plan (don't page me while I'm sleeping).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000 SP4
> On an underpowered machine (mostly an old RAID 5 controller/drives), hosting Visnetic MailFlow we
> had a .LDF file that was 10 GB (already dealt with). When the .LDF file filled, SQL Server did a
> 10% expansion and the entire server locked up for several minutes.
> Is it normal for the system to refuse connections when expanding the .LDF file? My guess is yes,
> because it has nowhere to put log entries (though I would have hoped it would do the expansion
> before it filled, thus allowing continued use).
> How about when the .MDF/.NDF is expanded?
> Thanks,
> Jay
>|||That makes sense Tibor, thanks.
To everyone else, thanks for your replies, but I already dealt with the
super-sized file before the post, hence the statement "(already dealt
with)". The question was to verify why the server locked up during the
expansion. As to how it happened, I'm being handed production database
servers one-by-one and would have dealt with something as basic as a huge
logfie withing a day, if not hours, of getting a server.
Thanks,
Jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:0E2F49FB-37F3-4497-9CB3-F8886A294B98@.microsoft.com...
> Jay,
> I wouldn't expect SQL Server to to refuse connections. But perhaps this
> was perceived so, because the app tries to do some modification when it
> connects. Even the smallest modifications would require a log records and
> if the log is full, well...
> No, database files are not expended in advance, that is your job. ;-) This
> is one of the reasons why we only use autogrow as a sort of backup plan
> (don't page me while I'm sleeping).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> SQL Server 2000 SP4
>> On an underpowered machine (mostly an old RAID 5 controller/drives),
>> hosting Visnetic MailFlow we had a .LDF file that was 10 GB (already
>> dealt with). When the .LDF file filled, SQL Server did a 10% expansion
>> and the entire server locked up for several minutes.
>> Is it normal for the system to refuse connections when expanding the .LDF
>> file? My guess is yes, because it has nowhere to put log entries (though
>> I would have hoped it would do the expansion before it filled, thus
>> allowing continued use).
>> How about when the .MDF/.NDF is expanded?
>> Thanks,
>> Jay
>>
>
On an underpowered machine (mostly an old RAID 5 controller/drives), hosting
Visnetic MailFlow we had a .LDF file that was 10 GB (already dealt with).
When the .LDF file filled, SQL Server did a 10% expansion and the entire
server locked up for several minutes.
Is it normal for the system to refuse connections when expanding the .LDF
file? My guess is yes, because it has nowhere to put log entries (though I
would have hoped it would do the expansion before it filled, thus allowing
continued use).
How about when the .MDF/.NDF is expanded?
Thanks,
JayA 10 gig TLog? Wow :)
Is not it too big mate? You have an issue which is more important than
"expanding" thing... You better take a look at the following documentation:
Optimizing Transaction Log Performance
http://msdn2.microsoft.com/en-us/library/aa178420(SQL.80).aspx
Transaction Logs
http://msdn2.microsoft.com/en-us/library/aa933065(SQL.80).aspx
Managing the Transaction Log (Applies to SQL Server 2005, however, it's
gonna give you precious ideas I believe)
http://msdn2.microsoft.com/en-us/library/ms345382.aspx
--
Ekrem Önsoy
"Jay" <nospan@.nospam.org> wrote in message
news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000 SP4
> On an underpowered machine (mostly an old RAID 5 controller/drives),
> hosting Visnetic MailFlow we had a .LDF file that was 10 GB (already dealt
> with). When the .LDF file filled, SQL Server did a 10% expansion and the
> entire server locked up for several minutes.
> Is it normal for the system to refuse connections when expanding the .LDF
> file? My guess is yes, because it has nowhere to put log entries (though I
> would have hoped it would do the expansion before it filled, thus allowing
> continued use).
> How about when the .MDF/.NDF is expanded?
> Thanks,
> Jay
>|||Sounds like you have Full recovery mode enabled and you are not backing up
your tlog. Common mistake.
You should size tlog appropriately for your workload, data size and backup
plan. It should NEVER grow automatically - you should control this if
needed during periods of minimal activity. Autogrowth should only fire if
something unexpected happens.
"Jay" <nospan@.nospam.org> wrote in message
news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000 SP4
> On an underpowered machine (mostly an old RAID 5 controller/drives),
> hosting Visnetic MailFlow we had a .LDF file that was 10 GB (already dealt
> with). When the .LDF file filled, SQL Server did a 10% expansion and the
> entire server locked up for several minutes.
> Is it normal for the system to refuse connections when expanding the .LDF
> file? My guess is yes, because it has nowhere to put log entries (though I
> would have hoped it would do the expansion before it filled, thus allowing
> continued use).
> How about when the .MDF/.NDF is expanded?
> Thanks,
> Jay
>|||Jay,
I wouldn't expect SQL Server to to refuse connections. But perhaps this was perceived so, because
the app tries to do some modification when it connects. Even the smallest modifications would
require a log records and if the log is full, well...
No, database files are not expended in advance, that is your job. ;-) This is one of the reasons why
we only use autogrow as a sort of backup plan (don't page me while I'm sleeping).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000 SP4
> On an underpowered machine (mostly an old RAID 5 controller/drives), hosting Visnetic MailFlow we
> had a .LDF file that was 10 GB (already dealt with). When the .LDF file filled, SQL Server did a
> 10% expansion and the entire server locked up for several minutes.
> Is it normal for the system to refuse connections when expanding the .LDF file? My guess is yes,
> because it has nowhere to put log entries (though I would have hoped it would do the expansion
> before it filled, thus allowing continued use).
> How about when the .MDF/.NDF is expanded?
> Thanks,
> Jay
>|||That makes sense Tibor, thanks.
To everyone else, thanks for your replies, but I already dealt with the
super-sized file before the post, hence the statement "(already dealt
with)". The question was to verify why the server locked up during the
expansion. As to how it happened, I'm being handed production database
servers one-by-one and would have dealt with something as basic as a huge
logfie withing a day, if not hours, of getting a server.
Thanks,
Jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:0E2F49FB-37F3-4497-9CB3-F8886A294B98@.microsoft.com...
> Jay,
> I wouldn't expect SQL Server to to refuse connections. But perhaps this
> was perceived so, because the app tries to do some modification when it
> connects. Even the smallest modifications would require a log records and
> if the log is full, well...
> No, database files are not expended in advance, that is your job. ;-) This
> is one of the reasons why we only use autogrow as a sort of backup plan
> (don't page me while I'm sleeping).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> SQL Server 2000 SP4
>> On an underpowered machine (mostly an old RAID 5 controller/drives),
>> hosting Visnetic MailFlow we had a .LDF file that was 10 GB (already
>> dealt with). When the .LDF file filled, SQL Server did a 10% expansion
>> and the entire server locked up for several minutes.
>> Is it normal for the system to refuse connections when expanding the .LDF
>> file? My guess is yes, because it has nowhere to put log entries (though
>> I would have hoped it would do the expansion before it filled, thus
>> allowing continued use).
>> How about when the .MDF/.NDF is expanded?
>> Thanks,
>> Jay
>>
>
Wednesday, March 7, 2012
Automated Backup and restoring to a new machine
Hi all,
I am not sure if this is the correct place to ask this quesion. The problem
i am trying to solve is basically this. We do a nightly complete backup of
our database. The next day we copy the database to a different machine
restore it and run jobs on this database. I would like to automate this
process, but don't know the best solution.
We have 3 machines here. Server A is the main producation db machine, server
B is our backup machine and server C is where we restore the backed up DB and
run the jobs. Can someone please suggest what in theri opinion would be a
good solution for this. Thanks
Poorav
You might want to investigate log shipping for this functionality which
requires Enterprise Edition. However, you can use scripts off the Resource
Pack or off some internet sites to achieve the same effect if you only have
Standard Edition. The principle is not difficult - you have a backup job on
the principal server and you need a share to copy files to and a job on the
standby server to restore the database and subsequent log files('WITH NO
RECOVERY').
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I am not sure if this is the correct place to ask this quesion. The problem
i am trying to solve is basically this. We do a nightly complete backup of
our database. The next day we copy the database to a different machine
restore it and run jobs on this database. I would like to automate this
process, but don't know the best solution.
We have 3 machines here. Server A is the main producation db machine, server
B is our backup machine and server C is where we restore the backed up DB and
run the jobs. Can someone please suggest what in theri opinion would be a
good solution for this. Thanks
Poorav
You might want to investigate log shipping for this functionality which
requires Enterprise Edition. However, you can use scripts off the Resource
Pack or off some internet sites to achieve the same effect if you only have
Standard Edition. The principle is not difficult - you have a backup job on
the principal server and you need a share to copy files to and a job on the
standby server to restore the database and subsequent log files('WITH NO
RECOVERY').
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Subscribe to:
Posts (Atom)