is this the best way to backup more than one database

I am setting up a DTS so that we can backup each of the database, is this the best way to back up each of the mdf & ldf files to another server? cheers

EXEC sp_addumpdevice 'disk', 'cc_150_chg', '\\homer\db_backups$\cc_150_chg.mdf'
EXEC sp_addumpdevice 'disk', 'cc_150_chg_admin', '\\homer\db_backups$\cc_150_chg_admin.mdf'
EXEC sp_addumpdevice 'disk', 'cc_150_chg_Log', '\\homer\db_backups$\cc_150_chg_log.ldf'
EXEC sp_addumpdevice 'disk', 'cc_150_chg_admin_log', '\\homer\db_backups$\cc_150_chg_admin.ldf'
EXEC sp_addumpdevice 'disk', 'cc_153', '\\homer\db_backups$\cc_153.mdf'
EXEC sp_addumpdevice 'disk', 'cc_153_log', '\\homer\db_backups$\cc_153.ldf'
EXEC sp_addumpdevice 'disk', 'cc_153_admin', '\\homer\db_backups$\cc_153_admin.mdf'
EXEC

This will form part of the step in 'command line'?

Cheers
EWhy don't you just backup to a file?|||backup all the database to a file? How can I do this? also do i need to specifiy all the databases individually by backuping both mdf & ldf files?|||Backup each database to a separate file. You don't need to back up the log files (ldf) unless you need point-in-time restore.|||You don't need to back up the log files (ldf)

You're fired|||alll sorted!!!!1thanx|||OK Brett, YOU explain the whole concept of log files to him! I got actual work I gotta do today.

...or just point him to Books Online...

:)|||OK Brett, YOU explain the whole concept of log files to him! I got actual work I gotta do today.

...or just point him to Books Online...

:)

Settle down...

Hey, there's a first time for everything, too

OR, I could just post Tara's blog

http://weblogs.sqlteam.com/tarad/category/95.aspx

I think I've stolen most of that...

Let me ask...when was the last time, aside from testing contingency, that you had to do a restore, and have to reapply all of the logs?

I don't remeber...had to be 6.5

Is this feasible?

