Backward Compatiblity of Management Studio

I'm relatively new to SQL Server. I have spent some time in Enterprise Manager for our SQL Server 2000 instances. We are testing SQL Server 2005, and I found that I was able to register 2000 databases into the new SQL Server 2005 Management Studio.

My question: Why not switch immediately to SQL Server 2005 Management Studio for managing all of our old 2000 instances, as well as the new 2005 instances are they are built/upgraded/migrated?

You can certainly do that, but not everything works. For one thing, the "reports" tab in SSMS won't work against SQL 2K, because 2K doesn't contain any of the dynamic management views to make it work. SSMS also can't edit the DTS packages in SQ, or work with some of the maintance plans.

If all that's OK, by all means go ahead and use it.

Buck Woody

Backward Compatiblity of Management Studio

I'm relatively new to SQL Server. I have spent some time in Enterprise Manager for our SQL Server 2000 instances. We are testing SQL Server 2005, and I found that I was able to register 2000 databases into the new SQL Server 2005 Management Studio.

My question: Why not switch immediately to SQL Server 2005 Management Studio for managing all of our old 2000 instances, as well as the new 2005 instances are they are built/upgraded/migrated?

You can certainly do that, but not everything works. For one thing, the "reports" tab in SSMS won't work against SQL 2K, because 2K doesn't contain any of the dynamic management views to make it work. SSMS also can't edit the DTS packages in SQ, or work with some of the maintance plans.

If all that's OK, by all means go ahead and use it.

Buck Woody

backups and restore

I am backing up SQL databases two ways, once through the Enterprise Manager
and also with Veritas. I need to do a test restore on a database and am
uncertain of the best way to go about it. Is it easier to restore from
Veritas or SQL backups? How do I do it without overwriting the existing
database? Any insights or help would be greatly appreciated.
Here is some info:
Windows 2003 Server
SQL 2000
Veritas 9.1
Thanks"NOBI" schrieb:
> I am backing up SQL databases two ways, once through the Enterprise Manager
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks
During the restore process you can define the name of the restored database.
There is no problem with overwriting the existing one (if you just choose a
different name). Don't forget to use the 'move ... to ...' option to give the
files a different location from the original!|||Hi Nobi,
You can use the RESTORE DATABASE command from query analyzer to restore the
database with a new name.
STEPS:-
1. Identify the logical name associated with MDf AND LDF using RESTORE
FILELISTONLY (see books online)
2. Use the RESTORE DATABASE command with MOVE options (see books online) to
create and restore the backupfile with new
database name. In the MOVE option for the physical name give a new name.
Thanks
Hari
SQL Server MVP
"NOBI" <NOBI@.discussions.microsoft.com> wrote in message
news:8AAA6DF0-3FCA-4F3B-9BFE-6FEA042E28EB@.microsoft.com...
>I am backing up SQL databases two ways, once through the Enterprise Manager
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks|||For full backups you shouldn't have any problems. But be carefull with
Transaction Log backups. If you run TLog backups from both sides (SQL Server
and Veritas) it will be very difficult, almost impossible to restore a DB to
the Point of Failure. TLog backups have a sequence number used to know in
what order they have to be restored after the full backup. Running TLog
backups from both tools will assign a sequence number for every backup
created, then you will have something like this:
FULL BACKUP
SQLTLog(1)
SQLTLog(2)
VERITASTLog(3)
VERITASTLog(4)
SQLTLog(5)
VERITASTLog(6)
Restoring from Full Backup up to the point 6 would require SQL and VERITAS
TLogs. This can be very difficult to achieve depending on the frequency of
TLog backups.
Hope it helps
Lionel Chacon
"NOBI" wrote:
> I am backing up SQL databases two ways, once through the Enterprise Manager
> and also with Veritas. I need to do a test restore on a database and am
> uncertain of the best way to go about it. Is it easier to restore from
> Veritas or SQL backups? How do I do it without overwriting the existing
> database? Any insights or help would be greatly appreciated.
> Here is some info:
> Windows 2003 Server
> SQL 2000
> Veritas 9.1
> Thanks

backup\restore password?

