SQL Server 2008 :: Restore Database From Raw MDF And LDF Files Of Old To New Computer
Aug 18, 2015
I have a client that has POS software called Restaurant Pro Express (RPE) from www.pcamerica.com
Their old POS computer had a hardware failure, but I was able to attach the hard-drive to another computer and recover the data. RPE uses a MSSQL database system. However, my client doesn't seem to make backups very often - the last one is dated January 5, 2015.
I was able to copy the C:Program FilesMicrosoft SQL Server folder over which contained the instance as well as all the data files - and has up-to-date information. The instance in the recovered Microsoft SQL Server folder was called MSSQL.1. I installed the RPE software on their new computer, and it too now has an instance called MSSQL10_50.PCAMERICA. The new computer is using MSSQL 2008 R2, while I believe the old computer would have been using MSSQL 2005.
Hi All I have Computer is setuped SQL Server 2000 and SQL server 2005 when I restore SQL Server 2005 it's OK but when it's not OK when I restore in SQL Server 2000. The Error as follows:
I have a database used in SSRS in which I get a backup daily and restore it to database "DATA"
The issue I come across is SSRS has a datareader user(ssrsuser) for access to the "report store" web where users get reports
access gets erased on each restore. Can I add something to my job to restore this user after each restore or keep the settings?
declare @Bakpath varchar(80) set @bakpath = 'C:EDataExtractedDataextract.bak' Use Master Alter Database [Data] SET SINGLE_USER With ROLLBACK IMMEDIATE RESTORE DATABASE [Data] FROM DISK = @bakpath --location of .bak file WITH REPLACE GO
Can I use a full and differential backup to restore to a point of time?
Or I have to use full and transaction log backups in order to do a point of time restore?
I found today when I tried to restore a db from another database at the point of time for example 3:10 pm, SSMS automatically select the full backup + the transaction backup that is done at 3:00 pm, but not select full + the differential backup I did at 3:12pm.
So I lost those records entered after 3:00pm.
I supposed it should use the differential backup and restore to 3:10. but it didn't.
UAT environment : SQL Server 2008 R2 SP3 Enterprise Edition
SANDBOX environment : SQL Server 2008 R2 SP3 Standard Edition
I have a database backup (.bak) that was taken from UAT environment that has CDC enabled on some tables. I want to restore that database into my SANDBOX environment which does not support CDC (because of standard edition). The restore process fails due to this incompatibility. Is there any way to restore without CDC (I dont CDC enabled on my SANDBOX; just my data from the backup) ?
I have one job for copy files from another server to my computer using Robocopy.but when I execute my job in sql server I get this error:
Accessing Source Directory 192.168.101.191Upload Access is denied. this is my job: USE [msdb] GO
/****** Object: Job [Test] Script Date: 02/04/2015 13:08:00 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 02/04/2015 13:08:00 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
[Code] ....
Should I create Credential and proxy?How can I do this?Would you mind giving me required scripts for this?
[XXXXX] Connecting to remote server XXXXX failed with the following error message : WinRM cannot complete the operation. Verify that the specified computer name is valid, that the computer is accessible over the network, and that a firewall exception for the WinRM service is enabled and allows access from this computer. By default, the WinRM firewall exception for public profiles limits access to remote computers within the same local subnet.
I have a client that has POS software called Restaurant Pro Express (RPE) from [URL] ...
Their old POS computer had a hardware failure, but I was able to attach the hard-drive to another computer and recover the data. RPE uses a MSSQL database system. However, my client doesn't seem to make backups very often
- the last one is dated January 5, 2015. I was able to copy the C:Program FilesMicrosoft SQL Server folder over which contained the instance as well as all the data files - and has up-to-date information. The instance in the recovered Microsoft SQL Server folder was called MSSQL.1. I installed the RPE software on their new computer, and it too now has an instance called MSSQL10_50.PCAMERICA. The new computer is using MSSQL 2008 R2, while I believe the old computer would have been using MSSQL 2005.
I am no DBA expert, especially when it comes to MSSQL. Is is possible to 'restore' the database from the 'raw' .mdf and .ldf files of the old computer to the new computer / database instance? If so, how should I proceed?
I am attempting to restore the database from within VB.NET application I am making the following 3 calls:
RESTORE FileListOnly FROM DISK = 'C:MyDatabase.dat'
USE Master RESTORE DATABASE MyDatabase FROM DISK = 'C:MyDatabase.dat' WITH NORECOVERY, MOVE 'MyDatabase' TO 'C:Program FilesMicrosoft SQL ServerMSSQLDataMyDatabase.mdf', MOVE 'MyDatabase_log' TO 'C:Program FilesMicrosoft SQL ServerMSSQLDataLDFMyDatabase.ldf', REPLACE
RESTORE DATABASE MyDatabase FROM DISK = 'C:MyDatabase.dat'
using SMO. This logic works fine with small *.dat files, however when using *.dat file of about 4Gb I get an error on the 3d restore database call:
ExecuteNonQuery failed for Database 'master'.
An exception occurred while executing a Transact-SQL statement or batch.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Operator aborted backup or restore. See the error messages returned to the console for more details.
ExecuteNonQuery failed for Database 'master'.
An exception occurred while executing a Transact-SQL statement or batch.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Operator aborted backup or restore. See the error messages returned to the console for more details.
The same program/logic also works fine when I use MS SQL 2005 and it runs fine from MS SQL 2005 Query Analyzer for both 2005 and 2000 databases. There seem to be only problem with MS SQL 2000 from within VB.NET. Anybody has any idea? I'd appreciate any response. Thanks
Hi,I use SQL Server 2005. In computer "A", I backup a database "AAA" to afile "AAA_Back.bak". When I try to restore on computer "B", error messageshows up, it try to find the original backup file path in computer "A".How can I fix this problem?ThanksKai
I have a backup that comes to me nightly in the format of XXXX_YY_MM_DD.bak where the date is incremented each night the previous night backup is deleted when the next days is added so in general . I have only one in that folder. I wanted to setup a restore to run nightly
RESTORE DATABASE [XXXData] FROM DISK = 'C:folderExtractedDataapp_XXX_backup_15_01_28.bak' --location of .bak file WITH REPLACE
I would like to do something like
RESTORE DATABASE [XXXData] FROM DISK = 'C:folderExtractedDataapp_XXX_backup*.bak' --location of .bak file WITH REPLACE
While I'm asking.. In case there is an older one left behind(which shouldn't happen) I saw something about "LATESTFULL" but think its a redgate command?
SQL Transaction replication, specifically SQL backup and restore Transaction replication. So Scenario,
S1 = Primary Server 1 R1 = T - Replication Server 1 R2 = T - Replication Server 2
So we have S1 replicating to R1, and we want to build another subscriber which is R2.
Can I take the Replicated Database from R1, backup it up, then restore it to R2, and create the publication/subscription?
Will that work? if not, is there an easier way to avoid the snapshot? the reason i ask this is because we do have replication snapshot, but takes long. One of my Colleagues stated he tried this, however replication made duplicate rows on R2, which is why he had to use replication snapshot.
New forum member here, and pretty new to SQL Server. My question is I'm having an impossible time trying to connect MS Access on Computer A to a database running on SQL Server 2k (SP3) on Computer B. Every time I try to create a new ODBC connection on Computer A, I get the dreaded Server Error 17 (SQL Server does not exist or access denied). I have no problems making the ODBC connection on the computer running SQL Server (Computer B).
My connection settings are as follows:
Computer A name = MAINDESKTOP Computer B name (contains SQL Server) = HOMELAPTOP Workgroup name = WORKGROUP
Server Properties:
Name = HOMELAPTOP Authentication = SQL Server and Windows Registered SQL Server Properties = Use SQL Server authentication, and I'm using a login name and password Server Network Utility = TCP/IP network protocol enabled, with default port 1433 Server Client Network Utility = TCP/IP network, default port 1433
Under "Security", I have created a new login using SQL Server Authentication called "bradley", and have assigned a password to the new login. I've noticed that when I open the login properties dialogue, there are always more ***'s in the password field than the number of characters I originally used, but I suspect that's normal. I have assigned a database called "Costume" to "bradley", and have verified that the permission is checked on the Database Access tab for user "bradley".
Under "DatabasesCostume", I have verified that there is a user called "bradley" with the same login name and user name.
No matter what I try, I can't get Computer A to connect to Computer B's SQL Server database. I have no problems with any other types of connections (pinging, internet, file transfers).
We use Netbackup for our SQL servers to backup and restore databases. I would like the service account used by Netbackup to have as limited permissions as possible. The account should be able to backup and restore a db without being able to read any of the content. Right now the account jobs fail if the service account is not in the sysadmin role.
I removed the account from sysadmin and limited it to dbcreator and public but the job fail.
How to setup an account so that people who know the service account password can't log in with that account and read db information?
I have a new problem with doing a restore of a number of databases using powershell. The script I'm using is based mainly on this one (Part 2 in particular): [URL] .....
The problem I'm having is around the RedgateGetDatabaseName function. My hunch is that its down to the different version of red gate and how sqlbackup works. Basically when the call is made to the function it is returning both the Database Name and the number of row's that the SQL command in the function has ran. I've tried to include a SET NOCOUNT ON at the start of the SQL command in the function but its still returning the now count.
I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:
1.Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past. 2. Restore the server with Oct 1 full backup with NORECOVERY option. 3.Try to restore to the point at Oct 5 12:00, with the transaction log.
But the restore fails and SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?
Hi,I need to know the way in which I can copy my sql server 2005 database from one computer to another ? Please I need a clear and complete explanation ,thanx .
I am working with one other person on a VS 2005 vb.net web project that accesses SQL Server 2005. Both the computers are connected and my partner can run the application on his computer from his VS 2005 but we are getting an error on our first databind to a gridview on the page we are trying to run the error is below A connection was successfully established with theserver, but then an error occurred during thepre-login handshake. When connecting to SQL Server2005, this failure may be caused by the fact thatunder the default settings SQL Server does not allowremote connections. (provider: Named Pipes Provider,error: 0 - No process is on the other end of the pipe.)
I check the properties of the SQL Server and the check box is checked that says allow remote access. I am not sure what to do.
I have a table with lots of xml files in one column(more than 1000), like this
1. <content xmlns:xsi="http://www.w3.org/2001/XMLSchema... 2. <content xmlns:xsi="http://www.w3.org/2001/XMLSchema... 3. <content xmlns:xsi="http://www.w3.org/2001/XMLSchema...each is big
I want to query some values for all to see the duration but now I can only query one of them
declare @bp xml select @bp=xml from bloodpressureohneschema ;WITH XMLNAMESPACES('http://schemas.openehr.org/v1' as bp,'http://www.w3.org/2001/XMLSchema-instance' as xsi,'OBSERVATION' as type) select * from( select m.c.value('(./bp:time/bp:value)[1]','date') as time, m.c.value('(./bp:data/bp:items[1]/bp:value[1]/bp:magnitude)[1]','int') as value from @bp.nodes('/bp:content/bp:data/bp:events') as m(c) )m
The number of files to retain for SQL Server Logs is set to 99. When I expand the SQL Server Logs node in SQL Server Mgt Studio it shows the current log file through Archive#49. The oldest archive file is dated 2015/05/26. If I select that archive in SQL Server Mgt Studio it shows me the details and entries of that archive file. Yet when I go to the directory on SQL Server for the log files there are only the 5 most recent files. I have searched for '.trc' files on the entire drive and have found no other files.
How can SQL Server Mgt Studio show archive files that have no corresponding archive file in the directory that is supposed to contain the log files?
I am trying to locate the mdf,log and bak files but I am not seeing folder (MSSQL10_50.MSSQLSERVER) the only folder I see there are 90,100,110 and Report builder.
I located the properties of one of the databases below right clicking the properties on SSMS, but when I physically go there, I don't find that folder.
What is the best method to restore a DBTest1 (with one .mdf and one .ldf) into DBTest2 (with one .mdf, multiple .ndf data files and with 4 filegroups associated with specific data files). I do not see how the one .mdf file (in DBTest1) can be separated into the other 4 filegroups (in DBTest2). This does not sounds like it is possible with Backup DBTest1/Restore to DBTEST2 or (Detach/Attach) because the underlying filegroup and file structure is different.
What method should be used to get the data and structure from DBTest1 (includes 1100 Tables and 550 GBs of Data) into DBTest2 (with 4 filegroups)? Is the following possible:
1) First, in DBTest2, execute a script to create tables/indexes on appropriate filegroups.
2) In DBTest2, use scripts to pull data from DBTest1 into DBTest2, for example INSERT INTO DBTest2.dbo.tables with SELECT FROM DBTest1.dbo.tables OR use SELECT/INTO DBTest2.dbo.tables FROM DBTest1.dbo.tables.
Or, is it possible to use the BULK INSERT or BULK COPY Options? Export/Import Wizard?
Does the Create Index step needs to be done after the data is loaded into DBTest2?
I need to use SSIS to connect to an FTP server. From there I need to download files to a local folder. I need to download only today's files and also on those files starting with Training or Recruitment. I have managed to set up tasks that copy all but I am having such a hard time writing a script using C# that will download using the filters.
I am developing the SSIS and stuck on copying specific files.
1. We receive CSV file to our drive on a daily basis.
2. The csv file name has the last 8 digits formatted with the yyyymmdd. For example, the file name might be abcdef_20150226.csv This means it will be our CSV file for today, Thursday, February 26, 2015.
3. There are a lot of files in this directory.
[URL]
What we would like to do:
Add the constraints (or expression) that will copy the files from this directory to another directory that have the date equivalent to Monday only. For example, the file abcdef_20150226.csv will not be copied because it is Thursday file. But the abcdec_20150223 will be copied to a new Directory because it is Monday.