Hi
We have an access desktop app with front-end/back-end situation where all
tables are in the back end and everything else, forms/queries, are in the
front end. We would like to make a web app to use the same access database
but are worried about access being able to handle web app users. Is it
feasible to move the access backend tables to the SQL Server and link the
sql server tables in the access front end? It will not help access desktop
app as all processing will still be done by access but the web app can
presumably benefit from tables being on the SQL Server? Then over time we
can also re-write the access desktop app to be native sql.
Thanks
RegardsHiya John,
The LAST thing you want to do is put Access as the backend of anything with
more than one or two users. Put your data in SQL Server.
"John" <john@.nospam.infovis.co.uk> wrote in message
news:%23VGkAIZyDHA.3888@.tk2msftngp13.phx.gbl...
> Hi
> We have an access desktop app with front-end/back-end situation where all
> tables are in the back end and everything else, forms/queries, are in the
> front end. We would like to make a web app to use the same access database
> but are worried about access being able to handle web app users. Is it
> feasible to move the access backend tables to the SQL Server and link the
> sql server tables in the access front end? It will not help access desktop
> app as all processing will still be done by access but the web app can
> presumably benefit from tables being on the SQL Server? Then over time we
> can also re-write the access desktop app to be native sql.
> Thanks
> Regards
>|||Put all your data in SQL Server (MSDE) and use stored procedures and views
for extracting data and use Access strictly for the front end of the
application.
Jim
"John" <john@.nospam.infovis.co.uk> wrote in message
news:%23VGkAIZyDHA.3888@.tk2msftngp13.phx.gbl...
> Hi
> We have an access desktop app with front-end/back-end situation where all
> tables are in the back end and everything else, forms/queries, are in the
> front end. We would like to make a web app to use the same access database
> but are worried about access being able to handle web app users. Is it
> feasible to move the access backend tables to the SQL Server and link the
> sql server tables in the access front end? It will not help access desktop
> app as all processing will still be done by access but the web app can
> presumably benefit from tables being on the SQL Server? Then over time we
> can also re-write the access desktop app to be native sql.
> Thanks
> Regards
>|||"William Morris" wrote
> The LAST thing you want to do
> is put Access as the backend of
> anything with more than one or two
> users. Put your data in SQL Server.
Where did you ever get that idea?
There are many factors that enter into multiuser access to Jet databasese,
including the requirements, design, and implementation of the application,
and the hardware, software, and network environments. If all those factors
are near-perfect, we have reliable reports of Access supporting 100+ happy,
concurrent users. Even if all are not near-perfect, we routinely see reports
of split Access-Jet databases supporting 30 to 70 users. We've had whines in
the past about "Access falling over with four users" and, any that we could
get details on turned out that all the factors were about as far from
perfect as possible, but the primary culprit was design by someone who
didn't know what they were doing.
Your user estimate is obviously nearly as low as it could possibly be,
unless some dunderhead claimed Access wouldn't support _any_ users.
And, by the way, "native SQL" does not have a desktop UI capability, just so
you won't make that mistake again.
Larry Linson
Microsoft Access MVP|||What you describe is certainly possible. Access makes a good client
application for server databases on the same LAN or WAN. If the person
needing the rich-client-interface is, however, accessing across the
Internet, you'll want to run Access on the server via some sort of Remote
Access Software (for one user, something like pcAnywhere or ReachOut would
be fine; for multiple users, take a look at Virtual Private Network and
Windows Terminal Server / Citrix).
However, Access itself would not be involved in supporting the web users in
such a situation. You have a Jet database, and you'd access it with either
DAO, or more likely, ADO code from .asp pages. If the database was on the
same machine, you can almost certainly support more concurrent users than
you could with an Access-Jet split database on a LAN. A Jet database can be
quite adequate for a web site with "modest" traffic. See my response to
William Morris for some numbers on concurrent users.
The advice you have received suggesting that you _need_ to convert to SQL
Server, without any indication of the requirements, or the expected number
of concurrent users, is "hasty", to say the least.
"John" <john@.nospam.infovis.co.uk> wrote in message
news:%23VGkAIZyDHA.3888@.tk2msftngp13.phx.gbl...
> Hi
> We have an access desktop app with front-end/back-end situation where all
> tables are in the back end and everything else, forms/queries, are in the
> front end. We would like to make a web app to use the same access database
> but are worried about access being able to handle web app users. Is it
> feasible to move the access backend tables to the SQL Server and link the
> sql server tables in the access front end? It will not help access desktop
> app as all processing will still be done by access but the web app can
> presumably benefit from tables being on the SQL Server? Then over time we
> can also re-write the access desktop app to be native sql.
> Thanks
> Regards
>|||We encountered a similar scenario before. Yes, you can upsize (use the
upsizing utility) the MS-Access database tables to a SQL server and link
them back in the MS-Access database. You may want to check the following in
your front end. (1) AutoNumber (identity) (2) All the queries. There are
some flavors of MS-Access query may not work well with linked SQL server
tables. You may also have to tweak your front end to avoid any performance
issues. You may also want to consider the effort required to do the above
before making the final decision.
"John" <john@.nospam.infovis.co.uk> wrote in message
news:#VGkAIZyDHA.3888@.tk2msftngp13.phx.gbl...
> Hi
> We have an access desktop app with front-end/back-end situation where all
> tables are in the back end and everything else, forms/queries, are in the
> front end. We would like to make a web app to use the same access database
> but are worried about access being able to handle web app users. Is it
> feasible to move the access backend tables to the SQL Server and link the
> sql server tables in the access front end? It will not help access desktop
> app as all processing will still be done by access but the web app can
> presumably benefit from tables being on the SQL Server? Then over time we
> can also re-write the access desktop app to be native sql.
> Thanks
> Regards
>