How and where in the GUI (Enterprise Manager) can I specify a password for
restoring databases?
Like this:
1. Create db backup job
2. set pwd
3. restore db, by specifying the pwd set in step 2
Please, if possible, show me a screen shot.
Thanks!
MagnusYou can't do it through Enterprise Manager. You need to use
T-SQL backup, restore commands. Or use SQL-DMO which also
exposes the password property through the Backup2 object
(MediaPassword property).
-Sue
On Tue, 2 May 2006 15:57:37 +0300, "Magnus sterberg"
<magnus_osterberg@.hotmail.com> wrote:

>How and where in the GUI (Enterprise Manager) can I specify a password for
>restoring databases?
>Like this:
>1. Create db backup job
>2. set pwd
>3. restore db, by specifying the pwd set in step 2
>Please, if possible, show me a screen shot.
>Thanks!
>Magnus
>|||In QA, you can make a T-SQL using password.
BACKUP DATABASE Northwind TO MyNwind_1
WITH PASSWORD='password'
RESTORE DATABASE MyNwind FROM MyNwind_1
WITH PASSWORD='password'
"Magnus ?sterberg"?? ??? ??:

> How and where in the GUI (Enterprise Manager) can I specify a password for
> restoring databases?
> Like this:
> 1. Create db backup job
> 2. set pwd
> 3. restore db, by specifying the pwd set in step 2
> Please, if possible, show me a screen shot.
> Thanks!
> Magnus
>
>|||OK, now I found out how to add this to my SQL Server Agent backup job. But..
.the pwd is stored as a part of the SQL statement in plain text. Any sa-user
can easily check up the pwd by opening the job.
Mayby one can also schedule the job by T-SQL? As in:
BACKUP DATABASE [ABC_XZY] TO DISK = N'C:\ABCDB_BACKUPS\ABC_XZY' WITH I
NIT , NOUNLOAD , NAME = N'ABC_XZY', NOSKIP , STATS = 10, NOFORMAT, PASS
WORD='sjkf7wed8', SCHEDULE=daily, 21:00
...sounds a bit far-fetched. But my idea is that I could execute the T-SQL
statement once, which should store the job in the DBMSS, without allowing an
yone to see the pwd. Possible?
Mayby I have to take care of the scheduling myself?
Magnus
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message news:fjre5254k8q0e06t68tnmrhthsigu70
57u@.4ax.com...
> You can't do it through Enterprise Manager. You need to use
> T-SQL backup, restore commands. Or use SQL-DMO which also
> exposes the password property through the Backup2 object
> (MediaPassword property).
>
> -Sue
>
> On Tue, 2 May 2006 15:57:37 +0300, "Magnus sterberg"
> <magnus_osterberg@.hotmail.com> wrote:
>
>|||anyone got an answer to this one'
"Magnus sterberg" <magnus_osterberg@.hotmail.com> wrote in message news:u2XH
eGobGHA.4040@.TK2MSFTNGP02.phx.gbl...
OK, now I found out how to add this to my SQL Server Agent backup job. But..
.the pwd is stored as a part of the SQL statement in plain text. Any sa-user
can easily check up the pwd by opening the job.
Mayby one can also schedule the job by T-SQL? As in:
BACKUP DATABASE [ABC_XZY] TO DISK = N'C:\ABCDB_BACKUPS\ABC_XZY' WITH I
NIT , NOUNLOAD , NAME = N'ABC_XZY', NOSKIP , STATS = 10, NOFORMAT, PASS
WORD='sjkf7wed8', SCHEDULE=daily, 21:00
..sounds a bit far-fetched. But my idea is that I could execute the T-SQL s
tatement once, which should store the job in the DBMSS, without allowing any
one to see the pwd. Possible?
Mayby I have to take care of the scheduling myself?
Magnus
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message news:fjre5254k8q0e06t68tnmrhthsigu70
57u@.4ax.com...
> You can't do it through Enterprise Manager. You need to use
> T-SQL backup, restore commands. Or use SQL-DMO which also
> exposes the password property through the Backup2 object
> (MediaPassword property).
>
> -Sue
>
> On Tue, 2 May 2006 15:57:37 +0300, "Magnus sterberg"
> <magnus_osterberg@.hotmail.com> wrote:
>
>|||Can't do that. Even if you could execute a statement once and have it
lie dormant inside the DBMS somehow, a sysadmin can still look in the
proc cache and see SQL statements & also at the current processes to see
what statements are running (including any sensitive information
included in the statement batch).
The closest you could come to that with pure SQL (and not some 3rd party
tool) would be to write a stored proc that contains an infinite WHILE
loop (eg. "WHILE 1=1") and in the loop you'd have the backup statement
and a "WAITFOR TIME '2:00:00'" statement (see BOL), which would put the
thread to sleep until 2am for example at which time the thread would
wake up, do the backup, loop and go to sleep again. Then you'd get SQL
Server to run the proc on start up (see sp_procoption in BOL) and you
could create the proc with the WITH ENCRYPTION option to make it a
little harder to see the password embedded in the proc. However, a
sysadmin would still be able to see that T-SQL code by querying the
appropriate system metadata (proc cache and/or current processes).
What's the point of password protecting a backup so that the sysadmins
on the box cannot restore that database when they can see the real
online live version of the DB anyway? Seems like not a very credible
situation. Why would you make a login a sysadmin if they're not allowed
to see the data in one of the databases? Isn't that the point of NDAs
(non-disclosure agreements) for DBAs?
*mike hodgson*
http://sqlnerd.blogspot.com
Magnus sterberg wrote:

> anyone got an answer to this one'
>
> "Magnus sterberg" <magnus_osterberg@.hotmail.com
> <mailto:magnus_osterberg@.hotmail.com>> wrote in message
> news:u2XHeGobGHA.4040@.TK2MSFTNGP02.phx.gbl...
> OK, now I found out how to add this to my SQL Server Agent backup
> job. But...the pwd is stored as a part of the SQL statement in
> plain text. Any sa-user can easily check up the pwd by opening the
> job.
> Mayby one can also schedule the job by T-SQL? As in:
> BACKUP DATABASE [ABC_XZY] TO DISK = N'C:\ABCDB_BACKUPS\ABC_XZY'
> WITH INIT , NOUNLOAD , NAME = N'ABC_XZY', NOSKIP , STATS =
> 10, NOFORMAT, PASSWORD='sjkf7wed8', *SCHEDULE=daily, 21:00*
> **
> ...sounds a bit far-fetched. But my idea is that I could execute
> the T-SQL statement once, which should store the job in the DBMSS,
> without allowing anyone to see the pwd. Possible?
> Mayby I have to take care of the scheduling myself?
>
> Magnus
> "Sue Hoegemeier" <Sue_H@.nomail.please
> <mailto:Sue_H@.nomail.please>> wrote in message
> news:fjre5254k8q0e06t68tnmrhthsigu7057u@.
4ax.com...
> <mailto:magnus_osterberg@.hotmail.com>> wrote:
> password for
>

Backup/restore just stored procedures

How can I backup and restore just stored procedures. I know in the old
Enterprise Manager it was easy to script just all stored procedures so I
could do it that way but in the new Management Studio Stored Procedures are
now one of the object types that can be selected.
Any ways to get this done?
Regards,
Gary Blakely
You can script sps in SSMS as well. I would look again.
Andrew J. Kelly SQL MVP
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>
|||Hello,
In SSMS, expand the databases, right click above the database name..
Tasks -- Choose Generate SQL Script and follow based on the wizard.
Thanks
Hari
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>
|||Use F7 maybe when in the stored procedures folder? The right pain will then
be a selectable list of all sprocs that you can generate code for.
TheSQLGuru
President
Indicium Resources, Inc.
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>
|||F7. Yes. that's it.
Thanks
Gary
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OL5V$5CrHHA.4180@.TK2MSFTNGP04.phx.gbl...
> Use F7 maybe when in the stored procedures folder? The right pain will
> then be a selectable list of all sprocs that you can generate code for.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
>

Backup/restore just stored procedures

