Restoring SQL Server Databases From .mdf Files
Feb 14, 2006
I recently was given control of an SQL server that stop working.
We had no past backups of the database and the only file we had was the .mdf
I decided to install a fresh version of SQL 2000 on another server and created a database using enterprise manager with the same name as my database called SWATraining
I then stop the sql server
the first thing that I notice is that the .mdf that enterprise manager created had _DATA at the end of the name. Thus I renamed the the orginal .mdf SWATraining_Data and
copied it to the
C:program filesmicrosoft sql servermssqldata
when I started SQL the database was greyed out and had (suspect)labeled
How can I recover the database when the only file I have is the .mdf file??
I even tried
sp_attach_single_file_db @dbname = 'SWATraining',
@physname = 'C:Program FilesMicrosoft SQL ServerMSSQLDataSWATraining.mdf'
but reason this error
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'C:Program FilesMicrosoft SQL ServerMSSQLDataSWATraining.mdf' is not a valid database file header. The FILE SIZE property is incorrect.
View 4 Replies
ADVERTISEMENT
Feb 23, 2015
I am working on a task. Currently we are taking a database backup and keeping that backups in a folder. The backups doesn't have time stamp on it. My task is need to get the latest backup and copy that backups into some other server and then restore the database from there.I am planning to create SSIS package.Do we need script task for this task.How to get the .bak with latest create or moidified date. For now we doesn't have timestamp so need to go based on modified date?
View 9 Replies
View Related
May 1, 2015
I was contacted by the SAN team to test backup/restore of larger databases using a split-mirror backup (BCV) or clone that is taken from production db server and copied to another sql box. They want to use this process once a week. I see the mounted drives with the data/log files. All looks good. Initially I attempted to attach the databases and received (Unable to open the physical file db.mdf Operating System Error 5 Access is denied). I manually granting SQLServerMSSQLUser$<computer_name>$<instance_name> on all of the physical files 20 total. That worked.
Since this will be weekly, the SAN team performed the copy again and now none of the databases can communicate with the newly copied files. NTFS permissions need to be set again. I'm getting (Operating System error 21: the device is not ready). Is there something that I'm missing in this process how the vendor BCV clones the data and SQL communicates with the copied files as I was thinking it would be more automated process?
View 0 Replies
View Related
Mar 11, 2004
Hi
I have another question : 1) Restoring SQL Server Databases after uninstalling and reinstalling SQL Server.
Two days ago there was some problem with Norton Antivirus, so Windows 98 restored itself to a previous state. The SQL Server name was not available when I tried to start again, so I had to uninstall and reinstall SQL Server. I started a new Database just a week before this happened (the change from Access to SQL Server as I stated in the other thread http://www.dbforums.com/showthread.php?threadid=987880) and after reinstallation I could not access the Database any more - even while it is in the Data directory. I did not have any backup to restore from - I just started creating tables, so there was only the Log file created and the MDF database file. I tried to import it for a while to the new server, but as the database is not shown in the new server, it was unable to import like I hoped. So how do you restore it back to SQL Server when you have no backup and only the (undamaged) MDF database file if you only have one server? I had to recreate the Database from scratch again - I don't want it to happen again.
Regards
Corné
View 4 Replies
View Related
Dec 1, 2014
Until yesterday I had a server running SQL Server 2008 R2 - with all the SQL Server DB files on an attached disk array.
The server died - so I attached the disk array to a new server - and all the DB data files are visible there.
I installed SQL Server 2014 on the new server and am trying to work out how to point it at the existing database files.
I also have backups of the DB's - but they will take ages to copy over and restore - so it would be much easier to just use the db files. Should I restore the master db first (easy as its small)?
View 9 Replies
View Related
Mar 5, 2007
Alright, here's the deal. I'm testing some backup/restore strategies, and hitting a (slight) sticking point.
We've got collections of database and log backups created by the usual maintenance plans on a 2000 Enterprise machine. I'm trying to run through a restore onto a new 2005 machine (Developer Edition on my test workstation) using the collection of .bak and .trn files copied from the 2000 server. When I try to restore to a new database on 2005 via SSMS, and select all the .bak and .trn files for the restore, I get the ol' "The volume on device '[trimmed]' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set." error.
I'm assuming this just means that SQL Server can't verify that these log backups are in fact part of a functional "set", even if they aren't part of a traditional backup media set. Is there any way to tell SSMS, "It's okay man, just restore the database from these files, in this order - trust me," or is the only solution restoring every individual log file one at a time? (Which seems to work fine, though is a tremendous pain with any more than a few log backups.) Seems like there ought to be a good one-shot method to restore a bunch of backups to a different server, and I'm just not finding it.
View 4 Replies
View Related
Mar 25, 2013
i wanted to test routines using my local sql server engine and thought I could backup the mdf ldf file on the true network sql server in which I am listed as a dbowner with full permissions. In fact i can add users for the at db etc.The backup allows me to browse to a local folder
with the following being the default C:Program FilesMicrosoft SQL ServerMSSQL10_50.CMS_PROJECTMSSQLBackup
i name the file and it executes properly but when i try to restore that file to my local db that path is not even visible to me.In fact when i use exploreer I cannot even find the folders after Microsoft SQL Server!I search for .bak but nothing.How can I see that folder construction in the sql browser but never in explorer -
View 2 Replies
View Related
Jul 9, 2015
I want to control the size of ldf files and mdf files of several databases on SQL Server 2008 in my organization (manual increase), but i have a question:
What would be the best practices (best methods) for provisioning a ldf file and mdf file? Exists any generic formula?
With this i want to avoid the shrink operation and the autogrow of sql server databases...
View 6 Replies
View Related
Nov 28, 2000
I am fairly new to SQL Server and have a question about restoring a database . If a server that contained SQL Server was totally destroyed, and we have backups stored on an external server, how do we restore these backups. I am assuming I would have to install a new instance on the server we buy to replace the destroyed one. Once installed how do I convince or tell SQL Server that there are backups out there. I could move the archived backups back to the new server, but how do I point the new instance of SQL Server to these backups? Thanks in advance for any help.
View 1 Replies
View Related
Jan 10, 2007
Guys,I need to restore a database which is 400 GB plus. Usually a normalrestore from a backup file takes about 40 minutes. I have a databaseCompany on a server A. I need to use the backup of database company torestore as company1 on the same box. At the end, company and company1would reside on the same box. Is there any way to do it faster than anormal restore???Thanks in advance.RegardsJaideep
View 3 Replies
View Related
Nov 15, 2007
I try to restore a database but I receive the message "...the database is compressing. Uncompress the database and try again..."
In the original system (MSQL 2003) the backup is doing by the utility "backup"
In my personal system (MSQL 2005) I use the restore utility
Notice that I don't use winzip, winrar or other compressing software
Can anybody help me?
Thanks in advance
View 3 Replies
View Related
Feb 9, 2007
Hi,
Hopefully someone can help me with this.
When I downloaded the files and after burning the DVD to create the DVD to setup the 7031A - Upgrading to Microsoft SQL 2005 course, all of the executable files (BCBase.exe, Upgrade.exe, InPlace.exe, SideBySide.exe and SQL7Upgrade.exe) are not in the Setup/Drives folder...the only item in that folder is a file called "placeholder.txt".
Please, if anyone knows how to get these exectuables let me know.
Thanks,
David - EduCorp Training & Consulting
View 1 Replies
View Related
Jun 17, 2002
I would like to know if its OK to restore a SQL 7 user database to SQL 2000.
I have looked into Books On Line, with no success.
Anyone know where I might find some documentation on this topic?
Many thanks in advance.
Gary Andrews
View 1 Replies
View Related
May 15, 2015
We have multiple databases on a single instance in an OLTP environment. I have my data files on a separate SAN LUN from my transaction log files (and a few NDFs split out onto additional LUNs). I was wondering if there is a performance benefit to putting each LDF file on its own LUN? Or at least my few busiest LDFs?
We are currently on 2012, but I'm having to put together specs for a 2014 installation and need to answer this question without having an environment in which I can benchmark different setups. I just want to hear whether or not others have done this (why or why not?).
View 3 Replies
View Related
Oct 24, 2013
I have a single .bak file containing full backups of 20+ databases.
Whats the best way of creating and restoring these database onto an instance on a separate server.
note that this is to migrate the whole instance of sql server onto a newly build server.
View 1 Replies
View Related
Jun 20, 2007
Hello:
I'm working with two sql servers instances, ServerA and ServerB, which run under two different service accounts on different machines. They both have a database, DatabaseA, that has some encrypted fields.
If I take a backup of DatabaseA on ServerA and restore it on ServerB, I need to re-encrypt the Database Master Key (DMK) with the Service Master Key (SMK) as follows:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pwd used to encrypt DMK'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
However, if I again take a backup on ServerB and then restore it on ServerA, I can use the DMK on ServerA without re-encrypting it with the SMK.
Shouldn't I have to re-encrypt the DMK with the SMK everytime I restore from a backup that was generated from a different server?
Thanks,
Cyndi
View 1 Replies
View Related
Jan 22, 2004
Hi
In order to copy a database from a server to a server, I cant detach the database as there are many users connected to it. The transaction log is large file while the data is about 46 MB, I made a backup for the data file only, zipped it and copied it to the target server.
Ex: the name of the source DB on the source server is x, the file I took as a backup for is x-data
From the Enterprise Manager, right-click the x DB, All tasks, Backup database, in the backup option click the File and Filegroup option and chose the primary file group with x_data file.
Then, I created a new database name it y with y_data , y_log files on primary filegroup. I want to restore my backup to have the DB x on my target server,
I tried:
RESTORE DATABASE y
FILE = 'y_data',
FILEGROUP = 'primary'
FROM disk= 'E:VMSDBFiles est'
GO
But it did not work. Any help?
Thanks,
View 1 Replies
View Related
Mar 22, 2008
Hello,
Does anyone else ever experience this problem, and if so do you know what causes it:
When ever I restore a database called say XXX.mdf, it restores OK and I can view via MSE as XXX.mdf. Then when I visit MSE again there is another database called C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataXXX.mdf - both are linked to the same .mdf file, however if you try to expand XXX.mdf nothing happens as though the database has been detached?
Cheers MArco
View 3 Replies
View Related
Jul 3, 2006
I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'
CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.
All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.
I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.
I then try the following commands, which I used when I brought the development database over to the test box the first time:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'
This caused the following error:
"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."
So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.
View 12 Replies
View Related
Jan 16, 2008
I have a backup that contains multiple databases. I am restoring it to a different server. How do I restore each database? Tried to restore under SQL Server Management Studio, but can only restore one of many from the backup (it appears it has only 1 logical file name in the backup) If you can refer me to a web page that would be great. I tried googling the topic but nothing seems to come up.
View 5 Replies
View Related
Dec 27, 2007
I have a question regarding moving databases within the same server but to a different drive. Using the 'Alter Database' query transaction procedures within the SQL Server Management Studio, I'm able to take a user database offline and move it. However, when using the Alter transaction to bring the database back online, I get errors. There was also an attempt that seemed to work, but the app that uses the database through ODBC can't see the database that was moved and errors out.
How can databases be moved and connections re-established within the same server?
Thanks...
View 1 Replies
View Related
Sep 15, 2006
Hello,
I would like to restore SQL Server Express and its databases from a tape backup to the same server. This is a disaster recovery senario.
I backed up the Master, Model, MSDB and my own test database using SQLCMD scripts. I have no problem restoring these using task scheduler on the server before the disaster recovery.
However, in my real disaster recovery testing, When the server is restored by tape drive (HP one button disaster recovery), I try to run my SQLCMD restore scripts in task scheduler and I cannot connect to the sql server. Also I cannot connect with Management studio. I have recieved the following error in event viewer.
----------------------------------------------------------------
Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: (2)
Event ID: 3411
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
Configuration block version 0 is not a valid version number. SQL Server is exiting. Restore the master database or reinstall.
Data:
0000: 53 0d 00 00 15 00 00 00 S.......
0008: 16 00 00 00 43 00 4f 00 ....C.O.
0010: 50 00 4c 00 45 00 59 00 P.L.E.Y.
0018: 4e 00 45 00 57 00 53 00 N.E.W.S.
0020: 5c 00 53 00 51 00 4c 00 .S.Q.L.
0028: 45 00 58 00 50 00 52 00 E.X.P.R.
0030: 45 00 53 00 53 00 00 00 E.S.S...
0038: 00 00 00 00 ....
Event Type: Warning
Event Source: SQLBrowser
Event Category: None
Event ID: 3
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The configuration of the AdminConnectionTCP protocol in the SQL instance SQLEXPRESS is not valid.
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The SQL Server (SQLEXPRESS) service terminated with service-specific error 3411.
C:Program FilesMicrosoft SQL Server90ToolsBinn>sqlcmd -S.SQLExpr
COPLEYNEWSDATABASEscriptsMASTERFULLRESTORE.sql"
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred whi
shing a connection to the server. When connecting to SQL Server 2005,
re may be caused by the fact that under the default settings SQL Serve
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
----------------------------------------------------------------------
My question is, what is the correct procedure to follow when I want to do a disaster recovery and restore SQL Server Express from tape backup using the Simple Backup method and scripts.
Is it always required to reinstall sql server express from the original program file or is it possible to reinstall from back up tape.
I know my backup and restore scripts work because I tested them on the server before I do the disaster recovery and rebuild that server from tape.
This is some kind of issue with SQL Server Express being restored by tape backup.
Any suggestions, thanks.
View 5 Replies
View Related
Jun 28, 2000
Hi All
I have taken ownership of a SQL 7 Server that has many DB's with each DB's devices spread all over the 6 disks that exists in the Server.
e.g.
DB - Reference has
Data devices on D (20mb), E(40mb) , F(60mb) drives
and Logs having a similar spread.
Now what I want to do is to restore a full backup of the database into a new db but with only one data device and one log. So that basically the server is tidied up. The reason the devices are spread is not due to performance reasons.
The QUESTION IS : How do I restore a full back up of a db with many devices in to a NEW db with only 1 data device and 1 log device.
Thanks
Steve
sfarmer@edfman.com
View 2 Replies
View Related
Jun 14, 2007
Hi All,
Are there any MDS Descriptor Files associated with Databases Files? Are these associated with transaction logs? As per my information there are .mdf , .ndf and .ldf files.Could anyone please guide me ASAP,as there is an urgent requirement from one of the clients?
Thanks in Advance
Gaurav Matkar
View 5 Replies
View Related
Aug 7, 2000
Hi,
I restored the data files .MDF , .LDF files from my nt backup. Now i want to apply these file to a database. How i have to apply?
Do i need to create new database and attach these files or how i have to do?
I have no idea, how i have to work on it?
pls suggest me.
Thank you everyone.
--Devi
View 1 Replies
View Related
Dec 6, 2007
Hi,
I'm making backups of the database by first making a full backup and then differential backups. The differentials are backed up to separate files.
Restore of the full backup works fine, but I can't restore a differential backup. In Management Studio Express, I first do a full backup restore with option NO RECOVERY and then try to restore a differential backup. But this failes with the message:
"This differential backup cannot be restored because the database has not been restored to the correct earlier state."
Is it possible to restore a differential backup that is backed up to a separate file?
View 8 Replies
View Related
Nov 25, 2006
I have some bookkeeping files that were saved using win98 backup that i need to restore to my accounting program on my winxp computer.. i have no idea how to do that. The files that are backed up belong to the same program on both the win98 and winxp.
View 1 Replies
View Related
Sep 18, 2006
Hi all.
I don't know if this is the right forum to post this question, but here it goes.
We have restored into sql 2005 the database backups made in sql 2000. We connect with trusted connection and application roles, and when trying to execute a transaction to another database (with the guest user), we get a permission error.
Does anyone know if, apart from restoring the databases, we should do something else to get the guest user working the same way as with sql 2000 in the restored databases?
Thanks in advance for your help.
View 1 Replies
View Related
Nov 19, 2007
Hi,
I am cleaning database backup history as "Delete history if 4 months old" for backup file, and "Delete history if 1 week old" for Log file back.
lets say, I have disaster after 3 months, and I recover database with data and log backup files. Will I be able to recover with 3 month old backup file with just 1 week Log file backup. As I am keeing my Log backups only for 1 week.
Thanks,
View 5 Replies
View Related
Feb 7, 2005
I like to move my database from a directory to another (on different drives foir exemple).
Is there an easy way to do this kind of job ?
View 3 Replies
View Related
Jun 15, 2007
Hi, I've created a new filegroup, then added to it a new filename, then create a table pointing to this filegroup. So far everything is ok, but if I want to revert the process in this way:
1- Drop the table - OK
2- Drop the filegroup - OK
3- Drop the filename - ERROR: Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized.
When I query a catalog view with this query:
Select * From Sys.Database_Files
I get the file that I had recently deleted, it is offline but I can not delete it using the ALTER DATABASE instructions.
This is the code I use:
Use [TESTING ]
Go
-- Add a FileGroup to the Database
Alter Database TESTING Add FileGroup FG01
Go
-- Add a file to a FileGroup
Alter Database TESTING Add File (
NAME = TESTING_DATA01,
FILENAME = 'D:Sql ServerDataTesting_Data01.ndf',
SIZE = 1 MB,
MAXSIZE = 10 MB,
FILEGROWTH = 1 MB
) To FileGroup FG01
Go
-- Create the table using a specific FileGroup
Create Table TABLE1 (
Id Int Not Null,
FirstName VarChar(30) Not Null,
LastName VarChar(30) Not Null,
BirthDate SmallDateTime
)
On FG01
So far everything works ok, but in the next code there is an error:
-- Delete the table
Drop Table TABLE1
Go
-- Remove a FileGroup from the Database
Alter Database TESTING Remove FileGroup FG01
Go
-- Try to remove the file
Alter Database TESTING Remove File TESTING_DATA01
When I try to remove the file there is an error:
"Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized"
View 2 Replies
View Related
Sep 8, 2006
Is there some sort of command that can tell me which mdf and ldf files are associated to which databases?
Thanks,
Ninel
View 1 Replies
View Related
Jun 26, 2006
Hi,
I have some doubts about Shrinking databases or files.
while shrinking a file, I learned that we are alllowed to shrink more than the minimum size of the file, does not it bring damage to the data in that file ??
View 13 Replies
View Related
