Why Are Other Servers In Backup Tables?

Sep 14, 2007



I ran the following code on one of our servers and I got some surprising results. Those results included backups for that machine but also showed entries for other servers. I don't understand it! I can understand the results being wrong because I coded something incorrectly, but why would another server be listed in bs.server_Name? It appears that the entries are "correct" in that backups were taken on the other server at the time given, but why is it there??? (I put the code together to list the most recent backup for each database on the instance. I hope it is correct.)

select @@ServerName, bs.server_Name, bs.Database_Name,
bmf.physical_Device_Name,
bs.backup_start_date, bs.backup_finish_date,
Case When bs.backup_start_date < Getdate() - 1 Then 'Stale' Else 'Recent' End as Status,
Case When bs.backup_finish_date is NULL Then 'Failed' Else 'Successful' End as Success
from msdb.dbo.backupset bs
Join msdb.dbo.backupmediafamily bmf on bmf.media_set_id = bs.media_set_id
where type = 'd'
and bs.backup_set_id = (select max (bso.backup_set_id)
from msdb.dbo.backupset bso
where bso.server_name = bs.server_Name
and bso.Database_Name = bs.Database_Name
and bso.Type = 'D')
order by database_name

View 1 Replies


ADVERTISEMENT

Clustered Servers & Backup

Jun 30, 2000

I am trying to install failover support for my sql server 7.0 database on the in clustere server enviorment
Now We have operating system on two clusterd servers but just one common database disk
Now what will happen if i lose server A ( Active ) the server B will take over and use the same database . But what will

happen if database disk crashes IS it possible for me to replicate the data or use disk mirroring , or have one clustered

database server seperate
How does the database server cluster work ?
What kind of replication do i use for my backup support ?
what should be my recovery process ?
how can i use standby option on the database during recovery ?

Please try to reply as soon as possible
if you can also point to some documetation on the net that will also help


Also look at my current structure ( but this should not affect your answer )

Tables

group 1 --- 20 tables ( being replicated to server c )
group 2 ----60 tables

on cluster 1
Server A-- Holds Group 1 ( Transictional replication to server c )


on cluster 2
server B---(Production server ) Group 2 + Group 1

View 1 Replies View Related

Primary&backup Servers

Aug 10, 1999

Hello!
We are planning to build Primary SQL Server 6.5 and Backup SQL Server 6.5.
In case of media failure on primary server what kind of options should
we set if we want all users automatically switch to backup server until recovery process on primary server take place?

Thank you in advance,
Alona

View 2 Replies View Related

Backup Database On Other Servers

Apr 14, 2004

How to backup database on the other servers in the LANS.

View 8 Replies View Related

Use Backup Compression On Few Servers

Jul 26, 2015

I am wanting to use backup compression on a few sql servers (2008R2 and 2012). I have never touched compression before and always just gone with the default.I use Ola Hallengren's scripts to do the backups which, if not specifically specified, will use the server default.Backups (FULL and LOG) have been happening successfully on these servers for years.SQL won't care that the previous backup in the set was uncompressed or that the hourly transaction log backups previously taken were uncompressed?Restore statements (T-SQL) will be identical?From everything I am reading it is simply a case of setting the configuration and acting like nothing changed but I just wanted to be 100% certain.1 of the servers is a SharePoint backend.All of the backup files from all servers are picked up by Commvault backup system.

View 4 Replies View Related

ATM Circuit & Sql 70 Warm Backup Servers

Apr 10, 2000

Hello out there.

I am currently testing the transmission rates of sql server 70 backups over an ATM circuit (T3) from tampa, fl to detroit. We are evaluating what we need to do to relocate our warm backup database servers to our Disaster Recovery site in detroit. I have tried several types of transmissions and are not receiving any transfer rates that will reasonably work with a recovery plan. I have tried copying a file from source to target, I have tried backing up from source to target, I have tried FTP'ing files, and never can achieve higher transmission rate then 1 gig per hour.

I am looking to talk to anyone out there that may have implemented a long distance sql servber DR site or anyone who is thinking about it. Or anyone who has any input on how I might be able to achieve a higher transmission rate over a T3. Or anyone who is using SAN's in both their local and DR site and might be using special synchronizing software. I am currently piloting a Metastore SAN and a Compaq SAN to solve some local disk management problems and eventually transmit data from SAN to SAN for DR.