How can I backup and restore just stored procedures. I know in the old
Enterprise Manager it was easy to script just all stored procedures so I
could do it that way but in the new Management Studio Stored Procedures are
now one of the object types that can be selected.
Any ways to get this done?
Regards,
Gary BlakelyYou can script sps in SSMS as well. I would look again.
Andrew J. Kelly SQL MVP
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||Hello,
In SSMS, expand the databases, right click above the database name..
Tasks -- Choose Generate SQL Script and follow based on the wizard.
Thanks
Hari
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||Use F7 maybe when in the stored procedures folder? The right pain will then
be a selectable list of all sprocs that you can generate code for.
TheSQLGuru
President
Indicium Resources, Inc.
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||F7. Yes. that's it.
Thanks
Gary
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OL5V$5CrHHA.4180@.TK2MSFTNGP04.phx.gbl...
> Use F7 maybe when in the stored procedures folder? The right pain will
> then be a selectable list of all sprocs that you can generate code for.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
>

Backup/restore just stored procedures

How can I backup and restore just stored procedures. I know in the old
Enterprise Manager it was easy to script just all stored procedures so I
could do it that way but in the new Management Studio Stored Procedures are
now one of the object types that can be selected.
Any ways to get this done?
--
Regards,
Gary BlakelyYou can script sps in SSMS as well. I would look again.
--
Andrew J. Kelly SQL MVP
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||Hello,
In SSMS, expand the databases, right click above the database name..
Tasks -- Choose Generate SQL Script and follow based on the wizard.
Thanks
Hari
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||Use F7 maybe when in the stored procedures folder? The right pain will then
be a selectable list of all sprocs that you can generate code for.
--
TheSQLGuru
President
Indicium Resources, Inc.
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
> How can I backup and restore just stored procedures. I know in the old
> Enterprise Manager it was easy to script just all stored procedures so I
> could do it that way but in the new Management Studio Stored Procedures
> are now one of the object types that can be selected.
> Any ways to get this done?
> --
> Regards,
> Gary Blakely
>|||F7. Yes. that's it.
Thanks
Gary
--
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:OL5V$5CrHHA.4180@.TK2MSFTNGP04.phx.gbl...
> Use F7 maybe when in the stored procedures folder? The right pain will
> then be a selectable list of all sprocs that you can generate code for.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:%23YeDZj4qHHA.4132@.TK2MSFTNGP05.phx.gbl...
>> How can I backup and restore just stored procedures. I know in the old
>> Enterprise Manager it was easy to script just all stored procedures so I
>> could do it that way but in the new Management Studio Stored Procedures
>> are now one of the object types that can be selected.
>> Any ways to get this done?
>> --
>> Regards,
>> Gary Blakely
>>
>

Backup Transaction Log on TempDb (No items for Databases in EM)

I cant see any items in Enterprise manager under databases so I need to do
this from Query Analyzer. Please help on backing up Transaction log.
"Tibor Karaszi" wrote:

> No can do. Make sure that tempdb is still in simple recovery mode. If it i
s, you can have long
> running transactions, or some humongous modifications requiring lots of lo
g space in tempdb. Short
> term: make sure you have storage and autogrow is possible, Long term, hunt
down what it is using
> this space and fix the code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
> news:C70FC7C3-E8F8-4DE9-A2DA-42D0B44F1AC1@.microsoft.com...
>
>Again, you can't backup log of tempdb. Please read my prior post for recomme
ndations on how to
handle your situation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:B5B42792-D6F0-4620-88D3-3CF822A28CDB@.microsoft.com...
>I cant see any items in Enterprise manager under databases so I need to do
> this from Query Analyzer. Please help on backing up Transaction log.
> "Tibor Karaszi" wrote:
>|||Hi,
As Tibor mentioned see the recovery model for Tempdb. Exeute the below
command:-
Select DATABASEPROPERTYEX('tempdb',Re_covery )
If the recovery model is not SIMPLE change it to Simple
ALTER DATABASE TEMPDB SET RECOVERY SIMPLE
Thanks
Hari
SQL Server MVP
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:B5B42792-D6F0-4620-88D3-3CF822A28CDB@.microsoft.com...
>I cant see any items in Enterprise manager under databases so I need to do
> this from Query Analyzer. Please help on backing up Transaction log.
> "Tibor Karaszi" wrote:
>

Backup to Storage Server in another domain.

I am attempting to use enterprise manager to backup databases to a
storage server in another domain and was looking for some advice.
Currently the server is running small business server so I cannot
create a trust to the storage servers domain.
I created a mapped drive when logged in as the sql service account but
this did not work.
I was thinking about making a job that used the net use command to
create the drive then running the backup script.
I was wondering if anyone else had a better solution for crossing the
domain security barrier.
Thanks!
Hi
I have my doubt that you have by EM backup database on remote computer.
BACKUP DATABASE db TO DISK =
N'\\ServerName\backup\db.BAK'
exec SRV003.master.dbo.sp_executesql N'BACKUP DATABASE pubs
to DISK =''d:\Program Files\Microsoft SQL
Server\MSSQL$BELLTOWER\BACKUP\testback.bak'''
exec SRV003.master.dbo.sp_executesql
N'BACKUP DATABASE pubs to testback'
<outoftherealm@.hotmail.com> wrote in message
news:1143475970.853389.244620@.u72g2000cwu.googlegr oups.com...
>I am attempting to use enterprise manager to backup databases to a
> storage server in another domain and was looking for some advice.
> Currently the server is running small business server so I cannot
> create a trust to the storage servers domain.
> I created a mapped drive when logged in as the sql service account but
> this did not work.
> I was thinking about making a job that used the net use command to
> create the drive then running the backup script.
> I was wondering if anyone else had a better solution for crossing the
> domain security barrier.
> Thanks!
>
|||Create a share on the remote server and then use unc path for the
destination.
e.g.
backup database <db>
to disk='\\remoteserver\shared\db.bak'
-oj

> <outoftherealm@.hotmail.com> wrote in message
> news:1143475970.853389.244620@.u72g2000cwu.googlegr oups.com...
>
|||If you dont want to create a permanent share then you can use
net use <drive name>: <unc path> user:<user name with doamin> password
ex
net use P: \\10.10.10.10\folder /user:mydomain\username password.
Run this job as first step and if is success take backup to mapped
drive.
Regards
Amish Shah

Backup to Storage Server in another domain.

I am attempting to use enterprise manager to backup databases to a
storage server in another domain and was looking for some advice.
Currently the server is running small business server so I cannot
create a trust to the storage servers domain.
I created a mapped drive when logged in as the sql service account but
this did not work.
I was thinking about making a job that used the net use command to
create the drive then running the backup script.
I was wondering if anyone else had a better solution for crossing the
domain security barrier.
Thanks!Hi
I have my doubt that you have by EM backup database on remote computer.
BACKUP DATABASE db TO DISK =
N'\\ServerName\backup\db.BAK'
exec SRV003.master.dbo.sp_executesql N'BACKUP DATABASE pubs
to DISK =''d:\Program Files\Microsoft SQL
Server\MSSQL$BELLTOWER\BACKUP\testback.bak'''
exec SRV003.master.dbo.sp_executesql
N'BACKUP DATABASE pubs to testback'
<outoftherealm@.hotmail.com> wrote in message
news:1143475970.853389.244620@.u72g2000cwu.googlegroups.com...
>I am attempting to use enterprise manager to backup databases to a
> storage server in another domain and was looking for some advice.
> Currently the server is running small business server so I cannot
> create a trust to the storage servers domain.
> I created a mapped drive when logged in as the sql service account but
> this did not work.
> I was thinking about making a job that used the net use command to
> create the drive then running the backup script.
> I was wondering if anyone else had a better solution for crossing the
> domain security barrier.
> Thanks!
>|||Create a share on the remote server and then use unc path for the
destination.
e.g.
backup database <db>
to disk='\\remoteserver\shared\db.bak'
-oj

> <outoftherealm@.hotmail.com> wrote in message
> news:1143475970.853389.244620@.u72g2000cwu.googlegroups.com...
>|||If you dont want to create a permanent share then you can use
net use <drive name>: <unc path> user:<user name with doamin> password
ex
net use P: \\10.10.10.10\folder /user:mydomain\username password.
Run this job as first step and if is success take backup to mapped
drive.
Regards
Amish Shah|||how to set username and password
when server run with local system service?
mydomain\username and password don't work or
server is in workgroup (not in domain)
Thanks
"amish" <shahamishm@.gmail.com> wrote in message
news:1143559961.804068.209860@.t31g2000cwb.googlegroups.com...
> If you dont want to create a permanent share then you can use
> net use <drive name>: <unc path> user:<user name with doamin> password
> ex
> net use P: \\10.10.10.10\folder /user:mydomain\username password.
> Run this job as first step and if is success take backup to mapped
> drive.
>
> Regards
> Amish Shah
>

