SQL Server Admin 2014 :: Restore Filegroup From One Database To Another

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


ADVERTISEMENT

SQL Server Admin 2014 :: Restore Database From GUI Takes Long Or Never Pop Up

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

SQL Server Admin 2014 :: Restore Database Permission Failure

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

SQL Server Admin 2014 :: Restore Database Without Backup History In MSDB

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

SQL Server Admin 2014 :: Adding New Partition To Filegroup

Nov 28, 2014

I work for a 24/7 shop. We currently have a table that is partition on monthly. I have to created a script that will add a new file group, add the new file to the group, and alter the the partition scheme and function. However, I need for this process to not cause a lock on the table. Typically I get the locking and issues when I am run the split command. Is there a way to prevent this from happening?

View 4 Replies View Related

SQL Server Admin 2014 :: Separate Max Fields From The Others To Another Filegroup?

Jan 18, 2015

we have many max fields in our database .

A role in performance says : It's better to separate this fields to another FileGroup with separate file.

Is the result good for performance? and what are the risks?

View 9 Replies View Related

SQL Server Admin 2014 :: Filegroup By Table Category

Oct 14, 2015

I'm being asked to create multiple filegroups for a new database based on the table type, transaction, lookup, misc... From what i'm reading this doesn't make sense. I'm reading either large tables get file groups, nonclustered indexes when they are about the same size of the data, or a few other reasons...

First of all, we are talking about the same disk (please don't ask me about how it is configured) and I'm not sure yet if restoring separate file groups is even going to be necessary.

So here are my questions (beyond, the test and see what happens) because in the end I'm going to probably have to do what i'm told. So this is for my professional knowledge.

1. Does file groups separated by table type make sense?
2. Should you put tables that are queried often together in the same or different file groups.
3. I'm pretty sure you can't restore single file group for write access, am I correct?

View 0 Replies View Related

SQL Server Admin 2014 :: How To Find Used Space In Memory Optimized Filegroup

Jun 11, 2015

How do i find Total allocated space and used space of a memory optimized filegroup?

use memory_optimized_db
Go
select (SUM(size)*8.0)/1024.0 as Space,
FILEGROUP_NAME ( data_space_id ) , type_desc from sys.database_files
group by data_space_id,type_desc;

above query gives "current used size of the container " of memory optimized file group but doesn't give Total space detail.

View 0 Replies View Related

SQL Server Admin 2014 :: Move Text Data From Primary To New Filegroup?

Oct 15, 2015

I need to modify a table to reside on a new filegroup and also point TEXTIMAGE_ON to that filegroup instead of PRIMARY. Apparently in the past, the only way to achieve this via SQL is to create a new table, copy over data, drop the old table and rename the new table to the original name. I found this solution in the SQL Server 2005 forum.

Is there any other way to alter this table in order to point the TEXTIMAGE_ON to new filegroup using SQL Server 2014? We are on Standard edition. The technique I am using is the drop constraint (with move option) and add constraint (to new filegroup) commands. The data and indexes move, but not the text data (it still is in primary filegroup).

View 0 Replies View Related

SQL Server Admin 2014 :: Primary Filegroup For System Objects / Secondary For Data

Jul 27, 2015

I have been creating databases in SQL 2008 with a primary filegroup for the system objects and a secondary, marked Default, for the data.

We are preparing a migration to SQL 2014, and the administrator is complaining he won't adopt this structure on the new servers because 'there is no benefit' and 'a backup cannot be restored (!?)'.

View 2 Replies View Related

SQL Server Admin 2014 :: How Data Writes In Multiple Secondary Files If There Is No Filegroup Created

Nov 12, 2014

I read , When sql server Database having multiple data files within single filegroup then sql server writes data in multiple proportional file algorithm where the amount of data written to a file is proportionate to the amount of free space in that file, compared to other files in the filegroup.

so if there is no filegroups created and multiple secondary files are attached in databse , is there same way data stored and writes data in multiple files by the same algorithm or any different way.

View 2 Replies View Related

SQL Server Admin 2014 :: How To Do System Restore To Previous Restore Point

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

SQL Server Admin 2014 :: Restore A Database Of Higher Version To Lower Version?

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

SQL Server Admin 2014 :: How To Restore MDF File

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

SQL Server Admin 2014 :: What Does Restore Log Do After The Log Is Restored

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

SQL Server Admin 2014 :: Way To Restore All File Groups Except One?

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

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 View Related

SQL Server Admin 2014 :: Restore DB1 To DB2 (with Different Filegroups And Files Structure)

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

SQL Server Admin 2014 :: Restore Encrypted Server Backup Via GUI

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

SQL Server Admin 2014 :: Restore Lost Transaction From Transaction Log File

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

SQL Server Admin 2014 :: Restore DB With Full Backup And Transactional Log Backup

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

SQL Server Admin 2014 :: Restoring A Database Even If No Database Or Backup Encryption

Sep 3, 2014

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.

View 6 Replies View Related

SQL Server Admin 2014 :: Cannot Export All Data From Database

Dec 4, 2013

And have chosen the destination - unstructered (flat) file. But the wizard proposes to export only one table (dbo.Acocount) and all the others from the list are not exported. How can I export ALL the data into one file.I need to do this to edit the syntax in the editor and then import this data and database structure into Postgresql

View 4 Replies View Related

SQL Server Admin 2014 :: Logins With No Database Permissions

Nov 3, 2014

Query to show logins that don't have any permissions within the SQL instance? I'm tasked with doing some cleanup and have found some cases where the database was deleted or moved to another server but the logins that used it were not deleted. I'd like to identify them to research.

For instance a query to show logins that have no permissions in any of the existing databases would be handy. I'm thinking it would be complicated by the need to loop through all of the existing databases and then outer join it to the list of instance level logins. Going to try to write something like that but was hoping that a script already exists.

View 3 Replies View Related

SQL Server Admin 2014 :: Mirroring Database Limits

Nov 22, 2014

I have multiple SQL 2008 severs with databases. Also, 1 mirroring server in place.

Since my database count is increasing can i have only 1 mirroring server. Is there any limit of db at mirroring server. I would have approx. 150 databases.

View 4 Replies View Related

SQL Server Admin 2014 :: Rename A Live Database?

Jan 31, 2015

I want to Replace The Big Log database with A new one ( A database with same structure).But current DB has many connection .

This is my plan :

1- Create a new database with same structure.

2- Rename current database to olddb with this code :

USE master
GO
EXEC sp_dboption CurDataBase, 'Single User', True
EXEC sp_renamedb 'CurDataBase', 'OldDataBase'
GO
3- Rename Newdb to current DB.
USE master
GO
EXEC sp_renamedb 'NewDataBase', 'CurDataBase'

is it true ? and Tsql code is ok ? (dont forget many of connection to curdatabase (that Is a log db) and loss some seconds data is not problems)

View 4 Replies View Related

SQL Server Admin 2014 :: Database Went Into Suspected Mode

Apr 13, 2015

My database went into suspected mode. and after we had run some script, it came out from the suspected mode. but we encountered this error while opening table in database.

2009-11-02 15:46:42.90 spid51 Error: 824, Severity: 24, State: 2.
2009-11-02 15:46:42.90 spid51 SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:43686; actual 0:0). It occurred during a read of page (1:43686) in database ID 23 at offset 0x0000001554c000 in file 'H:MSSQL.SQL2008MSSQLDATAmy_db.mdf'.

Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

View 9 Replies View Related

SQL Server Admin 2014 :: Send Database Mail Only Once

Apr 14, 2015

I have a job under the SQL Server Agent, which is configured to send an email notification on failure.

The job is scheduled to run every 30 minutes.

Is it somehow possible to configure it so that it only sends one email in case of subsequent failures instead of "spamming" my inbox every half hour?

View 4 Replies View Related

SQL Server Admin 2014 :: Copying Database Failed

May 11, 2015

I tried to copy db from server to server by sa user ( sql login) but this error raised and the copy failed

Executed as user: NT ServiceSQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 9:55:24 AM Progress: 2015-05-11 09:55:24.45 Source: 10_32_0_201_10_32_0_202_Transfer Objects Task Task just started the execution.: 0% complete End Progress Error: 2015-05-11 09:56:31.87 Code: 0x00000000 Source: 10_32_0_201_10_32_0_202_Transfer Objects Task

An error occurred while transferring data. See the inner exception for details. StackTrace: at Microsoft.SqlServer. Management. Smo. Transfer. TransferData()The Execution method succeeded, but the

[code]....

number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:55:24 AM Finished: 9:56:32 AM Elapsed: 67.892 seconds. The package execution failed. The step failed.

View 8 Replies View Related

SQL Server Admin 2014 :: Virtual Log Files For Database

May 20, 2015

Is there a better way to deal with the virtual log files?...I see several approaches in dealing/decreasing the virtual log files for a database..want to know what's the best n safest approach, from the masters here?

View 9 Replies View Related

SQL Server Admin 2014 :: Create A Master Key In The Database

Jun 15, 2015

in my environment I am running the SQL Server agent job.i am getting below message.

create a master key in the database or open a master key in the session before performing this operation” error 

View 5 Replies View Related

SQL Server Admin 2014 :: Error - Cannot Drop Database Because It Is Currently In Use

Oct 29, 2015

I have an environment with MS-SQL Server 2014 and always-on availability group configured (on 2-nodes).

I'm writing a Powershell Script which removes the database from the availability group (on the primary server) and then SHOULD drop the database on the secondary Server.

That works most of the time, but not always...

When it fails I get the error message:

Cannot drop database "Customer_2" because it is currently in use.

When i check the secondary DB-Server (sp_who2) while the script is running, i see that there is a process for the DB "Customer_2" with Status="background", Command="DB STARTUP" and LastWaitType="REDO_THREAD_PENDING WORK".

As soon as the script fails, this process for "Customer_2" disapears.

This happens always only on the second database in the availability group.

Why is the process still there, even after I removed the database from the Availability Group on the primary node.

If I remove the database from the availability group manually, the "background" process on the secondary node for that database disappears..

[URL]

View 4 Replies View Related

SQL Server Admin 2014 :: How To Position 5 Database Server For Centralized Database For Application

Jun 3, 2014

1) We are providing a e governance solution for an organization,where we are providing a centralized database,Client have provided 5 Database server for the same.how can we position the Database Server? there are 5000 Concurrent users and 25000 users,SAN Storage for approx. 60 TB,Database size of 2 TB and growth of 1 TB every year

2) How many instance can we have for above said Case?

3) How much RAM Required ?

View 0 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved