Guys,
I need to restore a database which is 400 GB plus. Usually a normal
restore from a backup file takes about 40 minutes. I have a database
Company on a server A. I need to use the backup of database company to
restore as company1 on the same box. At the end, company and company1
would reside on the same box. Is there any way to do it faster than a
normal restore???
Thanks in advance.
Regards
Jaideep
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 RelatedI 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
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
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.
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.
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
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
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.
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
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...
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.
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 RelatedI 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?
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.
I believe it is possible to restore databases from Enterprise edition to an enviroment with Standard edition. We have been able to do it on databases without any partitioned tables. But if a database has partitioned tables it will not start up in the Standard edition after a successful restore. The error log states that the database will not start because partitions are not allowed in Standard edition (which we knew). But we were led to believe that the databases would restore and open fine, the partitions would just not be there in Standard edition. Are we possibly doing something wrong or will this just not work?
Thanks
Chris
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é
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.
Any advice on how to get this thing finished out?
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.
When i tried the sample on a newly created database it worked fine.
Is there any solution to make the restored database to work for service broker.
Thanks
Prashanth
hi from France !!!
i would like how to duplicate a database to another server with all datas, constraints, keys, indexes...
should i use sp_attach_db, dts, backup/restore, sql scripts... ???
thanks to all, nico
Hi, I am restoring a database as a complete step. The database icon, under the Sql server Group in the Sql server enterprise manager, turned into gray and got a word (loading) infront of the database name, what does that means?
Ali
I am just curious to know whether it's possible to restore a SQL 6.5 DB from a DB device file. For e.g. i have a device file abc.dat which contains a db. Now thru this device file can i restore the data on xyz database.
View 3 Replies View RelatedHi All,
I'm trying to restore a database to a completely new database from an exsiting full backup.
On the backup device there are two full backups from different dates.
How can I specify which one of the two backups I want use for my restore (The only difference is the date ??
Thanks,
I had SQL win2k running on a machine and had 2 dbs. unfortunately the hard drive failed (ntldr crashed). i was able to copy the contents of from the old drive (so i have the DB files) but no backups.
Is there any way to import this data into a new DB?
Can you restore a backup (bak) file to a different server? E.g. client gets server nicked, they have bak files, can I run a restore to my sql server?
I can see the with MOVE option, but I can't see how to attach (to the new server) if the database wasn't detached (from the nicked server).
i am getting the following error when i try and restore a datbase
The log in this backup set begins at LSN 110000000011300001, which is too late to apply to the database. An earlier log backup that includes LSN 102000000188100001 can be restored..
while iam restoring my back up i got this error message
Msg 3117, Level 16, State 1, Procedure res, Line 26
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Procedure res, Line 26
RESTORE DATABASE is terminating abnormally.
Hey all.Can anyone point me in the general direction of documentation whichwould tell me how to restore a DB via the command line instead of usingenterprise manager?ThanksMatt
View 2 Replies View RelatedHi all,I have a very old MSSQL data backup file.I do not even remember that it was created in SQL7 or 2000.Anyway, I'm trying to recreate DB from this backup.Is it possible to create DB from backup file?I tried restore from that file and SQL server complaining."The file on device '***' is not valid Microsoft tape format backup set."Thanks-Jay
View 2 Replies View RelatedHi there,
I've got a .mdf/.ldf database/log. I'm trying to bring it into SQL Server Management studio, but can't figure out how. I've gotten as far as "Attaching" it, but can't figure out where to go from there. Can anyone help me out. I know this is very basic.
Thanks.
Hi,
I have created a DB on my local machine.
Now I would like to copy this on to another machine.
I stopped the service and then copied both the MDF file and the
log files into the same folder as in Data folder of the second machine.
When I run theStudio express I cannot find the MDF file.It does not show up in the
the DB folder or window.
When I try to create one it says that the DB exists.
What am I missing?
I tried doing a backup and restore but then the DB is not created on the second machine and
hence wont work.
Tnx
i have sql server 2005 dev installed on a laptop. i have 2 .bak's from production sql server 2005 database. i went through the gui restore procedure and both backup files completed successfully. now both databases are in the (Restoring...) status and have been for quite some time. i have sp2 applied. when will these become available? each .bak was around 400 mb. it has been a few hours.
View 8 Replies View Related