Hey all,
I'm a newbie to SQL, the organization i work for recently had a server experience a multiple hard-drive failure, corrupted the OS, etc etc. We called in a data recovery specialist who used an Ontrack tool to pull the SQL files we could off of the dead server and onto a different server. Question being how do i attempt a restore from disk, and also the Ontrack recovery software created a directory named "LOSTFILE" which contains multiple directories which contain a variety of different filetypes. Has anyone experienced a similar situation? Any ideas how i can get this resolved?
Ultimately i'd like to setup a new server and transfer the recovered files to it.
I have a copy of SQL Server Express on a clients computer. When i try & restart the services it fails with the following error:- Error: 9003, Severity: 20, State1. It looks like the master database has become corrupt & needs rebuilding. I have found a utility rebuildm.exe that repairs the master db but unfortunately it is not supported in sql 2005. Does anyone have any other suggestions on how to repair the master db.
With some performance issues faced as I checked on Google came across Paul Randal's article on Logging & Recovery: URL...In this regard, and with some schema changes required, ended up with a decision to drop and re-create a database. What are the proper way to do the same and following queries:
A) What would be the recommended way to shift data from some tables of former database to that of the newer one (The latter ones created with the Script Table transaction)? B) Would a simple Drop Database completely delete the former database completely and clean up the memory (database pages etc.) or I need to take some additional steps for clearing up the space? C) Most of the data tables have been updated via loading the Bulk Insert commands time to time from CSV/flat files. Could that be somehow the cause of enlarging the the size of Database and Log?
I have come across a database system which isn't designed to work optimally. It is fairly large (~400GB) and performance of loading and querying is degrading (improper data types, fragmented indexes, non unique clustering key and other problems). So, I have quite a task in front of me, but I am up for the challenge. I figure this is not a unique situation, many of us would have come across this before. I have done this before too, but only for smaller databases, some of the operations here I expect to take a couple of hours or more to complete (depending on load/infrastructure speed etc, I know).
My plan is thus:
+ Take a full backup of the database + Set the recovery model of the DB to simple + Drop non clustered indexes + Drop clustered indexes + Remove PKs (wrong data types, too large!) + Narrow data types (add new column, update column in batches to old value, rename new column to old column) + Add PKs, which will create clustered indexes automatically based on PK ID + Create non clustered indexes + Run a SHRINKDB (normal operations I would never do this, but this is a special case, ensure log file is truncated to a logical size especially after all those table modifications...) + Set the recovery model of the DB to Full + Ensure everything works OK or better
I did a full backup of a db from one server(Express2005) and trying to restore it to a different instance of SQL2005 on the same development machine. (Also had some fulltext columns if that means anything)
Many failures but finally got it to report all was successful except the icon in Object Explorer shows (Restoring...) with no indication of any real activity going on. It's a tiny database with hardly any data in it.
Just not sure what the heck is going on there. It also won't let me into the database until this the (Restoring...) goes away.
I did tried the encryption on server "A" for database "AdventureWorks2012". Then I tried to restore to server "B". There was the certificate issue, and I thought "of course : it's encrypted ! Let's deactivate it". So here I go "ALTER DATABASE AdventureWorks2012 SET ENCYRPTION OFF".I look at sys.databases : not encrypted.I backup using no encryption, I verify using msdb.dbo.backupset : not encrypted.
I move my backup to my other server where encryption was never configured (so no certificate, nothing...), and I have the error : Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xFA130E58C999C4919B8975999C83A75A403B11D8'. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
I want to restore a database (from an encrypted .bak file) - but *not* over the live original if you take my meaning. Encryption is the standard AES-256 that comes with Sql Server 2014 btw. I don't want the original touched/altered in any way. I would like to capture a success message if possible.I can extract the physical device name of the database in question using the following code:
SELECT physical_device_name, * FROM msdb.dbo.backupmediafamily WHERE media_set_id =(SELECT TOP 1 media_set_id FROM msdb.dbo.backupset WHERE database_name='MyDatabase' AND type='D' ORDER BY backup_start_date DESC)
I would like if the newly restored database was rename to something different than 'MyDatabase' (as shown above) and has different logs than the original. If possible, and capture a success message when restored.
I have a Subscriber database updated using transactional replication and want to create a copy for development & testing. The Distribution database is running on the same 2005 instance as the Subscriber and the Publication database on another server running SQL Server 2005.
If I use a TSQL script to run the restore, the database is restored to a usable state but I get the following messages:
Msg 15247, Level 16, State 1, Procedure sp_MSrepl_check_job_access, Line 112 User does not have permission to perform this action. The replication agent job [job name] was not removed because it has a non-standard name; manually remove the job when it is no longer in use. (I€™ve removed the job name from the message) RESTORE DATABASE successfully processed 46219 pages in 935.413 seconds (0.404 MB/sec).
It looks like an attempt is being made to restore replication information too. I note that the RESTORE statement has a KEEP_REPLICATION options which implies (I would have thought) that by default the replication information is not be restored.
Incidentally, before implementing replication, backing up the live database and restoring the backup to a new database took < 7 minutes but running the same process with a Subscriber database backup takes about 15 minutes.
I€™d be interested to know if anyone has any idea what I€™m overlooking or doing wrong.
1) We have got the database dump (.bak) file in one machine and we need to restore this to another machine where the SQL server (version 7) is running. But in the restore option there is no way to choose any mapped or shared drives in remote machine. Any pointer to resolve this problem?
2) Can you set up a database if you have the .mdf and .ldf files?
I am running SQL 7 on two seperate NT4 servers, a production server and a standby server. Both servers are configured for Mixed Mode authentication. If I back up a database on the production server, do I also need to back up the master database and restore it. My reasoning behind this is that users' login details will be held in the sysxlogins tbl in the master, while their access rights will be held in the sysusers tbl in the database I am backing up and restoring. If I back up and restore a database without backing up and restoring the master db as well, will the connection between the system login and the database user be broken. Any comments/suggestions would be appreciated.
Currently have large database in three devices, named Cabinet cab2 and cab3. Cab3 device has been expanded twice. We are upgrading the hardware and would like to get the database into one device named cabinet and clean the mess up our vendor initially created for us. Any Ideas/steps on how this could be accomplished??? TKS Jerry
Using SQL 7.0, has anyone backed up a database and restored on to a different database without the device files being moved around.
For example, With SQL 6.5 we used to backup PROD (source) database and restore to TRAIN (target) database. It worked just great regardless whether the databases are located on the same drive or not. We loved it and used it to refresh TRAIN from PROD on a weekly basis.(using sql Enterprise Mgr)
However, in SQL 7.0 when we attempt this procedure the database and log files of the TRAIN (target) are getting relocated to the drive location of PROD (source) database. Very strange.
Though thru object transfer i.e. import ,we can import data from PROD(source) to TRAIN (target) database, but it is very slow and takes lot of time and performance degrades for source database (PROD)
Any input on above will be appreciated if backup / restore thru enterprise mgr or TSQL procedure works in SQL 7.0 for restoring another database which is also on different drive i.e PROD (source) database on drive C and TRIAN (target) database on drive E.
While this may be in the wrong forum, I thought that I should try here first.
One of the developers that works for this company would like to restore a sample database to the SQL server after installing our software.
Is there a command line command that can be issued to restore/create a database with a file (.bak) of an SQL database (sample)? If this can't be done, can it be done with VBScript or something similar?
SQL is not my strong point, and neither is development. Any help would be appreciated.
Two of my hard drives went down yesterday. and with it went the Sql server database.The data recovery guys recovered the back up database (.bak) file.I have since reinstalled SQL Server 2000 and now when i try and restore the .bak file it says the Microsoft tape back up file set is not valid when i try and open the .bak file.
Whats the right way to add my databse to the SQl Server2000 using my .bak file and if anyone has come across the error they could help.
hey... when i try to restore my database... they gave an error. the database is still in use.. is there a way to kill all the connections to that database ?
A while back, I used SQL Server 2000 to back up a database. Now, I am wondering if there is a way to restore this backup into a SQL Server 2005 environment as I no longer use SQL Server 2000?
Hi all, Our Application is made to work with different database. I have a few query 1. There is option of restoring for only table, the tables can be mention during runtime, how to acheive same in sql 2. During restoring if already same data exists, it throws an exception , based on the exception we display the message data already exist. In sql any option is available to check the data exist or not 3. Can sql create the column during runtime.
theres two .mdf files game_log.mdf game_data.mdf is there a way to restore a database with these files but not with .bak (backup) files? i couldn't connect to database so i reinstalled sql server 2000 and this time i could connect to the sql server fine but when i tried to restore the database, it wouldn't work when i set it to transcation log and it shows an error
The preceding restore operation did not specify WITH RECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step. RESTORE LOG is terminating abnormally.
Hello,due to some testing my SQL Server crashed. I have to reinstall it and ...For now I only have .mdf file and nothing else. My question is: is there anypossibility to restore this .mdf file for SQL 2000? I have some useful datain this file and there will be nice to use it.Best RegardsMarciN
Is there a way to restore a database backup file to the default data directory in a specific SQL 2005 instance using T-SQL?
I know that you can use the command RESTORE DATABASE, but that will restore the database files back to it original location. I also know that I can use the WITH MOVE option also, but is there a keyword or some special notation that I can use to make sure that the data/log files are moved to the default data directory for a specific SQL 2005 instance?
I am trying to avoid cases where the original file location does not exists in the current file system.
Hi everyone, I made a backup for my Bilgiler database and then by relying on this back up, I erased my Bilgiler data base. so now I tried to restore this Bilgiler database by using Enterprise Manager however I could not succeed. How can I overcome this ?
Hello, I am taking the error message which is in the below side,while restoring a database.(I am using sql server 2005) The backup set holds a backup of a database other than the existing 'db1' database. (db1 is the database name.) Can you help me?
Hi I have a .db file from another server and i am trying to install it at home and get this lengthy error message ( 9 lines long ) Microsoft SQL-DMO ODBC SQL STATE 42000 The physical name ( path blah blah may be wrong) how do i change the physical path of the .db file i'm trying to restore?? thx
Hi there, I am using VWD and have my production website hosted elsewhere. The hosting company provides a backup service that allows me to download a something.bak file as the backup of my website. My query is: how to export or copy the data in the something.bak file into the database on my own computer? Can anyone helps please? Thanks in advance.
m able to take backup and restore the database through gui. Backup is comming with backup query, but using restore query m unable to restore database.It is giving exception is REstore failed for server.what it means ? what is the solution for this?
Does anyone know if it is at all possible to restore a single database from tape if no backup jobs have been ran or setup for this database. However, the server itself has been backuped to tape. So I would like to restore this database from the server tape backup. If so, how do I accomplish this?
We are using sql server 7.0 for developing our applications.Recently we formated the disk and.But before doing so i just backed up the database(full backup using EM) alone.Then i restored the database after reinstalling sql server.All the objects are restored except the logins.So how to restore the logins.
I cannot create logins of same name and getting an error saying that the login already exists.
I have two servers (same config) I am trying to restore a database that I backed up on one server to the other. The method I used was as follows: I backed up the database to disk. Copied to the Disk of the second server. When I try to restore I get the following message. "The backup set holds a backup of a database other than the existing "database name" database. What am I doing wrong?
Hi, I am in a production database server. Data has been added and deleted via the web. So there is alot of traffice in and out of the database. How can I make a backup plan to make sure I am not lossing any input or deleted data . I am familiar with backing up a database in regular intervals, but I amnot sure if this is the best way in this senario.
How can I take advantage of the transaction log in restoring the database.
Iam planning to move the production server from one server to another. Can any one tell me how to do the migration of the master,model and msdb database. Help in this regard is highly appreciated.Is it ok if I do the dts for these three databases Thanks Naveen.