Please, if you are involved with any of these strategies and would like someone to share your knowledge with or if I can be of help to you by sharing our testing knowledge, please write asap.

Thanks.
Gail Wade
Database Administration
Raymond James Financial
gwade@it.rjf.com

View 3 Replies View Related

How To Copy A Backup File Between Two Servers?

Sep 14, 2004

Hi,

I am creating a job that runs this command:

EXEC xp_cmdshell 'copy f:dados_sqlmssqlackup
ecom_tb_basico.bak \stalingrado_2c$ emp',

The result of this job is:

"Access is denied.
0 file(s) copied.
NULL"

But I am running this command with sa user.....
Wich kind of permission is missing to execute this copy?
When I execute the same command to copy the backup from the server to itself, it works fine!!!!
Does someone have an idea to solve this problem?????

View 3 Replies View Related

Automating A Backup And Restore Across Servers

Jan 10, 2005

Hi,

I have been experiencing a problem with a job I am trying to automate. My team requires two instances of a db on development. Every week, db1 is detached and reattached as db1_copy, and a new copy of db1 from production is copied over. I have used cursors for the dropusers, addlogins, grantdbaccess, addroles, and addrolemembers aspects of the restore process. Also, we use sql authentication and not windows authentication. The issue is that when the agent encounters a minor issue, ie. a login that already exists in that db, or adding a rolemember to a role that is already there, I get an error. I have ensure that at that step, the job simply proceeds because it is a minor step in the process (it happens during the script execution when done manually but I can override this because I am present and just let it run in one step instead of many). Is there any way to do this without adding a multitude of steps?

Thanks,

Maria

View 1 Replies View Related

Backup And Restore From Current Setup To New Servers

Jun 23, 2015

We are told that we cannot get remote access on our DB servers , can access them only via SQL management studio. But we are the one's who will be restoring all our DB's from current setup to new servers.

My Question:

1. Can we restore the DB's directly via SQL Management studio(from other server where management studio is installed) without logging into the DB server.

2. How do we set the mdf/ldf file paths, assuming we have elevated access on the DB. Is this possible?.

Any other blockers we can face?

View 1 Replies View Related

2 Tables From Different Database Servers

Nov 6, 2005

 coycoy wrote:you wanted to join some columns coming from two different tables...right? if that so, use an SQL query. Try using the "inner join" statement.
i wanted to combine the data from two tables. these two tables belong to different servers: table1 is from sql server found in cerebrum station and table2 is from mysql server found in copernicus station. i have this code but the problem is i can only use this code if the two tables belong in the same database server.
</P>
<P>string limitReghrsvalue = "select statuslog.ActId as STATUS_ID,DalsDataNew.ID as MANHOUR_ID,statuslog.ActDate as DATE,statuslog.PrjCode as PROJECT_CODE,statuslog.MapNumber as MAP_NUMBER,statuslog.Activity_Code as ACTIVITY_CODE,DalsDataNew.ActivityMedium as MEDIUM_CODE,statuslog.RegHrs AS REGHOURS,statuslog.OTHrs AS OTHOURS,statuslog.Status AS STATUS,DalsDataNew.Flag,DalsDataNew.Approvedby from statuslog,DalsDataNew where statuslog.PrjCode = DalsDataNew.ProjectCode and statuslog.PIN = DalsDataNew.PIN and statuslog.ActDate = DalsDataNew.Date and statuslog.Activity_Code = DalsDataNew.ActivityCode and statuslog.RegHrs = DalsDataNew.RegHours and statuslog.OTHrs = DalsDataNew.OTHours and statuslog.PIN = 'P120' and statuslog.ActDate &gt;= '"+this.firstdate.Text+"' and statuslog.ActDate &lt;= '"+this.lastdate.Text+"'";</P>
<P>
Sql Server in Cerebrum: database dals
DalsData
ID   |   Date   |   PIN   |   ProjectCode   |   ActivityCode   |    ActivityMedium   |   RegHrs   |   OTHrs   |   Approvedby   |   Flag
123 |9/17/2005| P120|   1234               |         B               |   W(P)                   |   5.50       |      0.00     |         P083        |   1
124 |9/17/2005| P120|   1234               |         I                |   W(PC)                 |   1.50       |      2.25     |                         |  
MySqlServer in Copernicus: database stat
Statuslog
ActID   |   ActDate   |   PIN   |   ProjectCode   |   MapNumber   |   ActivityCode   |   RegHrs   |   OTHrs   |   Status(%)
1         | 2005-9-17   | P120  |       1234           |     map01          |            B            |      5.50     |     0.00    |    100
2         | 2005-9-17   | P120  |        1234          |     map01          |             I            |      1.50     |    2.25     |    75
 
