I would like to know your experience about how to make regular database restore and point-in-time restore in SQL Cluster environment.
(1). My first question is about database backup. We use SQL Server backup in our shop. We hope that we can back up the database to a one network shared drive. The SQL Server can not do it. It only allows us to back up database to the local drive. Since there is no too much spaces in the local drives (C, D and E), I would like to know how we can back up to the network shared drive.
(2). A few database are critical databases which require point-in-time restore. How we can back up database (complete backup or differential backup), transaction log?
(3). If database and transaction log crash at the same time, can we still do point-in-time restore?
(4). How we do Master DB and MSDB back up? Weekly or monthly?
I am new to database. I have a website in a hosting environment and it uses sql server 2005 databases. I want to have a complete backup of my database in addition to the hosting company's routine backup. I want to write a program to automatically backup everything every night. And of couse have the ability to restored the whole database if necessary. How should I do it? Are there any tools exist that I can use? What is the best way to deal with backup/restore in hosting environment? Thanks!
I have a SAN and configuring a cluster on SQL 2005. I initially created a Quorum drive when setting up the cluster and now added 4 more drives to the physical node but when I try to install SQL that drive cannot be located.
Do we need to create all the drives when installing the cluster or what is the way to add the drives later on.
I have finished a change request from our client. I need to update clients' database with the one in developments.Here is the changes i made to database:Added/Changed some tablesAdded/Changed some stored proceduresAdded data to some dictionary tableThe data in clients' current database MUST be kept. So how can I merge the changed information to clients' database?
I want to build a SQL testing environment in an active/active setup. Any recommendations on what I could use if I want to set up the most bare-bones system. I want to do it as cheaply as possible.
1) I have the Active/Passive cluster environment (using Microsoft cluster service). 2) SQL2000 is installed on the cluster (Virtual instance). 3) Virtual instance of SQL Server is used as Publisher in Transactional replication with updatable subscribers. 4) Subscribers are passing updates to subscriber using MSMQ 5) The problem is : after I establish replication , the Queue Agent fails with following error (“Failed to initiate search for MSMQs”)
Need to set up a linked server in two node SQL Cluster pointing to a standalone SQL thru security setting "be made using current login's security context". It's double hop Kerberos setup. Cluster uses a domain account, so we manually set SPNs for that account with both instance name and protocol as well (set up these SPNs with SQL virtual name only).
Also, constrained delegation has been  set to that stand alone SQL services (MSSQLSvc).
Both SQL cluster and standalone servers are in same domain, cluster service account is domain account and standalone SQL uses default SQL local service account.
Coming to SQL, when I create linked server, sometimes it lets me create without issues and sometime it throws this below warning and even if I create it won't work.
 Login failed for user 'NT AUTHORITY/ANANOMOUS LOGIN'.Â