Is this considered scale-out?

I picked back up an effort to get our reporting services DR scenario in place and am having some issues. Our corporate approach for production databases is to back-up the database to the local server disk, copy the back-up files to a remote office and restore the databases on remote DR servers. This process is scripted to run overnight so in the case of failure of the production environment we lose at most one day of work.

Now, I would like to set up a parallel scenario for Reporting Services and started with reading a bit of documentation. I read "Moving a Report Server Database to Another Computer" and "Backing Up and Restoring Encryption Keys" and then set to work.

I have two instances of SQL Server 2005 Standard installed as the default instance on two servers, PROD and DR. I backed up the encryption key on PROD and restored it on DR using the Reporting Services Configuration Manager. Then I backed up the PROD/ReportServer and PROD/ReportServerTempDB databases and restored the databases to the DR instance. Next, I connected the DR Reporting Services instance to the newly restored databases and clicked 'Apply'.

I get this warning:

"Setting Connection Info for Reporting Server

You specified a connection to a report server database that contains encryption keys for another report server. If you are configuring a scale-out deployment, that feature is not supported by this edition of Reporting Services. If you want to use this report server database with the currecnt report server instance, remove the existing encryption keys first."

And as you would expect navigating to the DR web application tells me "The report server installation is not initialized."

So, a couple questions?

1) Would this be considered a scale-out installation?
2) Did I miss some configuration step?
3) If I want to continue down this DR scenario do I have to upgrade to Enterprise edition?
4) Are there any suggested DR approaches for Reporting Services 2005?

Thanks in advance for you help.

Calvin

It seems you do not need a scale-out installation. The problem is after you set db connection from PROD to DR, their is an extra key in the RS database that makes RS think you are doing scale-out. Check the Keys table in your ReportServer database on DR. There could be two reasons for this to happen:

1) You configured report server on DR to use RS databases , so that the Keys table contains an entry for DR. Basically you need to decide if you want to access the backup RS db from PROD or DR. You can't access RS from both, for it is a scale-out scenario. Remove the entry you don't need.

2) For some reason there was an entry for PROD that is left over in Keys table, and you get 2 PROD entries. This makes the RS thinks you are doing scale-out. Simply remove the one that is invalid.

|||

Thanks for your response.

1) When I click the "Change" button on the Encryption Key tab, I see there are two servers listed. So, I believe there are two keys in the database but I don't believe it is as simple as just removing the key I don't need. I want to access the backup RS database from DR but the DataSource connection strings and such are encrypted using the key from PROD. If not by restoring the key using the Configuration Manager, how can I make this happen? Will Reporting Services throw a fit if I manually update the Keys table in the restored RS database in DR to make it use the encryption key from PROD but the instance information from DR? Is this even possible?

2) I believe the two keys are present because I told DR reporting services to "Connect" to the RS database which contained a key from PROD.

Thanks again,

Calvin

|||

I tried it again this morning and got it working. I believe the sequencing is where I went wrong. I'll have to do some testing to figure out what the correct sequence is but at least now I know it does work.

Thanks again,

Calvin

|||When you backup the key on PROD, the key is decrypted using the machine key of PROD and then encrypted using the password you specify. When you restore the key DR, the key is decyrpted using the password and reencyrpted using the machine key of DR. You see the key is always in encrypted form, and is preserved when you move servers.

Is this a bug in SQL 2000/2005?

