I am a SQL lightweight and I tried to update the data on the test SQL server with the data on the production server and now the test SQL server won't start.
I was trying to move a sql database from our production server to our test server and things got mucked up. I think where I went wrong is that I tried to backup the database on the Production server and then restore it to the test server. I think I should have backup the production server and then detached the databases from the test server and then attached the backups. I got in a hurry and had an attitude that SQL is so good that if I mess up, I can recover.
These are the databases I recovered to test server:
Builder
CSales
CIB
Master
Model
MSDBdata
Now SQL server won€™t start and I get the error message that the database Model is in the middle of being restored. We are on SQL 2000. I tried to start from a command line with the €“f switch and it still can€™t come up.
Should I reinstall SQL and then attach the databases?
Do I need to bring over the Master, Model and MSDBdata data bases from the production servers.
I am trying to imitate a DR situation where the primary db is down and I need to recover the secondary db on another server. They are a log shipping pair and so to imitate a DR, I remove the log shipping in the primary server maintenance plan. Then I go to the secondary server and disable the log shipping jobs there and attempt to do the following
RESTORE DATABASE database_name WITH RECOVERY
but I can't get exclusive use because the database is in use. But I don't see any other users... am I wrong in thinking that the log shipping was completely deleted? Anything I can do to force exclusive access?
How to test always on availablity after configuring them.I have configured always on group with 1 active and 1 passive with readonly.I want to test from application.what are cases which we can have for testing.
We are setting up a test lab environment with 100 machines. Â We want one master testing db that gets replicated to each to run scripted application tests nightly. Â
My goal is to minimize the amount of work to move this thing to each of the 100 test machines. Â I am wondering if we need to even have the sql local and invest in a monster db server with 100 copies of the db we restore and each test machine point to their own db on that server, or if I should use db mirroring or something to get the master test db to each of those machines instead.
I€™m trying to resolve an issue and wonder if anybody has ever run into and possibly had a resolution for.
I wrote a custom conflict resolvers to manage data at the column level. What I needed to do to achieve this was to use a remote query to the mobile subscriber that raised the conflict. I€™m trying to put in a rule that if there are any issues connecting to the subscriber I want to use the default conflict resolver and move forward. Here is my issue, it seems there is no way I can test a connection without getting a critical error that I can€™t seem to trap. Example
I was trying to do
Select * from [linkedserver].[database].[dbo].[incident] If @@error <> 0 use default conflict resolver
What I did to test this was I changed the linked server authentication to an incorrect password to get the following error that there is no way to trap.
Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user 'sa'.
Does anybody have an idea how to trap this? In SQL 2005 there is a stored procedure
sp_testlinkedserver to handle this but I need this to work in SQL 2000 also, it seems many people have ran into this issue on the web and I tried to get the one where sqldmo is used to test connection but that will not work since I wont always know the password. I would like to just check the @@error value after trying to run a remote query.
Unfortunately a table is Deleted by me from my database. Now How I can Recover that I have no data and structure of that table Now. It Was Very IMP. Table . So Plz Help.......
Is there any tool available to migrate the data from the SQL Server test database to SQL Server production database. Data Migration should be based on a condition which can be given as an input for a table by the user. The dependant tables also should be migrated based on the given condition. i.e data subsetting based on the matching conditions.
Ex : Salary > 2000
The rows of the table which matches the condition alone need to be migrated for the corresponding table. Also its dependant table's rows should be migrated based on the given condition. Please help me with a tool which can automate this.
This past Monday, our Raid 5 array failed on our SQL 2000 server (standard edition). Long story short, we were able to get the data back, mostly. In SQL server Ent. Mgr., I had to restore 4 databases that were 'Suspect' and "greyed out". No problems there... I was able to restore them.
What was interesting was that the SQL Server Agent would not start. As it turned out, a few files were either corrupt or missing after we got the machine back up. We had to restore a few files from tape backup and now the SQL Server Agent starts... but now I have a new problem.
For some reason, my default SQL Server Agent "Job" that is scheduled to run once per day fails. It appears that all of the databases get backed up, but for some reason, the msdbdata.mdf database is NOT being backed up.
I have a few questions. Could my msdbdata.mdf be corrupt? I have a successful backup of the msdbdata.mdf table from 9/10/2007. Can I just restore from that? Will there be any repercussions to any of the other database tables if I do?
Furthermore, should I just restore the msdbdata.mdf, master.mdf and the model.mdf? I really have no idea what these tables do and the repercussions of modifying them. I am not a DBA (obviously) and I just don't want to make an mistakes.
I have SQL Server 2000 Standard Version backups going to Disk then we have Legato Networker Software to make backups of these backup directories to TAPE.
When we retrieve the file from the TAPE and use to recover the database we get the following errors: (Notice some work and some do not. This is from command 'restore verifyonly from disk='
Server: Msg 3242, Level 16, State 2, Line 1 The file on device '\backupfilersqlbackupsqlserver ecovered_iis_mainsqlbackupsqlserveriss_mainiss_main_tlog_200709210130.TRN' is not a valid Microsoft Tape Format backup set. Server: Msg 3013, Level 16, State 1, Line 1 VERIFY DATABASE is terminating abnormally. The backup set is valid. The backup set is valid. Server: Msg 3242, Level 16, State 1, Line 4 The file on device '\backupfilersqlbackupsqlserver ecovered_iis_mainsqlbackupsqlserveriss_mainiss_main_tlog_200709180130.TRN' is not a valid Microsoft Tape Format backup set. Server: Msg 3013, Level 16, State 1, Line 4 VERIFY DATABASE is terminating abnormally. The backup set is valid. The backup set is valid.
If I take the original backup file that are on disk they are all valid.
Thanks for any help that can lead me to the solution
I currently have a test sql server database and I am trying to refresh it with the production sql server data - to get the production current data.
If I copy the production database.mdf file and the database.ldf file from the from the production server and replace it with the test database database.mdf file and database.ldf files and then restart the database, would this give me the production current data, please advise. - If not could you please advise on how I could get the productions current database.
Pages on a full recovery model database corrupted, need to ensure data loss is minimal for restore operation am thinking about restoring the latest full backup.
I currently work for a company that has had a "family business" mentality for many years. The only trouble is, it has grown into a fairly large company over the years and that mentality just doesn't cut it anymore (We have around 200 employees in 2 offices, and 25-50 remote users).
I have recently taken over the role of System Administrator, and I'm having a lot of trouble trying to get everything under wraps. There are a LOT of un-documented things happening with our database/web server, and in order to keep things safe & secure, I need to try & flesh out what those mystery settings would be. I feel that NOW is the time to get some solid documentation down, to protect my ass before some critical failure happens.
I'm wading into unknown waters here, and I'm hoping that somebody who's "been there" might have some tips to make my life a little easier.
I have an IBM Bladecenter, and a spare blade with which I can use to re-produce as close to a mirror copy of our system as possible. I have installed windows 2003 /w SQL2000 and restored a full backup of our databases and imported all database user accounts, and logins. What about Maintenance Jobs? Error Logs? Patches/Hotfixes? Service accounts? Membership roles? System databases? ODBC Sources? Services? Startup Scripts? I'm sure the list could go on & on... What are some of the crucial details I should look into?
Has anybody been in this boat before having little knowledge of what was built before you arrived, and have any insight on how to get through this without too much headache?
Is there any tool available to migrate the data from the SQL Server test database to SQL Server production database. Data Migration should be based on a condition which can be given as an input for a table by the user. The dependant tables also should be migrated based on the condition. i.e data subsetting based on the matching conditions.
Ex : Salary > 2000
The rows of the table which matches the condition alone need to be migrated for the corresponding table. Also its dependant table's rows should be migrated based on the given condition. Please help me with a tool which can automate this.
We have an issue with a 3 node SQL 2012 Always on availability group. Normal operation is node 1 (primary replica) with node 2 and node 3 as secondary replicas.After some patching, SQL wasn't running on node 1 hence the AG flipped over to node 2. This went unnoticed for some time and the transaction log for one of the AG databases became full on node 2 and node 3. (I think this is because it couldn't commit the transactions on node 1 so couldn't truncate it's t-log?) The DB is using synchronous replication btw.So I started SQL on node 1 and flipped the AG back to node 1 (with a data loss warning but I accepted this).Now the issue is that on node 2 and 3, the DB in question is stuck in a "Reverting / In Recovery" State. I've tried various commands such as ALTER DATABASE SET ONLINE, RESTORE DATABASE WITH RECOVERY etc but these fail stating unable to obtain a lock on the DB.
The weird thing is that on node 1 the state of the DB is "synchronised".how to resolve this issue on node 2 and 3? I've left them overnight (in case they were rolling back transactions, the DB is fairly large) but nothing seems to have happened. remove the DB from the AG in node 2 and 3 and add it back in again, ie recreate the replication?
I'm trying to find any useful whitepapers about how to effectively build and operate a disaster recovery site at a remote location for SQL Server 2000. Does anyone know where to find such information?
I also know that one good option for my customer is using the Mirroring feature of SQL Server 2005. What are the other options? Is Replication an effective one for a mission-critical database (online banking)?
I've built my SQL Server Express database with SQL Serevr Management Studio Express, and now I want to enter some seed data to assist in building tha app around it. I cannot find an option to manage the data in SQL SMSX, like I used to with Enterprise Manager. I don't want to have to write an app just to get test data in. Seems like this should be a common need. Am I missing something obvious here? Can't find any reference to this in a search of the forums. Please help.
Can not attach database, Error message; The file you've specified is not a valid SQL Server database file.
My costumer HDD some files Deleted mybe elektric prblem -Elektric go,com,go,come- then windows not opened. insert corrupt hdd Another computer, then recovering (recover my files software) Recover mdf and ldf files. but I can not not attach mdf. file,
I am recovery .mdf file http://www.officerecovery.com/mssql/ some table ok. but my importand table is not ok.
if I can recover another recovery software , my database correct recover or same.? I am using RecoverMyFiles.
We have a requirement to build SQL environment which will give us local high availability and disaster recovery to second site. We have two sites- Site A & Site B. We are planning to have two nodes at Site A and 2 nodes at Site B. All four nodes will be part of same Windows failover cluster. We will build two SQL Cluster, InstanceA will be clustered between the nodes at Site A Server and InstanceB will be clustered between the nodes at Site B, we will enable Always On Between the InstanceA and InstanceB and will be primary owner where data will be written on InstanceA and will be replicated to InstaceB. URL....Now we want we will have instanceC on the Site B and data will be writen from the application available on Site B, will be replicated to the instance on the Site A as replica.
I have a DB shown as recovery pending when running the following:
SELECT Name, state_desc FROM sys.databases;
The DB was created by someone outside of our team using the Full Recovery model & I can see that no transaction log backups have been taken for this, causing the log to growth to a large size.
The MDF is only 5,120kb but the TRN has grown to 10,773,120kb
When I checked the Server I could see the data area had run out of space so I have freed up some space for this so now have 2.5gb available as a short term solution.
The MDF & LDF files are still visible & when checking the SQL log the DB is being reported as having a Full Transaction Log.
Essentially I want to change the Recovery Model from Full to Simple, Reduce the size of the transaction log & bring the DB back online. Luckily this DB is only used by a handful of users but I still need to get it up & running asap.
We are using SQL Server 2012 for both production and development server. Currently, i have plan to transfer the full backup of SQL server database to the development server and then restore it on the development server for testing purpose, so we don't want to disturb on the production.
I have successfully backup the database from the production server and transfer to the development server. however, i encounter some difficulties when trying to do restore. my question is:
1. do i need to firstly create an empty database on the development server and then restore it into this empty database? say i create database call "Test2", then i perform a restore into this database.
2. currently, there is already an existing database being restored previously in the development server and this database is actually the older version of the backup that being restored by the previous engineer. should i remove this database first and restore a new one or both of the database can coexist as long as we put different name for the database?
For the best practice I issued full SQL Server database, differential and transaction log backups. I have setup a process to backup to local disks and then also copy the files to a centralized set of storage. On a weekly basis the centralized file system is backed up to a tape backup device. I know I can get data off of the tapes, but that process is time consuming, not well tested from my perspective and I am not in control of the overall process. Can you offer some recommendations from a SQL Server backup retention perspective?
I have configured replication between Always ON Availability Groups  (Listener) (PUBLISHER), remote distributor to XYZ SUBSCRIBER...with above link ...
Now, I want to know how to replicate Data from XYZ SERVER a PUBLISHER to Always ON Availability Groups  (Listener) (SUBSCRIBER)? Distributor Database being on XYZEX:
XYZ SQL SERVER as PUBLISHER, and DISTRIBUTOR to Always ON Availability Groups  (Listener) SUBSCRIBER...
I'm working on SQL 2012 Box, which is having Logshipping failed on secondary database, the secondary database was in stand by mode right now but no more restore operation performed on this database since 2 weeks! We checked in the SQL error log and found the error code 14421, severity 16, stat: 1
How to reset the logship back to normally, do I need to disable the jobs before proceed any operation!
I have a database that I am trying to recover space from, it consists mostly of unallocated space, but I can’t seem to get that unused space released.
The DB is in simple recovery mode. There are no open transactions (used dbcc opentran).
The server is running SQL Server 2014 and the DB is in compatibility mode SQL Server 2008 (100). It was upgraded to 2014 a month or two ago.
I have tried to re-size the log to 100mb, but any way I have tried (none gave errors), the log file remains the same size. I have tied to shrink the log file (through the UI and via DBCC commands) without success; no errors, but also no change in file size.
I have checked Log Reuse Waits, just in case, and as expected it showed “NOTHING” (select log_reuse_wait_desc, name from sys.databases)
I tried running a checkpoint, but that did not allow any resize or shrink to work.
I have tied creating large transactions to move the used point in the log file, in case this was the issue. I did this by creating tables that I drop after large inserts. While it shows me that the log space % used increased, the log file still does not allow the space to be reduced.
The following is what I was using for the transactions to get the log used.
BEGIN TRAN select a.* into testtable from sysobjects a, sysobjects b, sysobjects c ROLLBACK TRAN
Do I just need to continue running large transactions until the log space used gets high enough to get the “end point” in the log to really move? Is there an easier way to accomplish this (I have several DBs that have the almost identical problem), what I am using moves the Log Space Percent Used about a percent on each execution.
I am able to run the package successfuly in test database. but not in production database. It throughs up error saying
Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. Description: Failed to open package file "D:\TAHOE\APPS\SSISPackages\Integration Services Packages\ArchiveMain.dtsx" due to error 0x80070015 "The device is not ready.". This happens when loading a package and the file cannot be opened or loaded c orrectly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. End Error Could not load package "D:\TAHOE\APPS\SSISPackages\Integration Services Packages\ArchiveMain.dtsx" because of error 0xC0011002. Description: Failed to open package file "D:\TAHOE\APPS\SSISPackages\Integration Services Packages\ArchiveMain.dtsx" due to error 0x80070015 "The device is not ready.". This happens when loading a package and the file cannot be opened or loaded corr ectly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
In SQL 2005, sys.databases has a column named recovery_model that stores a code for the type of recovery model used by the database. Where is the recovery_model column in the SQL 2000 master database?
Now that we have a good programming model in SSIS - the question is whether to write automated unit tests for your packages, and would it generally be a good idea for packages?
Also - if yes to write tests - then where to find more informations regarding How to accomplish that?
hi every one, i need to test SSIS pacakge which will import data from different database where record count is around 5 millions. iam planning to test it through c# code as well as manually also. SSIS source : consist of 7 tables SSIS destination :consist of 7 tables Using c# code iam trying to run ssis package through batch file. i am putting expected rowcount, column count in an excel file and comparing same with destination tables by writing query implementing ADO.Net concept. am i going right way ,can any one suggest best and productive way to test the ssis package . what are the other things i need to test it. do any one can add test cases to it.
S.No
Test Case
1
Verify all the tables have been imported.
2
Verify all the rows in each table have been imported.
3
Verify all the columns specified in source query for each table have been imported
4
Verify all the data has been received without any truncation for each column.
5
Verify the schema at source and destination
6
Verify the time taken /speed for data transfer
7
Fields truncated due to difference in length of the field at destination. Regards Arif shareef