Currently i faced a problem while setting up SQL 2005 STD into the Cluster Environment.
User has existing windows cluster purposely for Print Cluster usage. We install SQL 2005 standard version into windows cluster. After installed SQL 2005 cluster into windows cluster. All SQL services can be up correctly in Cluster. Then we install Print Manager Plus software from vendor Softwareshelf.com into both cluster nodes. Configure/add one additional resource as generic service with dependency with windows/system32/spooler. Everything is working fine for that night. Next day morning I install SQL 2005 Management Tools into both server. User claim that one of the printers has 1 print queue cannot be deleted in Print Management software from Microsoft. We tried shutdown and unplug cable from that PC. That print queue also cannot be deleted. That I check with Cluster Administration. Found Visual C++ runtime error. It is related with €śspoolsv.exe€?.
Existing environment: OS is windows 2003 Ent R2 SP2 x 2 Cluster has been setup for print management server. Please help.
We will be implementing our first SQL cluster in December. Our current plan calls for a shared development/test database server with one physical server, but two SQL Server instances. Our production environment will be a SQL cluster. Is it necessary to create a clustered test environment for testing patches, hot-fixes, etc...?
We are planning to build a cluster environment on MS SQL 2005. We want to install multiple named instances in a single database server and use cluster for that database server.
Is this option possible ? Any relevant documentation would be helpful
I am installing SSRS 2005 with the following scenario:
- Report Server and Report Manager will be installed on web server
- The repository databases (ReportServer and ReportServerTempDB) will be hosted in SQL2005 cluster server.
Both servers are running Win2003 SP1, SQL/SSRS 2005 SP1
When I try to browse the ReportServer virtual directory from IIS on the web server, the system keep asking username and password. After 3 times trials and it failed eventhough the username and password is correct.
The error message is:
HTTP Error 401.1 - Unauthorized: Access is denied due to invalid credentials. Internet Information Services (IIS)
I've been searching on the web and found couple of articles talking about this issue. I've tried some of them like: http://support.microsoft.com/kb/887993 or even this one: http://groups.google.ca/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/991c9178ceab8f58/8f1a447aa60ad837?lnk=st&q=reporting+services+the+server+is+not+responding&rnum=3&hl=en#8f1a447aa60ad837
But this doesn't work.
I've checked both event viewer both web server and SQL server, but I can't see any information that tell me what had happened.
I also look at BOL, but doesn't help much.
Is there anyone can help me or point me to the right direction? Is there any guide document that explains step-by-step how to install SSRS in my scenario or similiar?
I have getting issues when i am creating listener for always On . Error shown as below
Can not bring the Windows server fail over cluster (WSFC) resources online. (Error Code 5942). The WSFC service may not be running or may not be accessible in its currents states, or the WSFC resources may not be in a state that could accept the request.
For information about this error code see "system error code" in windows development documentationÂ
The attempt to create network name and IP address for the listener is failed. The WSFC service may not be running or may not be accessible in its currents states or the value provide for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate network name and IP address with network administrator. (Microsoft SQL Server error 41066) ...
We have a valid full backup of a database. We know it is valid, we have restored it twice from the network with no problems, but we do not have access to the network location from our sandbox environment.
The .bak file is sizable at about 9GB. The .bak file resides on our internal network, in a SAN drive. No problems there. When we copy the file from there into a sandbox environment to attempt the restore in the sandbox environment it gets corrupted. We've tried three different times and all three different times it gets corrupted. First time we copied the file over to the sandbox the restore went up to 50% and failed. The second time we copied the file again and attempted the restore again it failed at 70%.
The third time it failed at 60%. The error message we get during the restore is "...Read on ... failed: 13(The data is invalid) Msg 3013, Level 16, State 1, Line 1 Restore database is terminating abnormally."
Now some background here. To move the file our network team is doing this: they have this .vmdk file that they mount out in our production environment (which has access to the network location where the .bak file is), copy the file into the drive, then move the .vmdk file into the sandbox environment(which does not have access to the network location), mount the drive in the sandbox environment, and then I have access to the .bak file from within the sandbox environment.
Something in the process of using the .vmdk file to move the .bak file from production into the sandbox is causing the file to get corrupted.
I am trying to set up log shipping in a clustered server environment. I am pretty confused about the location of the shared folder to be created to put backup created by  log shipping job. Which drive should I use either local or clustered shared drive to store the backups in primary server and  to copy the same in secondary Server?
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME> FROM DISK = 'D:DATAMYTEST.DAT_BAK' WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF', MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF', REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME> FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
I have a cluster set up and working fine. I am in a stage where I need to do the backups. I have added 4 disks as dependecies to the sql service for the instance. The backups should not be on these disks. When I do a simple backup I can see only these 4 disks so my question is how do I do the backup to fileshare or other disks that are not partof the cluster. I have 3 virtual servers so what wil be the best thing to do for backup
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
We are planning to upgrade the SQL Server in our production environment from SQL Server 2000 to SQL Server 2005. This is a 4 Node cluster environment with 3 Databases on 3 Virtual instances. The main requirement is to achieve this with no/minimal downtime.
Could you please suggest or direct me to any documentation for the best practices used to upgrade such an environment?
I have been experiencing difficulties achieving the above.
We have a secure network environment and the connection can only be established from within, not from outside, so basically, there is a rule that allows me to go outbound on port 5022 to our other network.
There is no domain trust relationship, so I have had to create Certificates and use these as the method of authentication.
I have managed to get this working on two other environments where there is two way communication enabled on port 5022.
I thought I might be able to fool it into forcing the mirroring without two-way communication by setting up mirroring in the environment and using the hosts file to change the actual IP address of the server, this didn't work. The mirroring monitor thought that mirroring was still active, but on the other server, the state never changed and was just waiting for something to happen.
I also thought I might be able to turn the transactional safety off, but only to find out that this can only be enabled in the Enterprise version of SQL Server.
The reason why I have been trying to get mirroring working this way, is due to a requirement for an encrypted connection, I know that with SQL Server mirroring you can choose the encrpytion method and the plan was to create snapshots of the database in the other environment, just so we could access the database.
I think we're at a stage where we will be willing to explore other possibilities, so if anyone could point me in the right direction, it would be greatly appreciated.
Please bear in mind, that we're not concerned with redundancy, this is purely to get the data from one point to another.
Can we backup our cluster databases directly to tape using native backups (without using any third party tool) ? It's SQL Server 2012 two node Active/Passive cluster. One of the DB will be huge in size, hence checking if we can directly backup from the cluster instance to a tape.
I developed an asp.net application in visual web developer 2005 express edition and SQL sever 2005 express with Advanced services. The application has been deployed and iam wondering what tools are availabel to for backing up my data. Are there any tools i can use to back-up my database. Iam not talking of third party tools but tools a vailable in sql sever 2005 express with advanced services or visual web developer express. OR can write a vb.net Sub procedure that i run and have my database backed up. If so where can i start or what other options may i explorer.
I've read lots about why you shouldn't normally shrink databases in posts such as this:
[URL]
But we have a situation where we are required to copy the live db to various non production environments for testing. Part of this process involves truncating a number of tables with masses of blob data. So we're freeing up quite a lot of space. The question is how to reclaim this? The database is peculiar in that it's got no clustered indexes so I can't rebuild indexes on another filegroup with drop to move and rebuild.
I've tried dbcc shrinkfile specifying a size. I've tried to shrink the file in increments. The problem is I'm just not getting much space released. I get maybe 2-3%. I suspect this is because we're dealing with heaps with some tables that have sparsely populated blob / image data.
Is there an alternative to shrinking? Should I recreate all the db objects in a new database? It doesn't matter if the process takes a while or if it has to be done manually.
Can someone tell me how to make a backup in a sql clustered environment (MSSQL 2000) and how to restore that backup in clustered environment (MSSQL2005)? A step by step backup and restore procedure is appreciated.
hi,I was redirect to this URL* based on Deploying a SQL Database to a Remote Hosting Environment,pertaining to my question on how do I convert MDF file to .SQL.I had downloaded and installed the program recommended that is Database Publishing Wizard.but the problem is now, there isn't any context menu "Publish to Provider" * when i right click on thedatabase aspnetdb.mdf..please advice me. thanks.* Reference : http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx
I have been looking for some documentation that would support or rejectmy opinion on Production -vs- Development naming conventions. Ibelieve that each environment should be housed on separate servers withidentical names, access, users, stored procs....... If you eitheragree or disagree with this methodology, I would appreciate your input.TIA,Bill
Our DBA is out for about 6 weeks. One of his regular jobs is to take a Database that is in our Prod environment and copy it to our Training environment.
I have backed up and restored to different servers before, however the Database name in Prod is different than in the Train environment in this case.
In other words I have a PROD database named DATABASE-XX that I want to copy to another SQL server and restore it to DATABASE-XX-TRAIN.
Is there anything special I need to do, other than backup on PROD, copy to TRAIN and restore to the corresponding DB in TRAIN?
I am running an application with an SQL database and it works fine. My question is about a customer changing his information. We will need to be able to produce copies of old orders/invoices even after that company's info has changed (e.g. he moved or changed the company's name).
Assuming he changes his company's name, for example, do I,
1. Enter him as a "new" company? This allows me to keep the old info in the database if I query the "old" name but does not provide any portability of "old" information forward to the "new" company like payment patterns, past orders, etc. since he is "new" and has no past.
2. Or do I change the record of the"old" company to reflect the "new" name? Here I can keep the "old" past under the "new" name but I now cannot retrieve an old document that reflects the "old" name.
3. Or, the third alternative is to export to alternative storage all the files of the "old" company and go to number 2 above?
I thought about copying the "old past" into a "new" customer but then I'd have double sales, receipts, etc and I just can't see an end to that debacle.
None of these sound like fun but I'm sure I'll soon have to do one of these. I can't be the first one to ask this and I'm sure there is someone out there who is smarter than me/has a more elegant solution. Any suggestions?
I also posted this to Small Business Development forum but thought the SQL guys might have another idea.
I have create a web site in visual studio with form user authentication. Thats create a mdf database in this path:
c:inetpubwwwrootwebsiteapp_dataaspnetdb.mdf thats use Sql server
i would like to access this database with c# form application on a network with multi-user. if i change the drive letter of the path above to f: (mapped drive) or \serverc, i got this message
System.Data.SqlClient.SqlException: The file "h:InetpubwwwrootwebsiteApp_DataASPNETDB.MDF" is on a network path that is not supported for database files. An attempt to attach an auto-named database for file h:InetpubwwwrootwebsiteApp_DataASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
i read that sql server did not support database sharing with mapped drive or unc share.
So how can i share website database with c# application???
Note: of course if i develop the website and the application on the same machine its work. I want this application run on several work station accessing all the same mdf database with his web user member.