the output in datagrid should be:
ID   |   ActID   |   Date   |   ProjectCode   |   ActivityCode   |   MediumCode   |   MapNumber   |   RegHrs   |   OTHrs   |   Status   |   Approvedby   |   Flag 
123|        1      |9/17/2005|     1234          |         B              |    W(P)               |         map01      |      5.50      |      0.00      |   100    |   P083    |      1
124|        2      |9/17/2005|     1234          |         I              |    W(PC)               |         map01      |      1.50      |      2.25      |   75    |          |      could someone help me how would i do this?

View 5 Replies View Related

How To Use Tables From Two Servers Under The Same Group?

Feb 3, 2005

Hi

I have two servers under the same group in my enterprise manager, I need to run insert query on a table in one server and select into the insert from the other server.
So what is the syntax to do it ?? hope it's possible...

Thanks,

Inon.

View 5 Replies View Related

Join Tables On Different Servers?

Sep 27, 2013

How to join tables on different servers if one server, for instance, SERVER2 required authentication?

View 2 Replies View Related

Compare Tables Of Different Servers

Oct 8, 2014

I have two databases under two different servers.

Server1:
DB1
Table1

Server2:
DB2
Table2

I need to compare Table 1 & 2 using SQL server and how can I achieve this?

View 2 Replies View Related

How To Synchronize 2 Tables On Different Sql Servers

Jul 23, 2005

Hi, anybody can help me.How can i synchronize 2 tables on 2 different sql servers 2000i mean TABLE1(col1, col2, col3, col4) andTABLE1(col1, col2, col3, col4, col5, col6)the first 3 colums are the same in rwo tables.Thanks--Message posted via http://www.sqlmonster.com

View 6 Replies View Related

Tables In Muliple Servers

Jul 20, 2005

What is the best way to use a table located on 1 server on anotherserver. We have an application that needs to use data from 2 separateservers. TIA.TS

View 2 Replies View Related

Transferring Two Tables Between Servers (was Logic)

May 12, 2005

I have 2 tables with the following structure:
CREATE TABLE [dbo].[table1] (
[RID] [int] IDENTITY (1, 1) NOT NULL ,
[RText] [varchar] (400) NULL ,
[DateModified] [datetime] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Table2] (
[GrpRID] [int] IDENTITY (1, 1) NOT NULL ,
[GrpID] [varchar] (10) NOT NULL ,
[RID] [int] NOT NULL ,
[Status] [bit] NULL ,
[SortOrder] [int] NULL ,
[DateModified] [datetime] NULL
) ON [PRIMARY]
GO
I am transfering 2 table between 2 SQL server based on GrpRID from table2.
Since RID is identity in table1 sometimes it is different text for spesific
Rid in second server. Some how I need to get the match the right text from server1
to server2 and if text doesn't exists create a bew entry in table1 with the update to table2
wich should reflect correct RID.

View 1 Replies View Related

Linking Database Tables On Different Servers?

Oct 16, 2015

I have two production servers with two different databases and I was thinking about using Linked Servers, but never did this before.Found this stored procedure

sp_addlinkedserver('servername').Would you just execute this and then run your query after the SP?

View 8 Replies View Related

Moving Data Tables Between SQL Servers

Jul 20, 2005

I have user that we just migrated his Access database to SQLServer. All went well with the migration, but then he came up withanother requirement to be able to replicate the database to a localSQL server living on the hard drive of a laptop. Before the migrationhe just copied the entire Access databse to the lap top.I tried using the Copy SQL Server Objects Task to move thenecessary tables from the production server to the laptop, but noticedit doesn't copy over the table Indexes/keys identiy fields etc. Iended up backing up the production database and restoring it to thelaptop database, but wondered if there is any way to move the tables,with their properties from one server to another? I know I can setup the backup process to run as scheduled, but the problem is the dataneeds to be moved on an irregular time table. I thought about justwriting code on the remaining Access front end to empty the localtables and then query the data from the production side to reloadthem, but I'm sure there's an easier way.Any suggestions would be appreciated.Thanks,Tom

