Basic Mirroring Setup Help

After going through the Enterprise Manage mirroring setup, I get the error message ...

The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)

I have three instances of SQL Server 2005 Enterprise Edtion
MSSQLSERVER (primary) endpoint 5022
MSSQLSERVER (partner) endpoint 5023
MSSQLSERVER (witness) endpoint 5024

I have enabled TCP\IP and named pipes on all three protocols.
In surface configuration I have enabled everything.
Each instance has the -T1400 flag set.
Each instance is running under mixed security.
Each sql Server and sql agent instance runs exactly in the same domain account.
The machine has been rebooted.
The following tests all pass

telnet MYMACH.mynet.net 5022
telnet MYMACH.mynet.net 5023
telnet MYMACH.mynet.net 5024

This problem happens both with Enterprise Edition and Developer Edition.

Any ideas?

AIM

Hi,

Thank you for providing informtaion into the problem that occurred. It is very difficult to diagnose some of these problems with so little information.

My first guess is security, but lets start from the top.

I assume that you have all three instances on the same machine, correct? That should work just fine. And you have setup the endpoints to listen on the different ports, which is correct. Now you said that you can telnet to the different ports, I'm assuming that you did it from the same machine. If you can try the telnet test again from a different machine that would be good, but not neccessary. Should be fine.

If this is the recent SP1 CTP build, http://www.microsoft.com/sql/ctp_sp1.mspx, then you do not need the 1400 trace flag (it is just a noop now). Should be fine.

Now the account that the instances are running under? What type of account is it? Can we make it a Sys Admin account on all three instances? Look in the errorlog for the instances. If you see anything along the lines of "denied access" in there?

Also, one last thing; you state that the error message is:

"The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

If you want the principal to listen on the 5022 port, then you should have gotten an error saying "... MYMACH.mynet.net:5023..." Can you go back and check your setup?

Thanks,

Mark

|||

O.K.

FIRST

All three instances are running under a Windows Account that is an Adminstrator on the machine. This has always been this way.

SECOND

I said:

"The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

I meant to say:

"The server network address "TCP://MYMACH.mynet.net:5023" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

O.K. WHAT NEXT SHOULD I TRY?

THANKS.

AIMDBA

|||

Can you verify that the Windows account that the instances are running as are "SQL Server System Administrators?" Thanks.

If you could attach the error logs during the time that you tried to setup database mirroring, that would help a lot. Grab the error logs from both the principal and mirror instance. By default these are located at:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

Get the "errorlog" that contains the time at which you issued the start mirroring command.

At this point, I see two approaches.

1. posting the errorlogs so that I can look at them.

2. trying to setup mirroring just using t-sql. There are some examples in BOL.

Let me know how things are going.

Thanks,

Mark

|||

I'm getting the same error here. Below is my sql server error (machines are on different IP's) and below this is my error log.

An error occurred while starting mirroring.

ADDITIONAL INFORMATION:

Alter failed for Database 'UOP_GSO'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2040.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The server network address "TCP://BOGEYWRKSTATION.svc.nt.edu:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

2006-04-12 16:48:01.75 spid51 Using 'xpstar90.dll' version '2005.90.2040' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2006-04-12 16:48:02.55 spid12s The Service Broker protocol transport is disabled or not configured.
2006-04-12 16:48:02.57 spid12s Server is listening on [ 'any' <ipv4> 5022].
2006-04-12 16:48:02.57 spid12s The Database Mirroring protocol transport is now listening for connections.
2006-04-12 16:48:02.77 spid12s Service Broker manager has started.
2006-04-12 16:48:04.49 spid5s Recovery is complete. This is an informational message only. No user action is required.

|||

First thing to do is to go to the "Troubleshooting Database Mirroring Setup" topic in BOL. It was designed to solve this first basic question of "why do I get error 1418?"

The snippette of the errorlog that you posted indicates that the database mirroring endpoint was started at one point, but that doesn't tell me much.

If you could post the part of the errorlog around the time you got the 1418 error, that would tell me much more. It is generally one of two things.

1. Network error where we tried to open the port and for some reason the network said we failed.

2. Security problem where we connected to the port but for some reason, our security credentials were not correct.

Thanks,

Mark

|||

Got it up and running. Had nothing to do with the specific error that I was receiving but I had not restored the failover correctly.

thanks

|||

Bogey1,

What did method did you use, (what did you do) to do the correct failover restore?

AIMDBA

|||Two seperate machines on the network, but still in the same domain. Running domain accounts for sql server. I did a backup of my primary and applied that to my mirror, but I kept getting the error my mirror is not in sync. So I just restored my backup to my primary and started the mirroring. Works quite well.|||

So, to initialize the mirror, you must do a full backup of the database on the princpal, restore the full backup to the mirror with NO_RECOVERY, and then usually (there are some cases where you do not have to do another backup or restore, but they are not normal operating scenarios) take a log backup and restore it to the mirror with NO_RECOVERY. You must also restore any other log backups that are taken on the principal to the mirror.

Thanks,

Mark

|||Rather that starting an entirely new thread, I figured I would post my question here since my issues are stemming from the next point of the mirror creation process.

