It seems to me that either by accident or design, SQL Server tends to steer you away from backing up directly over the network.
Are there reasons for not doing this because you obviously don't want to leave your backups on a local drive in case the drive fails?
Some possibilties that I can think of are:-
1. Local drives have faster access times and SQL backups can get quite large. I did a quick test and found that a netwrok backup takes 2 to 3 times longer than it does on a local drive.
2. Backing up on the netwrok could hog too much bandwidth. I haven't tested this and would be surprised it it's true.
3. There could be some reason that you don't want the Server and Agent services running under a domain account but want to leave them on the Local System account. I am not aware of any such reasons by the way.
4. Local drives persumably have a slightly higher availability than network drives. If the server is running, the drive should be available.
My company is thinking about moving to a product that uses Microsoft SQL Server and I have been asked to find out what are the Pros and Cons of the product, if any.
Anyone know where I could find some good articles about pros and cons of upgrading SQL 7 to SQL 2K?
Our boss suddenly got this bug up his rear that we need to upgrade. :( Probably because we are to a point where everything is running smoothly, and he needs to throw a monkey wrench in it.
We are a very small shop, and are not stressing SQL 7 in the least. I am interested to read some articles about SQL 2K, because right now, I can't see any possible reason an upgrade would be worth doing.
I have a requirement where I need to perform a query for positioninformation. But for some types of entries, I need to "expand" the rowto include additional position rows. Let me explain with an example:An index is a security that is made up of components where eachcomponent has a "weight" or a number of shares. So if I have 1 share ofthe index, I have X shares of each component.AAPL is an Equity, CSCO is an Equity, SPY is an Index. Lets say thatSPY has one component, AAPL, with shares being 10. (1 share of SPY = 10shares of AAPL).So, I do some trading and I end up with positions as follows:+10 AAPL-5 CSCO+2 SPYThe query I need returns:+10 AAPL-5 CSCO+2 SPY+20 AAPL (from 2 SPY * 10 shares)which becomes (after grouping):+30 AAPL-5 CSCO+2 SPY-----------------------------------------Based on that criteria and the following schema (and sample data):-- Drop tablesDROP TABLE [SecurityMaster]DROP TABLE [Position]DROP TABLE [IndexComponent]-- Create tablesCREATE TABLE [SecurityMaster] ([Symbol] VARCHAR(10), [SecurityType] VARCHAR(10))CREATE TABLE [Position] ([Account] VARCHAR(10), [Symbol] VARCHAR(10), [Position] INT)CREATE TABLE [IndexComponent] ([IndexSymbol] VARCHAR(10), [ComponentSymbol] VARCHAR(10), [Shares] INT)--Populate tablesINSERT INTO [SecurityMaster] VALUES ('AAPL', 'Equity')INSERT INTO [SecurityMaster] VALUES ('MSFT', 'Equity')INSERT INTO [SecurityMaster] VALUES ('MNTAM', 'Option')INSERT INTO [SecurityMaster] VALUES ('CSCO', 'Equity')INSERT INTO [SecurityMaster] VALUES ('SPY', 'Index')INSERT INTO [Position] VALUES ('001', 'AAPL', 10)INSERT INTO [Position] VALUES ('001', 'MSFT', -5)INSERT INTO [Position] VALUES ('001', 'CSCO', 10)INSERT INTO [Position] VALUES ('001', 'SPY', 15)INSERT INTO [Position] VALUES ('001', 'QQQQ', 21)INSERT INTO [Position] VALUES ('002', 'MNTAM', 10)INSERT INTO [Position] VALUES ('002', 'APPL', 20)INSERT INTO [Position] VALUES ('003', 'SPY', -2)INSERT INTO [IndexComponent] VALUES ('SPY', 'AAPL', 25)INSERT INTO [IndexComponent] VALUES ('SPY', 'CSCO', 50)INSERT INTO [IndexComponent] VALUES ('QQQQ', 'AAPL', 33)-- *****************************-- Based on the rules:-- 1) Index positions appear like other positions (account /symbol) pair, but-- its components show up as new rows of account (of index),symbol (equal--to component symbol), position (equal to shares * index position)-- 2) One row for each account / symbol pair (GROUP BY account andsymbol, SUM position)-- Expected output (without grouping) (sorted by account / symbol)-- 001 AAPL 10-- 001 AAPL 375 (component shares * index position) (25* 15) (SPY)-- 001 AAPL 693 (component shares * index position) (33* 21) (QQQQ)-- 001 CSCO 10-- 001 CSCO 750 (component shares * index position) (50* 15) (SPY)-- 001 MSFT -5-- 001 QQQQ 21-- 001 SPY 15-- 002 AAPL 20-- 002 MNTAM 10-- 003 AAPL -50 (component shares * index position) (25* -2) (SPY)-- 003 CSCO -100 (component shares * index position) (50* -2) (SPY)-- 003 SPY -2-- Expected output (with grouping account / symbol) (sorted by account/ symbol)-- 001 AAPL 1078-- 001 CSCO 760-- 001 MSFT -5-- 001 QQQQ 21-- 001 SPY 15-- 002 AAPL 20-- 002 MNTAM 10-- 003 AAPL -50-- 003 CSCO -100-- 003 SPY -2---------------------------------------------Is a UNION the best way to perform the query. What are the pros andcons? What, if any, is a better way?SELECT[Account], [Symbol], SUM([Position]) AS [Position]FROM(SELECT[Account], [Symbol] , [Position]FROM[Position]UNION ALLSELECTP.[Account] , IC.[ComponentSymbol] AS [Symbol] , (P.[Position] *IC.[Shares]) AS [Position]FROM[IndexComponent] ICJOIN[Position] PONP.[Symbol] = IC.[IndexSymbol]) DGROUP BY[Account], [Symbol]ORDER BY[Account], [Symbol]
Hi alli am building a SQL 2000 database that it is proving a littlechallenging, i have companies with multiple addresses, phone numbers,owning mine sites etc and also joint ventures so maybe you get thepicture with a few design issues that i ma encounteringMy queriy is about a primary key identity, and which one to use withrespect to either the identity data type or the unique identifier ,I am aessentiall building an address table to hold all multipleaddresses as well as phone numbers etc, so my desire to have a uniqueidentity for each record is very important.My view is i will run in to violation errors by just using the tableidentity data type, i could i suppose use composit primary keys butthat may have a performance impact, although thiis will not be a hightransaction database.Does anyone know about performance issues regarding each identitysolution, by using a generated 16 bit identifier there are going to behuge numbers for the DB to verify. or am i worried about nothing?any views greatly appreciatedregardsGreg
Im about to start converting code to Stored Procedures for all my reports in Reporting Services. I was wondering what the pros and cons of this may be.
Like in the subject: What are the cons and pros of using nvarchar(max) versus ntext? Does it have something to do with having to enable full text search perhaps in the latter case?
I'm just wondering what any pros and cons of using multivalue columns in a database are.
I'm designing a database which will have a column for FABRIC_TYPES_AVAILABLE for a certain FURNITURE_ITEM. Each FURNITURE_ITEM can have multiple FABRIC_TYPES_AVAILABLE of course. So I was just going to store a 2 or 3 digit number of the FABRIC_TYPES_AVAILABLE in that row. So I would have something like....34,24,453,32,23,45,67,65,43,21,21,45.
Anyway....thanks in advance for any information. Links I could read would be great too...b/c I did do a bit of searching, but didnt find much.
I have a main database...for this large Web site...and Im wondering what would be the PROS and CONS of using another database (located on the same, or on another SQL Server). Im just thinking this would be good incase we ever needed to take some load off one of the servers.
Also, we will be integrating Community Server into this Web site. Of course you know CS adds its own database objects which crowd up our main database objects.
We were thinking of giving CS its own database also; bad practice, or....it doesn't matter much?
I was wanting to know. I am making a site that might be come big. And me and this dude are considering pickering abotu GUID. I don't want to use them but he does. And I was wondering what should we do? I know nothing about guids
vinod writes "Q1>Should I apply filter in sequence(based on primarykey,not null,comparision,between clause) ?
-How to apply filter in correct format,and SQL server internally execute it. Q2>Should I use 'is not Null' to be put at the last?
Q3>Should I use 'between clause' rather than relation operator i.e(empid>10 and empid<200)
Q4>Does filters of sequence has any impact on the query execution process
table1[pkey1,col1,col2,col3] -->pkey is pkey1 table2[pkey2,pkey1,col11,col12,col13]--->pkey is pkey2, and pkey1 is foreign key
CaseI->select table2.pkey2,table1.col1 ,table2.col12 from table1 inner join table2 on table1.pkey1=table2.pkey1 where (table1.col3>100 and table1.col3<300 ) and table2.pkey1=2020 and table2.col13 is not null
CaseII->select table2.pkey2,table1.col1 ,table2.col12 from table1 inner join table2 on table1.pkey1=table2.pkey1 where (table1.col3 between 101 and 299 ) and pkey1=2020 and table2.col13 is not null"
Are there any vices to using default constraints on all columns in your table. For example an Int that defaults to 0 or a char or varchar that defaults to ''
I know that 0 and Null are not the same thing. But if your programs don't have the concept of NULL then you have to convert the NULL to zero.
So, DEFAULT CONSTRAINTS on every column. Is it good or Bad?
Can someone provide information or a link to information regarding the pros and cons of saving files directly into a SQL 2005 database? I'm actually for saving files to a database (cleaner implementation then just saving the location then having to get the file, etc), but my project manager is not convinced so I need to make an argument for (or against depending on what I actually find out) using varbinary data type. Thanks.
Hi All!We are doing new development for SQL Server 2000 and also moving fromSQL 7.0 to SQL Server 2000.What are cons and pros for using IDENTITY property as PK in SQL SERVER2000?Please, share your experience in using IDENTITY as PK .Does SCOPE_IDENTITY makes life easier in SQL 2000?Is there issues with DENTITY property when moving DB from one serverto another? (the same version of SQL Server)Thank you in advance,Andy
hi all, I was wondering if anyone knows of any pros/cons on storing SSIS (2005) packages within SQL Server 2005 SP2. We're contemplating the migration/storage of a large number of packages, a minority of which designed in dts (SQLServer 2000) and using third party activx components.
We are in the process of replacing our primary production server. In the process of determining how SQL server is going to be structured, it has been suggested that I place all current and new indexes on a separate file group. These filegroups would then reside on a separate shelf on the server. What are the pros and cons of doing this?
I am using transaction replication between a transaction and reporting database server. When I use a snapshot to initialize my subscribers, I currently get a lot of deadlocks during the snapshot creation. I am considering using a database backup instead. Can anyone tells me how to reduce the table locks that I am getting during snapshot creation or advice on using database backups?
i try to create a dump device on a drive remote from the sql server(6.5),using machinenamefolderdump.dat. it creates the dump but when i tried to initial the backup it gives and error system that the device is offline. thanks in advance
hello group,a few days ago i used the following backup statement and everthingworked fine:sp_addumpdevice 'disk','pipedump','\192.x.x.xLData est.bak'Backup database Test to pipedumpThe same statement is not working today!!!!!What's the reason?Please dont tell me that check if the network path exists or not! I'vechecked the path and permissions. Everything is ok!Thanks in advanceDebian*** Sent via Developersdex http://www.developersdex.com ***
I tying to test my backup to a network drive but it fails.
Please help ..thanks this is the script -- example from BOL:
BACKUP DATABASE pubs TO DISK '\radrisMSSQLBACKUPpubtest.bak'
this is the error:
Server: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device '\radrisMSSQLBACKUPpubtest.bak'. Device error or device off-line. See the SQL Server error log for more details. Server: Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.
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?
I am trying to backup a database with a command like:
BACKUP DATABASE my DataBase TO DISK = '\bkSystemkDiskBackup1.bak'
but I get the error 'Cannot open backup device '\bkSystemkDiskBackup1.bak'. Device error or device off-line. The bkDisk folder is shared, with Everyone full-control access (it's a test environment)
We get an error when we are trying to run a backup of the SQL db here, we just got a new ISP to this company and al the routers and the like has been reconfigured (if that might be the problem). Would apreciate a speedy help with this one. Here is the error message we get
un OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259 (80004005) Error string: [DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 11 (B) Error string: [DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error string: Connection failure Error source: Microsoft OLE DB Provider for SQL Server Help file: Help conte... Process Exit Code 1. The step failed.
I have creted a backup device on network drive and getting the following error when I run the backup command as a job.SQL server and the agent are running on a domain account which has full permission on the network drive.Any help is greatly appreciated. Thanks!
"Cannot open backup device 'mccdiffbkpond2build'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed"
I am setting up a backup on one of my DB's and the network drive does not appear as an location that I can backup too. Only the C drive is present. However, on the server I do have a drive mapped to the network drive, so it should be available. Has anyone encountered this issue before?
I have scheduled a backup on a remote machine. Everyday I FTP these backups from the remote machine to the local drive.
A local drive is maped to the remote machine in Windows NT Explorer but SQLSERVER7 Enterprise Manager can't see this drive so that I could do the backup to it.
