Hi,
I am using an Enterprise Manager and i have created a Database Maintainance Plan, can anybody guide me as to how to create backup on a remote machine.
I also mapped that device(say M/c B) onto my server box(M/c A) but i am not able to see that particular location in my backup activity screen.
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.
Hi I'm an experienced programmer, but just don't have much experience with ASP or MSSQL. I inherited a website and now need to set up an automated backup system for a fairly large (40MB) MSSQL database hosted by a 3rd party provider of reasonable quality with ASP and SQL Server 2000. Let's avoid ASP.NET solutions since the website I inherited is written in "straight up" ASP (old school).
I have access to the database, login etc. I'm thinking I should combine an ASP script with the database connectivity and write daily/weekly backups to a dir on the hosting server.
I am experiencing an issue with SQL Server 2000 Maintenance Plan. DB Backup job fails to delete old backup files from the file server (I am backing up to the network share - actually, a DFS). Backup part of the maintenance plan/job succeeds, but then cleanup part fails.
I made sure that service account under which SQL Server Agent is running, has sufficient privileges over the network share by logging in and successfully deleting files in question.
I was not able to locate any log entries either on the SQL Server machine or on the file server machine that would indicate the root of the problem. Even though I turned on auditing for Delete operations for the destination folder, its subfolders and files, I could not find anything in the Security event log.
I would appreciate any ideas on how to troubleshoot and correct this problem.
ok, here is an interesting one. We have multiple jobs that run the following SQL Backups to Permanent Backup devices.
Backup Database DBX to DBXFull with NoInit - 1 am Backup Database DBX to DBXDiff with Differential, NoInit - 12 pm Backup Log DBX to DBXLog with NoInit - every hour on the hour
the devices are in path E:SQLBackups<databasename> E: is on a SAN
Every Night E: is backed up by legato, When Legato Starts to backup the directory, at the hour a transaction log backup occurs. The Backup Fails and The Database Is Taken OFFLINE.
Questions are as follows. What is causing the db to be taken offlline? How can i stop the db from being taken offline while still mainting my current backup strategy.
Temporarily we have stopped tlog backups during the directory backup phase (legato) and then resume them when it is complete.
Difficulty Level: we will not be using Legato's SQL Plugin for the backups, they have to be performed with jobs and SQL Backup.
Can SQL 2000 initiate backups to 3rd party applications (like LiteSpeedand BrightStor's SQL Backup Agent)? My goal is to be able to managebackup and restores completely through SQL Enterprise Manager and QueryAnalyzer without having to logon to the Server and using the 3rd partybackup application's software. Is this possible through SQL?Thank you,Stephanie
Hi,I'm trying to use one of my machines as a database server, just for local network testing/development etc. This machine/server is on my local network via a router.Can anyone advise me of how, when using VS 2005 Beta 2, to connect to my SQL Server 2005 installation on the server running on my network. ? In the 'add connection' dialogue, what do I put in the 'Server name:' field, (apart from server name...) is it the name of the machine or the IP address of the machine on the network ?Any and all advise appreicated.Thanks in advance.
Hi All,I have a requirement to create the database with data and log files ina different machine on the network.Googling, I got a link which said Mapped drives do not work but UNC pathswork. However in my case, both are failing.Any advices or links on net will be highly useful.Thanks and Regards,Chandra Mohan
I am hoping you will be of assistance to me and provide a little advice on what exactly is possible using SQL Express / SQL Server 2005 with regards to remotely connecting to a server.
I am a final year undergraduate student studying computing sciences in the UK. As a final year project I am attempting to create a client-server database, where the client (a WM5 PDA) can connect to an SQL server hosted on a different machine (a PC). However, I must be able to connect to the server when the PDA is being used on any Wi-Fi connection.
Basically I want the PDA to be able to connect to a database kept on the PC at home when you're out and about using the PDA's wi-fi.
Is this possible? If so, could you explain in 'dummy' terminology how one would go about this?
As my university is part of the MS Academic Alliance I have access to VS 2005 and SQL Server 2005 for this project.
Many thanks for your time and I eagerly await your responses! Simon
How do I check whether the Sql Server is installed in the particular machine in a network within the same domain? Suppose If i got a machine name as c-4200 in a network I need to display the all the database instances it has How do i do that?
I have created and deployed an SSRS report, which I am able to access from the same machine using the URL. But I am not able to access the URL from a another machine in the same network.
What could be the problem, do I need to make some configuration changes to the reporting server?
I found errors frequently in cluster events at logs as below
"Cluster network interface 'Node1 - 10.X.XXX.1' for cluster node 'Node1' on network 'Cluster Network 1' failed. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapter. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges."
How to fix this issues? is it missing any patch OS or cluster side?
Im getting an error when i try to connect to my MS SQL database
0x80040e21 .... multiple-step ole db operation generated errors ....check each ole db status value
this code works connecting to database via web hosting, only things i changed was to replace the server with "(local)" [also tried ip no joy there either] and add integrated security to the connection string
ms sql server and IIS, personal web server all on same laptop, i can access the same database with same details no problem to login it gives up on the subsequent login
code (generally)
Set objconn=Server.CreateObject("ADODB.Connection") objconn.ConnectionString="SERVER=(local);Database=blah;uid=blah1;pwd=blah2;Integrated Security=true;"
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.
Ex:-
CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY
Hi All, I have successfully installed MSSQL server 2005 on virtual machine running on XPSP2. No error messages were shown durring the installation. Still I do not see it working at all. There are no services insltalled for the SQL Server and it can not be accessed by any other applications. If anyone has an idea why this is happening, please help?!
Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.
The scrpt looks like this:
--------------------------------------------------------------------------------------------------- USE [master] GO IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME') EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD' GO GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME] GO GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME] GO GRANT CONNECT SQL TO [SSDBUSERNAME] GO GRANT CONTROL SERVER TO [SSDBUSERNAME] GO GRANT CREATE ANY DATABASE TO [SSDBUSERNAME] GO USE [master] GO If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME') ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD' GO GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME] GO GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME] GO GRANT CONNECT SQL TO [SSDBUSERNAME] GO GRANT CONTROL SERVER TO [SSDBUSERNAME] GO GRANT CREATE ANY DATABASE TO [SSDBUSERNAME] GO USE [master] GO IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ') DROP DATABASE [ISIZ] GO USE [SurveyData] GO exec sp_adduser 'SSDBUSERNAME' GRANT INSERT, UPDATE, SELECT, DELETE TO SSDBUSERNAME GO USE [SurveyManagement] GO exec sp_adduser 'SSDBUSERNAME' GRANT INSERT, UPDATE, SELECT, DELETE TO SSDBUSERNAME ---------------------------------------------------------------
I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.
I was wondering if somebody there could help me with this problem?!
I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.
Current Environment is MSSQL 2000 32-bit with current Service Packs.
I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.
I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.
Upgrade options:
1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via: a. DETACH / ATTACH b. BACKUP / RESTORE c. Is one method more advantageous relating to the end result? 2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases? 3. If you have performed this migration and have relevant experience please pass them along.
Hello all, I will try to supply all of the information available to me to help generate some answers to the question I have. I am doing some benchmark testing of an application of how it deals with various database queries, for example oracle 10g, DB2 and MSSQL 2005. I have a very simple database called "student" that has various tables, with various columns, all as mentioned very simplistic. Each table has a few hundred rows of data, the most being one table with 2000 rows. The same database (structure and dataset), is replicated across all 3 DB's. In order to do the testing, I have been recording the same queries (select * from table_a, select * from table_b etc etc) for each database at an interface which monitors traffic between the client and db server, the problem starts here; The size of the tcpdumps are vastly greater for MSSQL vs oracle or db2. Some examples:
100 query or transaction dump: mssql=3.7mb oracl10g=133kb
50,000 query or transaction dump: mssql=1.8gb oracl10g=0.6gb db2=0.6gb
as mentioned each database's tables and data are identical to my knowledge and all queries are the same. The bloated dumps are making mssql performance numbers look bad.
So my question is: What could be the reason for MSSQLs client/server queries to contain so much information, has anyone else come across this? Is there any setting or something I could try to minimize it?
Hi,i have SQL 2000 and 2005 on same machine(with different intance names,of course), my laptop - XP with SP2. The 2005 works fine but i can'tconnect on SQL 2000. All the the SQL services are started.Any idea? Have i to reinstall 2000?Tks,Lourival
i want to a backup a database on a different machine.Iam trying to give unc naming conventions i.e backup to //servername/sharename/file.bak but its not working.iam getting an error saying device error or device offline-access denied.
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 ***
My first post on these forums and I'm hoping someone can please help me before I pull all of my hair out.
I'm currently working on the redevelopment of a website. The old one was written in ASP and we're going to totally redo the whole thing. As a lot of the content was dynamic I want to fire up the old site on my laptop to browse and pull content from. I have a backup of the entire website which I have put under a fresh IIS install. I also have a backup of the MSSQL database. I have restored this to the local machine and can view it using the SQL Server Management Studio. The problem is that I think I am having lots of trouble synching the users/permissions for it. We don't have a backup of the Master databases so I am stuck with what I can extract from the site backup and web code.
Trying to load the old index.asp I get either of these error messages quote:'Login failed for user 'Username'. The user is not associated with a trusted SQL Server connection' or 'SQL Server does not exist or access denied.' depending on how I've played with the user permissions.
Please can someone help me sort this out so that the old ASP webpages can access the DB again? I'm used to mySQL/PHP so am struggling a bit to get this going.
-Edit- Bit more info. Here are the connection details the site uses within an ASP page. <% dim Conn
'---------------------Create connection------------------------------------------------------ Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open "Driver={SQL Server};Server=MYSERVER;Database=dbTest;uid=dbTestUser;pwd=testpasssql"
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?