Basically I take my full principal backup and then run the restore with no recovery to the mirror, once the restore is underway and then complete, according to my sql logs, I have the DBname (Restoring) indicator in management studio. Low and behold this message can be there for countless hours and in this scenario the database is unalvailable for use. Any ideas on what could be causing this?

Thanks
|||

This is correct behavior. Doing any type of restore with NO_RECOVERY leaves the database in an unrecovered state.

Continue with setting up database mirroring.

Thanks,

Mark

|||

I'm facing the same issue with error 1418. I have 3 VM servers ( hosted in the same host machine) that host each an instance of SQL server. I'm using a domain account with admin right on each server to run SQL server and my database mirroring. I have granted System admin right to that account and full right on all sql folder. Telnet test is successfull. after the mirroring failed, there is no failure audit in the security audit log. Everything seems to be setup properly. I have added the service account in all group created by SQL Server during the installation. But I'm still receiving this error.

In another network, where the servers are not VM Ware server but real physical machine I do not have any problem The mirroring is working fine. But with the VMWare server , I have the problem. Is this problem linked to the virtual machine architecture? Why I don't have the problem with physical machine?

Basic Mirroring Setup Help

After going through the Enterprise Manage mirroring setup, I get the error message ...

The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)

I have three instances of SQL Server 2005 Enterprise Edtion
MSSQLSERVER (primary) endpoint 5022
MSSQLSERVER (partner) endpoint 5023
MSSQLSERVER (witness) endpoint 5024

I have enabled TCP\IP and named pipes on all three protocols.
In surface configuration I have enabled everything.
Each instance has the -T1400 flag set.
Each instance is running under mixed security.
Each sql Server and sql agent instance runs exactly in the same domain account.
The machine has been rebooted.
The following tests all pass

telnet MYMACH.mynet.net 5022
telnet MYMACH.mynet.net 5023
telnet MYMACH.mynet.net 5024

This problem happens both with Enterprise Edition and Developer Edition.

Any ideas?

AIM

Hi,

Thank you for providing informtaion into the problem that occurred. It is very difficult to diagnose some of these problems with so little information.

My first guess is security, but lets start from the top.

I assume that you have all three instances on the same machine, correct? That should work just fine. And you have setup the endpoints to listen on the different ports, which is correct. Now you said that you can telnet to the different ports, I'm assuming that you did it from the same machine. If you can try the telnet test again from a different machine that would be good, but not neccessary. Should be fine.

If this is the recent SP1 CTP build, http://www.microsoft.com/sql/ctp_sp1.mspx, then you do not need the 1400 trace flag (it is just a noop now). Should be fine.

Now the account that the instances are running under? What type of account is it? Can we make it a Sys Admin account on all three instances? Look in the errorlog for the instances. If you see anything along the lines of "denied access" in there?

Also, one last thing; you state that the error message is:

"The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

If you want the principal to listen on the 5022 port, then you should have gotten an error saying "... MYMACH.mynet.net:5023..." Can you go back and check your setup?

Thanks,

Mark

|||

O.K.

FIRST

All three instances are running under a Windows Account that is an Adminstrator on the machine. This has always been this way.

SECOND

I said:

"The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

I meant to say:

"The server network address "TCP://MYMACH.mynet.net:5023" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

O.K. WHAT NEXT SHOULD I TRY?

THANKS.

AIMDBA

|||

Can you verify that the Windows account that the instances are running as are "SQL Server System Administrators?" Thanks.

If you could attach the error logs during the time that you tried to setup database mirroring, that would help a lot. Grab the error logs from both the principal and mirror instance. By default these are located at:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

Get the "errorlog" that contains the time at which you issued the start mirroring command.

At this point, I see two approaches.

1. posting the errorlogs so that I can look at them.

2. trying to setup mirroring just using t-sql. There are some examples in BOL.

Let me know how things are going.

Thanks,

Mark

|||

I'm getting the same error here. Below is my sql server error (machines are on different IP's) and below this is my error log.

An error occurred while starting mirroring.

ADDITIONAL INFORMATION:

Alter failed for Database 'UOP_GSO'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2040.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The server network address "TCP://BOGEYWRKSTATION.svc.nt.edu:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

2006-04-12 16:48:01.75 spid51 Using 'xpstar90.dll' version '2005.90.2040' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2006-04-12 16:48:02.55 spid12s The Service Broker protocol transport is disabled or not configured.
2006-04-12 16:48:02.57 spid12s Server is listening on [ 'any' <ipv4> 5022].
2006-04-12 16:48:02.57 spid12s The Database Mirroring protocol transport is now listening for connections.
2006-04-12 16:48:02.77 spid12s Service Broker manager has started.
2006-04-12 16:48:04.49 spid5s Recovery is complete. This is an informational message only. No user action is required.

|||

First thing to do is to go to the "Troubleshooting Database Mirroring Setup" topic in BOL. It was designed to solve this first basic question of "why do I get error 1418?"

The snippette of the errorlog that you posted indicates that the database mirroring endpoint was started at one point, but that doesn't tell me much.