Backup to Storage Server in another domain.

I am attempting to use enterprise manager to backup databases to a
storage server in another domain and was looking for some advice.
Currently the server is running small business server so I cannot
create a trust to the storage servers domain.
I created a mapped drive when logged in as the sql service account but
this did not work.
I was thinking about making a job that used the net use command to
create the drive then running the backup script.
I was wondering if anyone else had a better solution for crossing the
domain security barrier.
Thanks!Hi
I have my doubt that you have by EM backup database on remote computer.
BACKUP DATABASE db TO DISK = N'\\ServerName\backup\db.BAK'
--
exec SRV003.master.dbo.sp_executesql N'BACKUP DATABASE pubs
to DISK =''d:\Program Files\Microsoft SQL
Server\MSSQL$BELLTOWER\BACKUP\testback.bak'''
exec SRV003.master.dbo.sp_executesql
N'BACKUP DATABASE pubs to testback'
<outoftherealm@.hotmail.com> wrote in message
news:1143475970.853389.244620@.u72g2000cwu.googlegroups.com...
>I am attempting to use enterprise manager to backup databases to a
> storage server in another domain and was looking for some advice.
> Currently the server is running small business server so I cannot
> create a trust to the storage servers domain.
> I created a mapped drive when logged in as the sql service account but
> this did not work.
> I was thinking about making a job that used the net use command to
> create the drive then running the backup script.
> I was wondering if anyone else had a better solution for crossing the
> domain security barrier.
> Thanks!
>|||Create a share on the remote server and then use unc path for the
destination.
e.g.
backup database <db>
to disk='\\remoteserver\shared\db.bak'
-oj
> <outoftherealm@.hotmail.com> wrote in message
> news:1143475970.853389.244620@.u72g2000cwu.googlegroups.com...
>>I am attempting to use enterprise manager to backup databases to a
>> storage server in another domain and was looking for some advice.
>> Currently the server is running small business server so I cannot
>> create a trust to the storage servers domain.
>> I created a mapped drive when logged in as the sql service account but
>> this did not work.
>> I was thinking about making a job that used the net use command to
>> create the drive then running the backup script.
>> I was wondering if anyone else had a better solution for crossing the
>> domain security barrier.
>> Thanks!
>|||If you dont want to create a permanent share then you can use
net use <drive name>: <unc path> user:<user name with doamin> password
ex
net use P: \\10.10.10.10\folder /user:mydomain\username password.
Run this job as first step and if is success take backup to mapped
drive.
Regards
Amish Shah|||how to set username and password
when server run with local system service?
mydomain\username and password don't work or
server is in workgroup (not in domain)
Thanks
"amish" <shahamishm@.gmail.com> wrote in message
news:1143559961.804068.209860@.t31g2000cwb.googlegroups.com...
> If you dont want to create a permanent share then you can use
> net use <drive name>: <unc path> user:<user name with doamin> password
> ex
> net use P: \\10.10.10.10\folder /user:mydomain\username password.
> Run this job as first step and if is success take backup to mapped
> drive.
>
> Regards
> Amish Shah
>

Backup To Network Share

I would like to backup my databases to a network share (NAS) instead of local disk using Maintenance Plans created by Enterprise Manager. I have successfully used a UNC path to target the destination network share but have not been to figure out how to submit a logon to the network share before the backup is executed.

The SQL Server instance is running in the context of the local system account.

Can I insert a step in the SQL SQL job that is created by the Maintenance Plans that changes the Windows account that the backup runs under? If yes what command syntax would I use in the inserted step or is there another way to accomplish that I'm attempting to do?

thanks
HPTo do this you need the SQL Service to run as a network user account that has pemissions over the share.

That being said it is generally reccomended that you create your backup file locally and then copy it over the network using a DOS batch file that uses copy or some such method.