View 1 Replies View Related

Syncronising SQL Tables Between Seperate Servers

Jul 20, 2005

Does anyone have any idea how to syncronise two SQL identical tables acrossa network, between servers?We want to run our website from a SQL tables on the same server, but set upso that if the main SQL tables across the network change, or the tables onthe web server change, the two seperate servers willsyncronise/update/trigger updates to the tables on each other?We are running SQL server 2000.AHAJohn

View 2 Replies View Related

How To Compare Columns Of Two Tables In Two Different Servers

Mar 18, 2008



I have two table with the same name that reside in different databases. The two database have the same name, but reside in two linked servers
TABLE A: [ServerA].[ProdDB].dbo.[Orders]
TABLE B: [ServerB].[ProdDB].dbo.[Orders]


How do i find out if the two tables have the same number of columns or if the a column that exists on TableA does not exist on TableB.

Does any one have a script that could help me with this task. Thanks

I am using SQL Server 2005

View 8 Replies View Related

Comparing Tables In Different Servers (EXCEPT/INTERSECT)

Nov 14, 2007

Hi people!!!

First I'll introduce my situation.

I have the folowing tables:

1.- Table "Codes", DataBase "COD", server 1001

2.- Table "Codes", DataBase "COD", server 1002

Both servers run SQL Server 2000 Edition.

What i need to perform is a check that compares the data stored in both tables in order to know if there is any difference between them. Of course, the structure of both tables are the same.

I use the SP sp_addlinkedserver to link the servers, but the problem is that EXCEPT and INTERSECT didn't work.

Anyone can help me?

Thanks,

Bob

P.S.: Please!!!!!!!!!

View 6 Replies View Related

How To Query Two Tables In Two Different Servers In SSIS

May 21, 2008



Hi All,

I am trying to design a package that needs to compare two tables in two diiferent servers. Basically I need to insert records into one server by comparing existing records with second table in other server. Is ther any way I can do it with out using Linked server?. Both tables have same structure.

Hope any one will reply soon.

Thanks,
lmp

View 7 Replies View Related

Question About Linked Servers And Access Tables

Jan 7, 2005

Having a little problem with linking to Access tables. I have an Access database that has in it, some linked tables. After setting up the linked server in SQL server, none of the linked tables in Access show up, only the tables that were created or imported show up.

Is there anything you can do to get the linked tables in Access to show up under the tables list in the SQL linked server?

View 3 Replies View Related

SELECT Rowguidcol From Tables On Linked Servers?

Apr 25, 2008

I've run into the following problem using "select rowguidcol from [LinkedServer].[DBName].dbo.MyTable" (as opposed to using "select <rowguid column name>...".

Against a locally-connected database, the following SQL command works as expected:

SELECT rowguidcol from dbo.MyTable

However, If I run the same query against a linked server (using the 4-part notation), it fails with an 'Invalid column name 'rowguidcol'' error:

SELECT rowguidcol from [LinkedServer].[MyDB].dbo.MyTable

Msg 207, Level 16, State 1, Line 1
Invalid column name 'rowguidcol'.

I've searched around but can't seem to find any info that tells me definitively that I can't use rowguidcol against linked tables, so I'm wondering if I'm just doing something wrong?

Any ideas on how to get around this issue would be greatly appreciated. I really don't want to use actual column names, as this is for generated scripts.

More Info:
> I can select the data just fine from the linked server if I use the column name - so I don't think it's a configuration or permissions issue.

> The row I'm trying to view is, in fact, a rowguidcol. I used the exact same script to create the table on both the local (where select rowguidcol works) and the linked (where it doesn't) servers.

> Both servers are MS SQL 2005 Standard with SP2

View 1 Replies View Related

SQL Script To Compare Two Identical Tables On Two SQL Servers

May 23, 2007

Hi all,
I have two tables A and B on two both tables have similar architectures bout contain some deferent records.

I like to compare them and find and view the differences in records.


Thanks for any help.

Abrahim

View 3 Replies View Related

Fastest Way To Copy Tables And Their Indexes Between Servers?

Nov 13, 2006

The DTS Task Copy Server Objects is PAINFULLY slow.

The Copy Table Wizard is fast but generates an unmanagable DTS and does not bring over the indexes.

Any tips or tricks to copy tables, data and indexes and a reasonable speed?

Thanks,

Carl

View 1 Replies View Related

Noob Question Of How To Combine Two Tables From Two Different Servers.

Oct 11, 2006

I got two tables from 2 different server A and B.

I do a OLEDB source [server A] with "select ID, currencyNo, exchangerate from table A"

I do another OLEDB [ server B] source with "select currencyNo, currencyName from table B"



i want to combine these two OLEDB sources with a resultset



"select ID, currencyNo, currencyName, exchangerate from table A , B

where A.currencyNo = B.CurrencyNo"

how do i do this in SSIS? sorry if i m a noob. I dun want to use linked servers. can someone help?

View 7 Replies View Related

Error 0x80004005 When Copying Tables Between Servers

Feb 23, 2007

hi
I've got a job which copy tables between different servers .
I am feeding the tables one by one and the process of copying is in a loop so I have cotrol over the copying process.
it works fine but sometimes randomly I am getting
Execution failed with the following error: "ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".

and the process fails and this might happen for in any point and on any table and sometimes it runs all the way successfully!!
any idea what the problem might be!
Thanks

View 5 Replies View Related

Query Error: Collation Conflict (on Tables On 2 Different Servers)

Jun 30, 2005

Hi all,



I have a query that I need to run where I join two tables that both
reside on different servers. I use an INNER JOIN statement to attempt
to join these tables, but for some reason I am getting the following
error message...

"

Cannot resolve collation conflict for equal to operation."


The query is as follows...

SELECT    TABLE_NAME, LEFT(TABLE_NAME, CHARINDEX('_', TABLE_NAME + '_') - 1) AS Abbreviation
FROM     mrcsmis.INFORMATION_SCHEMA.TABLES
INNER JOIN DEVELCAD1.Portal.dbo.dnl_db_names_log AS imp ON (imp.dnl_table_name = TABLE_NAME)
WHERE     (TABLE_TYPE = 'BASE TABLE')

Thanks

Tryst

View 3 Replies View Related

DTS - Primary Keys Drop When Transferring Tables Between Servers

Sep 7, 2000

Hello All,

Has any1 noticed that when they are transferring SQL tables from one server (or machine) to another that the primary keys drop from the table (or is it just me). If so, has someone figured out why? and how to rectify this (apparent) error.

Many thanks in advance for any and all help,

Gurmi

View 1 Replies View Related

Adding Size Of Identified Tables On All Databases On A Servers??

Jun 13, 2000

I have used the following script to add the size of chosen tables in all the databases on a given server. What I need to do is to create a GRAND total. Can someone give me a hint?


exec sp_msforeachdb
@Command1 = "print'?'",
@Command2 = "use ?
SELECT CAST (o.name as char(20)) as 'Table', SUM(c.length) 'Record size',
MAX (i.rows) '#of rows',
CONVERT (decimal (10, 4), SUM (c.length * i.rows)/(1024.00 * 1024.00)) 'Approx. size (MB)'

FROM sysobjects o, syscolumns c, sysindexes i
WHERE o.id = c.id
AND o.id = i.id
AND (i.indid = 1 or i.indid = 0)
AND o.type = 'U'
And o.name in ('Table1','Table2','Table3')
GROUP BY o.name
COMPUTE SUM (CONVERT (decimal (10,4), SUM (c.length * i.rows)/(1024.00 * 1024.00)))"

Thank you

View 1 Replies View Related

Copy Tables Between Servers Using SQL Server 2005 Enterprise Edition

Jul 26, 2006

Hi, I am trying to use SQL Server 2005 Enterprise Edition to copy tables from my local server to my hosts server at brinkster.com. In the old version you could use DTS, but everything seems to have got far more complicated now! Please can anybody guide me in the right direction? Thanks a lot.
 

View 2 Replies View Related

User Tables/Views Transfer Between Production And Test Servers

Feb 22, 2005

Hello
I have a production database that i need to refresh to our test environment daily. The database size is 700 MB.
I do not need to transfer the stored procedures and triggers , users and logins.
Would a DTS package that runs every night be the best and the easiest solution to implement or should i look into log shipping and snapshot replication.

thanks

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved