SQL Server Admin 2014 :: BAK File Does Not Show In Restore Window
Jan 10, 2015
I have a windows 8 pc that I just got and installed sqlexpress 2014. My buddy haw windows 7 and installed sqlexpress on his pc. We create a db on his pc, did a backup, copied the backup to my pc. In ssms I right click on "database" > restore database. click device and the button to find my file. I navigate to the folder where the file shows in file explorer but the .bak file does not show in ssms to restore from. This is probably a windows thing but I have don't know what to look at.
View 4 Replies
ADVERTISEMENT
Mar 21, 2014
My sql databases in SQL Server 2014 has the status "suspend" as I saw in SQL Management Studio. I can't restore to serviceable condition sql databases through standard procedures. I need to restore .mdf file.
View 9 Replies
View Related
Oct 19, 2015
is there a way to restore all file groups except one? example: Database A has 10 filegroups, but 1 of them is defunct, so i cant delete it and there's no backup for restore it.Can I create a new DB restoring the 9 good FGs from a database A's backup?
View 9 Replies
View Related
Jul 21, 2015
I have installed 2 node windows Fail-over clusters successfully. But QUARUM Configuration is not appearing in Failover cluster manager instead "Witness: Disk (Disk Cluster 4)". I have also configured quarum configuration from Quarum "Configure Cluster QUARUM Settings". I have attached the snapshots of windows cluster configuration. Is it the issue or not. I have not got any warning and error during cluster validation while installing Windows failover cluster. I am assuming it is okay and i can move ahead to installation of SQL Failover cluster setup.
Products used for installation in Virtual Machine:
Windows Server 2012 R2
SQL Server 2012 R2
Note: Service Pack is not installed.
View 2 Replies
View Related
May 12, 2015
Is it possible to make window authentication using Connection String.
Like
Data Source="Remote Host"; User ID="user1"; Password="Password"
Integrated Security=SSPI'
user1 have rights on SQL server.
Is it possible user2 logged in on different machine can access SQL server.
user2 does not have rights on SQL Server.
View 3 Replies
View Related
Jun 10, 2015
I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed. I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log. What are the steps to restore full database back and one day's transaction log file. Note: there is no differential database backup and transaction backup.
View 8 Replies
View Related
Dec 31, 2014
In Windows Server 2012. How do I do a System Restore to a previous restore point?I need to install the 64 bit and 32 bit Oracle Client Install for connections in SSIS and to create Oracle Linked Servers.
If you make a mistake it is not fun removing it. Sometimes it corrupts the machine and it is difficult to uninstall since there is not an Oracle Universal installer for Oracle 11g.If you install the 32 bit before the 64 you mess up the machine.how to create a restore point.
View 6 Replies
View Related
Jul 29, 2015
when execute the restore log command, in the messages window it shows how many seconds the restore takes, at the meantime, on the status bar, it also shows the seconds the command takes.
Two values are different and could be very different, please see below examples , restoring takes 1.8 seconds, but in total the command takes 4 seconds to complete, the other one is 8.1 seconds and 12 seconds.
What does SQL Server or Windows do after the restoring?
pic a:
pic b:
I did a xperf, I can see after the restoring is completed, sql server did garbage collect and log write, which just run very quickly, but storage is busy on reading the log file for nearly 2.2 seconds( 4-1.8), and 4 seconds ( 12-8.1) .
pic 1:
pic 2:
see pic 1 above, from 13 to 17, the restore operation is finished, but the storage jump to 100% active to do some reads, only reads no writes. zoom that period shows pic 2, it read 4096 (I don't know the unit size) for about 4 seconds, what does this do?
Data file, log file, backup file are no different drives, but all local drive, the interesting point is the read jumped after restoring, I tested it on different server, same result...
View 1 Replies
View Related
Feb 12, 2003
I'm not sure when it started, but...
It seems that very recently, the backups made during a nightly ArcServe tape backup procedure are showing up in the restore window on our SQL server. We don't want that! The application group makes it's own backup's to disk, and we want to see only them in the restore window.
(The tape ackup is a redudnant process, to be used if one of our on-hand disk backups are bad or non-existent, or if the DB needs to be recovered from an older date.)
What made the ArcServe files show up? How can we stop SQL server from thinking tyhat they are part of the backup set we are conccerned with?
Thanks.
MichaelGG
View 1 Replies
View Related
Mar 14, 2014
I have two databases like each other that one is the backup of another. Each DB have 2 filegroups. I want to replace one filegroup from one db to another. How do I do this? Or how do I backup and then restore?
View 3 Replies
View Related
Mar 23, 2015
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?
View 3 Replies
View Related
Mar 24, 2015
I have SQL 2014. When I try to restore a user database using SSMS GUI, the Restore Database Pop up box never pops up. This happens for any database on this server at any time. Sometimes I get the pop up, some times I dont get.
So I tried to click on Databases on Top and Restore Database, and then select the db that I need to restore from Drop down, then it shows "creating restore plan selecting backups" but it takes forever.
We have full backup and trn log backups every 30 mins. So is it trying to get all these backup files in the background causing this issue? If yes then how to overcome this?
View 4 Replies
View Related
Jul 29, 2015
I'm trying to figure out why this SQL Agent job keeps failing.
We used management studio, and connected as mydomainmyuser, and developed a script to take a backup file from a network share and restore it. It worked fine in SSMS under that login.
After we got it working , we created a SQL Agent job on the same server to run the script, and set the agent job to run under that account that we tested with.
This is the error message we got:
"Executed as user: mydomainmyuser. Create Database permission denied in database master'.
So, I gave that login the rights to Create Database and Create Any Database. Then the error message changed to:
"Executed as user: mydomainmyuser. User does not have permission to RESTORE database 'mydatabase'. [SQLSTATE 42000][ERROR 3013] RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. "
* I can't use SA for the job account, because the SA account doesn't have rights to see the network folder where the backup file sits, so it has to run under the domain account.
* The user is a member of the dbcreator role - and the serveradmin and sysadmin roles
* The user is a member of dbowner on the database I am trying to overwrite with the restore
* I have given the user the rights CREATE DATABASE and CREATE ANY DATABASE
The only suspicious thing I found was that it appears the server was renamed at one time. When I looked at the login in management studio, I was not able to change some of the rights. On the Securables page, it shows the server name as "MyServer-New", but the server name is "MyServer". It is a replacement, and I suspect that when they did the replacement they named it "MyServer-New", set everything up, then renamed it.
I found this post listed below, and ran the script (shown below), and it showed that the server name was MyServer and the ServerInstanceName is MyServer-New
[URL] .....
SELECT HOST_NAME() AS 'host_name()',
@@servername AS 'ServerNameInstanceName',
SERVERPROPERTY('servername') AS 'ServerName',
SERVERPROPERTY('machinename') AS 'Windows_Name',
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'NetBIOS_Name',
SERVERPROPERTY('instanceName') AS 'InstanceName',
SERVERPROPERTY('IsClustered') AS 'IsClustered'
I can't reset SQL until the next maintenance window to test changing the server name as outlined in the post.
Am I on the right track with the name change messing up permissions, or is there something else I need to check?
View 5 Replies
View Related
Jul 23, 2014
What is the best way to restore a database from a folder of backups (including full, diff and log backups) without using the backup history in msdb?
I have a restore process that restores all backups on a regular schedule in order to fully verify their integrity. To do this, I use the backup history in msdb on each server that I'm monitoring. I had a thought the other day that I would be in trouble if I lost msdb. Then my backup history would only be as good as the last backup of msdb.
What I'd like to do is read a folder of backup files and generate a restore script up to a specified time. Would I use RESTORE HEADERONLY to do this? If so, would I use PowerShell to traverse each file in the folder?
View 6 Replies
View Related
Feb 10, 2015
I have an issue to restore an encrypted backup via GUI.I can restore an encrypted backup on another instance using t-sql command, but cannot do the same using the GUI of sql server 2014. The message error is: No backupset selected to be restored.I have restored the master key from the instance where the encrypted backup was created on the instance where I want to restore the encrypted backup, then restored the certificate. I managed to open the master key. I have the following versions:
On the instance where the encrypted backup is taken:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
On the instance where I'm trying to restore the bakcup via GUI:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
View 9 Replies
View Related
Jul 6, 2015
For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.
Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?
View 5 Replies
View Related
Apr 27, 2015
In a server we had File Growth,And then We had to Add New Hard Drive And New File On It.And Now We have New server with a Huge Hard Drive.But all files remaind.Can I Reduce This files to One data file or not ?
View 3 Replies
View Related
Feb 26, 2015
my 4 GB .OST file is corrupted and i am not able to access my mails and contacts.
View 2 Replies
View Related
Nov 7, 2015
A log file size of a production database has been increase from 4gb to 150 gb initial size.Now i want to find when it will grow & how much it grow & which transaction is responsible for this.
View 6 Replies
View Related
Jun 25, 2014
I have a database it is 50 gb with hundreds of columns. I would like to choose a certain column and convert the data in it to .csv or excel file. How can I do that I am very new to MSSQL...
View 1 Replies
View Related
Jul 23, 2014
I had to reinstall my local copy of SQL a few weeks ago, which naturally overwrote the
msdb.dbo.sysmanagement_shared_server_groups_internal and
msdb.dbo.sysmanagement_shared_registered_servers_internal tables.
However I still have the local XML file that SSMS reads so I can still access the groups, I just get weird errors when trying to re-register my install as the new CMS. How to rebuilt those tables from the XML file or know of a way to repopulate?
View 3 Replies
View Related
Nov 10, 2014
We have one table where we store all documents in one of the column called "Doc" with varbinary(max) data type.
We want to download those documents from sql table to windows explorer and i wrote BCP in sql 2005. And things were fine.
The format file I used there looks like this,
9.0
1
1 SQLBINARY 0 0 "" 1 Doc ""
Now we are in 2014 and when I try the same code with same format file, it hangs in the middle. So I changed the file to 12.0 instead of 9.0 but still not working.
View 0 Replies
View Related
Apr 8, 2014
What is the use of creating manifest file in SSIS... because I was schedule a job and its not needed and manifest file then why we are creating this.
View 4 Replies
View Related
Jan 21, 2015
I was running an operation to shrink file/emptyfile a data file, and then remove it.
It blocked and caused a huge mess, I suspect on the removal part. But I want to confirm that the emptyfile completed (and that the engine isn't going to try to put more data in there for when I schedule the removal part again a week or more from now).
How does the engine know not to put any more data in there, and how long does that situation last?
View 3 Replies
View Related
Jul 7, 2015
While i execute dbcc sqlperf(logspace); I get following values.
Database NameLog Size (MB)Log Space Used (%)
master 16.17969 13.30275
tempdb 7.429688 61.7245
model 0.7421875 45.78947
msdb 5.554688 25.87904
distribution 2808.93 0.8172179
BANKDB 23438.87 48.20037
WSMIRSDB 109.7422 4.839111
For database BANKDB , Log Space used(%) is 48.83% and Log size is about 23438.87 where as my database size of BANKDB is 60 GB. FULL database and Log back is done every day night one time. My database is performing slow now.
Do we need to take log backup frequently like once a 1 hour so that Log space used will be less. Same query is taking more time to execute than before in same database is it because of log file has increased.
I do index organize and rebuild once a week and stats apply nightly.
Is it correct once log space size is increasing more than 10%. Do we need to take log backup?
View 4 Replies
View Related
Sep 1, 2014
Is there any way to restore a database of higher version to lower version.
E.g. I have created a database in sql server 2012, created some tables & procedures in that.I took Full backup of that database. Can I restore it to sql 2008r2 or any lower version.
I know direct restore is not possible, I have to use either import or export option or generating script,but i want to know is there any easy step to do so.
Vimal Lohani
SQL DBA | MCP (70-461,70-462)
View 4 Replies
View Related
Aug 3, 2015
Need to restore database,here's the scenario:
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
View 8 Replies
View Related
Feb 2, 2015
Database File Placement Layout? We are planning to implement a new SQL Server 2014 OLTP Database with a 1 TB Data file and 1 TB Log File. I am looking at the possible layout of the database files and trying to determine the best possible configuration. My knowledge/research tells me that items which need separate storage due to constant simultaneous access are:
Data files – should go on the fastest reading storage.
Log files – should go on the fastest writing storage.
TempDb – involves a lot of writing at the same time the data files are being read.
Indexes - (including full text indexes) - involves a lot of writing at the same time the data files are being read.
Also, are there any benefit to having multiple OLTP Database Log files? Because SQL Server writes to the log file sequentially, I do not see any advantages to having multiple database log files. In a SQL Server 2012 Class I took last summer, under “Determining File Placement and Number of Files”, it states “Use a single log file in most situations as log files are written sequentially.”
View 9 Replies
View Related
Apr 30, 2015
I recently installed standalone version of SQL 2014 Standard on my work computer. I used Access before but I want to use a SQL server instead.
We have a shared drive that a file gets deposited every day at midnight. I want to be able to get this file and import it to the server (its basically a list of names).
Here what I have done so far:
I created the database
Created the file and successfully imported data into it using the Import Data feature.
I saved the SSIS package
Scheduled an Agent Job for this package to run at certain time,daily
At first the jobs would fail with a Access is Denied. I added a user under Credentials with my network account ( have admin rights on the work computer).Also added a Proxy for the Credential user I made.
Jobs fail with a “Cannot open data file” error. I tried changing things here and there, but I can’t get it to work.
View 9 Replies
View Related
May 14, 2015
I have installed SQL 2014 (Evaluation Version) on testing machine. We want to import some excel files on database. I manually created one Test Database and now trying to import excel file. Import completed successfully but I am not able to see any table created as result of Import. I tried it 3-4 times and even restarted sql services but no luck.
View 3 Replies
View Related
Oct 29, 2015
usual way to check if file exists
DECLARE @File_Exists INT
EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupfile.bak', @File_Exists OUT
print @File_Exists
1
And if check folder, can use "nul", but it doesn't work for UNC path
DECLARE @File_Exists INT
EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupul', @File_Exists OUT
print @File_Exists
0
If use xp_subdirs like:
EXEC master.dbo.xp_subdirs 'serverBSQL_Backups'
If the folder doesn't exists, Msg 22006, Level 16, State 1, Line 3 xp_subdirs could not access 'ServerBSQL_Backups*.*': FindFirstFile() returned error 67, 'The network name cannot be found.'
how to check if UNC folder exists in Backup? in my code I want to check if the unc folder exists before doing backup, the unc path is retrieved from other table or backup history.
View 6 Replies
View Related
Dec 18, 2014
I want to setuo AlwaysON that requires Node and FileShare Majority Quorum settings..
I want to know how to configure File share for windows clustering ????
View 2 Replies
View Related
Sep 12, 2015
I have a database which gets refreshed on the daily by restoring 1 transactional file log, at the moment it is done manually but I'm trying to create an automation job which would get the transactional life file and restoring it.
View 5 Replies
View Related