Running the script below the error number is not generated and the
transaction is rolled back ...
Should be like this, or it should nicely give the error number and let me
decide what to do?
This is very usefull when using EXECUTE() or sp_executesql().
Here is hwo you can reproduce the problem:
CREATE TABLE xTest (IDCol int, xText varchar(100))
INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
SELECT @.@.ERROR --should be 207
DROP TABLE xTestSome errors terminates the batch. See the error handling articles at www.sommarsko
g.se for details.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Florin Lazar" <FlorinLazar@.discussions.microsoft.com> wrote in message
news:A436FCDD-B959-42C6-965F-4D40B47F6434@.microsoft.com...
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest|||The batch does not compile, and thus it is never executed. So the line
"SELECT @.@.ERROR" will never be executed.
Gert-Jan
Florin Lazar wrote:
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest

Is this a bug in SQL 2000/2005?

Running the script below the error number is not generated and the
transaction is rolled back ...
Should be like this, or it should nicely give the error number and let me
decide what to do?
This is very usefull when using EXECUTE() or sp_executesql().
Here is hwo you can reproduce the problem:
CREATE TABLE xTest (IDCol int, xText varchar(100))
INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
SELECT @.@.ERROR --should be 207
DROP TABLE xTest
Some errors terminates the batch. See the error handling articles at www.sommarskog.se for details.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Florin Lazar" <FlorinLazar@.discussions.microsoft.com> wrote in message
news:A436FCDD-B959-42C6-965F-4D40B47F6434@.microsoft.com...
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest
|||The batch does not compile, and thus it is never executed. So the line
"SELECT @.@.ERROR" will never be executed.
Gert-Jan
Florin Lazar wrote:
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest

Is this a bug in SQL 2000/2005?

Running the script below the error number is not generated and the
transaction is rolled back ...
Should be like this, or it should nicely give the error number and let me
decide what to do?
This is very usefull when using EXECUTE() or sp_executesql().
Here is hwo you can reproduce the problem:
CREATE TABLE xTest (IDCol int, xText varchar(100))
INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
SELECT @.@.ERROR --should be 207
DROP TABLE xTestSome errors terminates the batch. See the error handling articles at www.sommarskog.se for details.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Florin Lazar" <FlorinLazar@.discussions.microsoft.com> wrote in message
news:A436FCDD-B959-42C6-965F-4D40B47F6434@.microsoft.com...
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest|||The batch does not compile, and thus it is never executed. So the line
"SELECT @.@.ERROR" will never be executed.
Gert-Jan
Florin Lazar wrote:
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest

is this a bad idea? (replicating from pub to dist, back to pub)

We need to create a copy of a db with updates on the same publisher.
Problem is that the publisher is already using a separate distributor
server. Since you can only have 1 distributor per publisher we are in a
bind here. We can't change the distributor because it's handling
replication for other publishers on this box. So we were thinking that
we just replicated the db to the publisher via the separate distributor.
Make sense? Is this a bad idea since technically it's leaving the
machine traveling across the network to the distributor then back down
to the exact same machine it came from.
What other solution might we use? They do not want to dts it for some
reason. They want it to replicate. Not sure why
tia
-comb
Comb,
this is a possible topology and I agree with what you're saying re
transactional replication, but merge replication and snapshot will not send
data via the distributor. As alternatives, you could also investigate
database mirroring to a separate instance (+ database snapshot if they want
to run reports) on the same box or a custom log shipping solution. It
depends on the business requirements really.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <OKh7ap1QGHA.3052@.TK2MSFTNGP09.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> Comb,
> this is a possible topology and I agree with what you're saying re
> transactional replication, but merge replication and snapshot will not send
> data via the distributor. As alternatives, you could also investigate
> database mirroring to a separate instance (+ database snapshot if they want
> to run reports) on the same box or a custom log shipping solution. It
> depends on the business requirements really.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Paul
Thanks for your response.
I am going to look into database mirroring and database snapshot right
now. I am not a sql dude (i am a network guy that gets stuck with
messing with replication). For some reason, i was thinking that those
two terms were only part of sql2005. I will do my research though and
make sure.
I guess one other issue is that this db is 300+gb and they only want a
few tables out of it.
thanks again paul.
-comb
|||Comb,
for a custom log shipping solution you'd be ok with sql server 2000. Apart
from that you're right that you'd need sql 2005 for a database mirroring
solution. The point about mirroring is that it is available but not
currently supported, so that might be a factor. if it's a case of just
needing a duplicate of the complete database, I'd script up a solution for
log shipping, or use some of the free scripts out there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <#PB2d75QGHA.4696@.tk2msftngp13.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> Comb,
> for a custom log shipping solution you'd be ok with sql server 2000. Apart
> from that you're right that you'd need sql 2005 for a database mirroring
> solution. The point about mirroring is that it is available but not
> currently supported, so that might be a factor. if it's a case of just
> needing a duplicate of the complete database, I'd script up a solution for
> log shipping, or use some of the free scripts out there.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Paul.
Thanks for your response. You wouldn't happen to have any of those free
scripts on your replicationanswers site would you?
tia
-comb
|||This should help:
http://www.sql-server-performance.co...g_shipping.asp
The other place I've seen them is on the resource kit - which you'll have if
you've got MSDN at work.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <#FkCWncRGHA.4952@.TK2MSFTNGP09.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> This should help:
> http://www.sql-server-performance.co...g_shipping.asp
> The other place I've seen them is on the resource kit - which you'll have if
> you've got MSDN at work.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
thanks again paul.

is there anyway to raise a warning from a UDF (in sql2000 and/or in SQL2005)

cos RAISERROR isnt callable, but I really want to indicate a non-fatal warning to be passed back to the client (C++ code)

You are limited to a single output either way dealing with a scalar or table valued function. The only way you could pass back a message of some sort would be to overwrite the scalar return value or add a column to your table return. Neither of those sound particulary appealing but you have few options.|||In SQL Server 2005, you could replace the TSQL UDF with CLR UDF and raise exception in your CLR code. But this is not a pretty implementation and depending on the TSQL code you will get worse performance. So best is to modify the UDF to a SP or just check for absence of row or special value like NULL.

Is there any way i can Roll back in sql server without using transactions ?

Hi,

i wanna know how to use rollback in sql server without actually using transaction...

is there any way i can do that?

thanx in advance

nope,

rollback will work only when transaction has been issued. Or take a daily backup of database so that you can retrieve it with previous days status

rgds

ramu

|||

::i wanna know how to use rollback in sql server without actually using transaction...

What sense would it make? The ability to separately commit/rollback changes is the definition of a transaction. You with is like drinking water without getting in touch with h20.

If you need transactional control, use a transaction. And rolling back changes you did IS transactional control.

|||

Hey thanks for ur replies.

me gonna implement transactions!!

thanx again

Is there any method to back up the database to place outside of the local server system?

Hi, all experts here,

Thank you very much for your kind attention.

I am wondering if we could back up the databases to any place outside of the local server system? As I found, we can only back up the database to the local server system, so we have needs to share databases on network places. Is there any method to back up the database on network place rather than first of all I have to back up the database on a local server system, then copy it to the network place, that just sounds really inconvenient.

Thanks a lot in advance for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Yes, you can use script

Code Snippet

BACKUP DATABASE [DB_NAME]
TO DISK = '\\FileServer\Backups\file_name.bak'
WITH INIT -- overwrite anything

GO

I think in SQL2005 SP2, you can specify Network Path for backup as well

Otherwise, try Maintainence Plan/Wizard as well (that's what we use)

SSMS -> Management -> create a plan -> Add "Back Up Database Task", and put the network path manually under "Folder" , under "Create a backup file for every database"

|||You'll need to make sure the service that runs mssql has access to the network drive where you are going to be placing the backup files.|||

Hi, thank you all very much for your very kind advices and help. It's been very helpful.

With best regards,

Yours sincerely,

|||

No problem at all, we are all here to help

don't forget to mark the answer to your question, so the thread is considered "resolved"