If you could post the part of the errorlog around the time you got the 1418 error, that would tell me much more. It is generally one of two things.

1. Network error where we tried to open the port and for some reason the network said we failed.

2. Security problem where we connected to the port but for some reason, our security credentials were not correct.

Thanks,

Mark

|||

Got it up and running. Had nothing to do with the specific error that I was receiving but I had not restored the failover correctly.

thanks

|||

Bogey1,

What did method did you use, (what did you do) to do the correct failover restore?

AIMDBA

|||Two seperate machines on the network, but still in the same domain. Running domain accounts for sql server. I did a backup of my primary and applied that to my mirror, but I kept getting the error my mirror is not in sync. So I just restored my backup to my primary and started the mirroring. Works quite well.|||

So, to initialize the mirror, you must do a full backup of the database on the princpal, restore the full backup to the mirror with NO_RECOVERY, and then usually (there are some cases where you do not have to do another backup or restore, but they are not normal operating scenarios) take a log backup and restore it to the mirror with NO_RECOVERY. You must also restore any other log backups that are taken on the principal to the mirror.

Thanks,

Mark

|||Rather that starting an entirely new thread, I figured I would post my question here since my issues are stemming from the next point of the mirror creation process.

Basically I take my full principal backup and then run the restore with no recovery to the mirror, once the restore is underway and then complete, according to my sql logs, I have the DBname (Restoring) indicator in management studio. Low and behold this message can be there for countless hours and in this scenario the database is unalvailable for use. Any ideas on what could be causing this?

Thanks
|||

This is correct behavior. Doing any type of restore with NO_RECOVERY leaves the database in an unrecovered state.

Continue with setting up database mirroring.

Thanks,

Mark

|||

I'm facing the same issue with error 1418. I have 3 VM servers ( hosted in the same host machine) that host each an instance of SQL server. I'm using a domain account with admin right on each server to run SQL server and my database mirroring. I have granted System admin right to that account and full right on all sql folder. Telnet test is successfull. after the mirroring failed, there is no failure audit in the security audit log. Everything seems to be setup properly. I have added the service account in all group created by SQL Server during the installation. But I'm still receiving this error.

In another network, where the servers are not VM Ware server but real physical machine I do not have any problem The mirroring is working fine. But with the VMWare server , I have the problem. Is this problem linked to the virtual machine architecture? Why I don't have the problem with physical machine?

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

Backward compatiblity in sqlserver 05 Enterprise edition

Hi,
I believe I'm running into a backward compatibility issue with the sqlserver
05 Enterprise edition. I restored a database from sqlserver 2000 to my new
sqlserver 2005. Both of my .net project and Access gets time-out errors
(running remotely) when they try to access to a table that I have modified
via query analyzer in sqlserver 2005. I had no problem reading the table via
those clients before I touched its data, so it looks like modifying data in
those table creating an incompatiblity issue.
Has anybody run into similar situation before? Thank you very much in
advance for your help!Never mind. I had a transaction failed to commit or rollback because of a
fatal error and so my subsequence data modification statements would just
lock up the tables.
"Zen" <zen@.nononospam.com> wrote in message
news:eKrY0y3fGHA.5092@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I believe I'm running into a backward compatibility issue with the
> sqlserver 05 Enterprise edition. I restored a database from sqlserver 2000
> to my new sqlserver 2005. Both of my .net project and Access gets
> time-out errors (running remotely) when they try to access to a table that
> I have modified via query analyzer in sqlserver 2005. I had no problem
> reading the table via those clients before I touched its data, so it looks
> like modifying data in those table creating an incompatiblity issue.
> Has anybody run into similar situation before? Thank you very much in
> advance for your help!
>

Backward compatiblity in sqlserver 05 Enterprise edition

Hi,
I believe I'm running into a backward compatibility issue with the sqlserver
05 Enterprise edition. I restored a database from sqlserver 2000 to my new
sqlserver 2005. Both of my .net project and Access gets time-out errors
(running remotely) when they try to access to a table that I have modified
via query analyzer in sqlserver 2005. I had no problem reading the table via
those clients before I touched its data, so it looks like modifying data in
those table creating an incompatiblity issue.
Has anybody run into similar situation before? Thank you very much in
advance for your help!Never mind. I had a transaction failed to commit or rollback because of a
fatal error and so my subsequence data modification statements would just
lock up the tables.
"Zen" <zen@.nononospam.com> wrote in message
news:eKrY0y3fGHA.5092@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I believe I'm running into a backward compatibility issue with the
> sqlserver 05 Enterprise edition. I restored a database from sqlserver 2000
> to my new sqlserver 2005. Both of my .net project and Access gets
> time-out errors (running remotely) when they try to access to a table that
> I have modified via query analyzer in sqlserver 2005. I had no problem
> reading the table via those clients before I touched its data, so it looks
> like modifying data in those table creating an incompatiblity issue.
> Has anybody run into similar situation before? Thank you very much in
> advance for your help!
>

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 Manage
r
> 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 th
e
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 Serve
r
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 Manage
r
> 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

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

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 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.