DB Engine :: How To Restore Only Last 1 Or 2 Months Data In Server

May 12, 2015

I have 5 TB of data in production and my development team requested to restore only last month data(<=500 MB ) in staging server and last 2 months data in pre production.

View 3 Replies


ADVERTISEMENT

Selecting Data Within 1 Month, 6 Months, 12 Months

Mar 27, 2008



I have the following table



FeedBack Type Date

test2 positive 03/15/08

tes3 negative 03/01/08

.. ....



in my page i need to select the number of negative/positive comments within the last



1 month, 6 months, 12 months



How can I accomplish that?
thanks

View 5 Replies View Related

DB Engine :: Server Restore Database

Jul 20, 2015

I am using SQL Server Management Studio 12 to try to restore a backup to a new database.  I know I have done this before, but cannot seem to make it happen now.  Her is what I have.

Backup file is "C:TempInProcessInspection_DataSQL_backup_2015_02_23_000012_4551393.bak" and it is for sure there. 

This came from a customer of mine.I launch SQL Server Management Studio 12 and log in as SA.  I right click on Databases and chose Restore Database.  I choose Device then file then Add and try to browse the the named location. 

When I get there, regardless if I choose All files or Bak, I see no files.I tried to upload an image of the "Upload Backup File" dialog, but MSDN would let it happen.  When I get to the c:temp folder, no files appear in the window.

View 30 Replies View Related

DB Engine :: Cannot Restore BAK File On Server 2012

Aug 6, 2015

i have  .bak file  downloaded from internet , and i also have istalled  sql server  2012.my problem that i can not restore  this  .bak file and get this error massage :

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATASRO_VT_SHARD.mdf'. (Microsoft.SqlServer.SmoExtended)
- .bak file  version  = 661  10  50  1600 = sql server  2008R
- my sql version     = Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)

View 4 Replies View Related

DB Engine :: Restore Backup File To Remote Server

Jul 8, 2015

I am working on a project for an SQL job. I am:

1.) Taking a database out of an availability group,
2.) Setting the recovery to simple,
3.) Shrinking the log file,
4.) Setting the recovery mode back to full,
5.) Then backing it up.

I need to restore the file to my secondary server with replace and non recovery mode. I am having trouble performing that call?  I have the code to reestablish the database to the availability group if I can get the restore feature working. 

View 11 Replies View Related

DB Engine :: Create Read-only Database From Production And Restore On Different Server

Jun 4, 2015

We have a production 2012 R2 SQL server that is not part of a domain. I have a database called CSSDC. I need to backup this database and make the BAK file available to a user on a different SQL server. Security is a concern. In order for this user to restore the database, they will have to be in the fixed server role sysadmins. I know I am going to have to use the Move with replace for the filegroups for the restore. Once the database is restored it will have to be altered to read-only. I would also like to remove the existing security and only have this new user access the read-only database.

View 4 Replies View Related

SQL Server 2012 :: Delete XML Data Older Than 2 Months

Aug 18, 2015

I have a table which has XML data, so it takes lot of time to delete it.

Here's the condition:

Archive all data older than 2 months and delete it.

What would be the good approach for this?

View 3 Replies View Related

SQL Server 2012 :: Select Query - Get Result As Month And Values For All Months Whether Or Not Data Exists

Jul 27, 2015

I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.

The Similar table would be-

create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)

But in result i want the table as -

Month Value

Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull

View 9 Replies View Related

DB Engine :: Restore Database Only With MDF File?

Jun 25, 2015

One of our database came to Restoring mode. I suddenly stop my SQL service and Copied only MDF files again Started SQL service ,unexpectedly i dropped the Database. Now i cant able to attach the database only with my MDF file.

I tried below Scripts. All scripts shows same error.

EXEC sp_attach_single_file_db @dbname='PhoenixPolice',
@physname=N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf'
GO
CREATE DATABASE PhoenixPolice ON
    (NAME = N'PhoenixPolice',
    FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf')
FOR ATTACH_REBUILD_LOG
GO
CREATE DATABASE phoenixPolice ON (FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf')
FOR ATTACH

All scripts shows below error

File activation failure. The physical file name "F:Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAPhoenixPolice_1.ldf" may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Msg 1813, Level 16, State 2, Line 1

Could not open new database 'PhoenixPolice'. CREATE DATABASE is aborted.

View 4 Replies View Related

DB Engine :: Logical Backup / Restore Method?

Oct 22, 2015

Is it possible to backup/restore sql server databases in a logical way like oracle(exp/imp, expdp,impdp, can export/import tables/users etc)?my case, I have a database which has many data files, in different file groups, I would like to put them together into one file and don't want to shred the database.

View 6 Replies View Related

DB Engine :: Restore Database When It Is Accessed By Application?

Oct 5, 2015

Our application team make design changes of the database in their development server and asks me to restore the bak file from the development server into the production server with replace command. The database in the production server is connected to the application server and so when I try to restore the database error message comes that the database is in use.

So first I find out the sessions with the logins who is the user in the database with sys.dm_exec_sessions joined with sys.dm_exec_requests and then kill the sessions. And after that it is possible to restore. But many times when I kill the session and try to restore in between new sessions comes into effect which are generated from the web server and many times I have to resubmit the query in the dmv's and find the sessions and kill it again and again before restore can be done. Is there any correct method to restore an active database which is being accessed by the application from a web server with out stopping the web server?

View 6 Replies View Related

DB Engine :: GRANT Read-only Permission After A Restore

Jun 19, 2015

I'm trying to grant a read-only permission in sql 2014 after a restore and I wanted to find out what it is in SQL 20114?

View 4 Replies View Related

DB Engine :: Restore Backups With Different Recovery Fork

Oct 21, 2015

how to restore database backups with different recovery fork. I have 1-full backup 2-diff backups and 10-tran backups. My prod database in mirror, so after error, switched to mirror with "allow_data_loss" option. And now I have full and diff backup with one recovery fork GUID and other backups with another GUID.So the question is, how to restore all this backups if in middle of restoration will be different recovery fork.Tryed to restore log backups with new fork guid and got error:This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time.

View 2 Replies View Related

DB Engine :: Restore Failed With Very Large Backup Devices

Jul 1, 2015

I have a database size of 9.8TB and I backup it to 30 backup devices. Each one has 110GB after backup compression.I tried to restore these files to standby server via 100MbE network but it always failed. My colleague told me this never happen before and I said yes because I have done this before a lot of times when backup devices are significantly smaller.He said the only way is to copy backup files locally and restore locally. But I am trying another method, I create more: 64(maximum) backup devices and try to restore via network again.SQL server version is Microsoft SQL Server 2012 (SP1)

- 11.0.3401.0 (X64)   Jan  9 2014 13:22:15   Copyright (c) Microsoft Corporation 
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601:
Service Pack 1) .

---  error message ---
5 percent processed.
10 percent processed.
15 percent processed.

[code]....

RESTORE DATABASE is terminating abnormally.

View 11 Replies View Related

Sql Server Engine In A VM; Data On A SAN?

Jan 10, 2008

Server consolidation is the word of the day here. One of the elements is to use, when applicable, virtual machines in our devstageprod environment. Our sql servers (with local storage) are well utilized and run fine. I don't want to move the entire server (sql 2005 engine + data) into a VM as I think performance will be bad. But, per my question, if only the sql engine is running in a VM while the data is stored on our SAN, I think we may achieve acceptable performance.



Do you have any thought or experience with this mix of Vm and SAN from a sql perspective?





TIA,



Barkingdog



View 1 Replies View Related

Power Pivot :: Comparing YTD Data To Average Of Data In Last 6 Months

Jun 9, 2015

I am trying to build various reports that compares data over time. I have one that measures Year Over Year % difference for number of incoming projects. I managed to do that easily by calculating the following

YTDProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(SUM('TrendData '[Projects]),DATESYTD(CalendarDate[FullDate])))
PYProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(sum('TrendData '[Projects]),SAMEPERIODLASTYEAR(DATESYTD(CalendarDate[FullDate]))))
YoYDifference:=[YTDProjects]-[PYProjects]
YoYPercProjects:=IF([PYProjects]=0, BLANK(), [YoYDifference]/[PYProjects])

Where Projects is the metric in question, TrendData is the table that contains project data and CalendateDate is the Date Table. But now I am trying to compare the same YTD projects data to number of projects that came in the last 6 months. How do our projects compare to average number of projects that came in last 6 month period.

I tried the the DATEADD function instead but got no luck and data came out wrong!

PrevProjects:=CALCULATE(SUM([Projects]),DATEADD(CalendarDate[FullDate],-1,QUARTER))

For some reason, this also returns blank in my model:

QTDProjects:=TOTALQTD(SUM('TrendData'[Projects]),CalendarDate[FullDate])

View 2 Replies View Related

DB Engine :: Store Landset Data In Server?

Jul 14, 2015

what is the best practices to store Landset Data in SQL Server? volume of data should grow up to 100 TB

View 3 Replies View Related

Data For Last 13 Months

May 8, 2008



Hi,

I want to select data from a table for last 13 months.
Can anyone tell me how to write the T-sql code for this?

The table looks like this.


FileMonth Type

2007-04-01 00:00:00.000 Total NULL 199886109.65
2007-04-01 00:00:00.000 Total Line 170724936.92
2007-05-01 00:00:00.000 Total Loan 29161172.73
2007-05-01 00:00:00.000 InstLend NULL 49780163.49
2007-06-01 00:00:00.000 InstLend Line 42450387.44
...........

So I wanna pull data from 04/01/07 to 04/01/08.

So I wanna write a query some thing like this.

Select *
from table1
where Filemonth BETWEEN PreviousMonth AND Last13thMonth


Thanks

View 1 Replies View Related

DB Engine :: Read Unicode Data From Server 2008

Oct 18, 2015

way to read data from database already stored as question marks .that because by mistake i insert the data "arabic" from  the VS as sqltext but the field in the database is nvarchar now i want to get the data back.

View 4 Replies View Related

DB Engine :: Take Only Last 1 Year Data Backup Using Script From Server

May 20, 2015

I have last 5 years data in my database(SQL server).

I want to take backup only last 1 year(last 365 days) data using SQL script.

View 6 Replies View Related

Select Last Months Data

Aug 5, 2004

Hi, i am trying to create a t-sql statement that will retrieve last months data (ie. if i run the query on 9th August, i only want to retrieve Julys data, 1st Sept will retrieve all of Augusts data etc). The query will be used once a month to populate a table, can anyone advise me on the correct where clause to use ?

Thanks in advance

Dave

View 3 Replies View Related

Return The Last 3 Months Data

Mar 12, 2007

Hi,

I have the following SSAS MDX query, currently taking a From & To date. How would I alter the MDX below so that just the prior 3 months data was returned for a chart of balances. I do not wish to retain the date parameters

SELECT NON EMPTY { [Measures].[EOD Book Balance] } ON COLUMNS, NON EMPTY { ([Time].[Simple Date].[Simple Date].ALLMEMBERS * [Products].[Product ID].[Product ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@FromTimeDate, CONSTRAINED) : STRTOMEMBER(@ToTimeDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Type].&[Term] } ) ON COLUMNS FROM ( SELECT ( { [Book Balance Type].[Balance Type].&[Credit Balance] } ) ON COLUMNS FROM [DailyBalances]))) WHERE ( [Book Balance Type].[Balance Type].&[Credit Balance], [Products].[Product Type].&[Term] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

View 7 Replies View Related

Transact SQL :: Getting Data For Last 3 Months

Oct 14, 2015

I have a date into format YYYYMM. Data type is int. I need to make query where every time it will return me last 3 months, but without current one. For example I have data for months below

201510
201509
201508
201507

Query should return all records for 201509, 201508, 201507.

I was trying lot of solutions founded in internet like this one:

Date_Column >= DATEADD(MONTH, -3, GETDATE())
or
DATEDIFF(MONTH, my_date_column, GETDATE()) <= 3

but it doesn't work.

View 4 Replies View Related

Showing Data For All Months

Apr 3, 2008

I have a Report Parameter Non-queried list for all months (Labels January, Februari... and Values 01,02...).
When I pick a month in my report all works fine
But I would also like to have data for the complete year (via an All selection)
How should I get this done . Any help appreciated
Edwin

View 4 Replies View Related

Sorting Data Having Months

Sep 21, 2006

Dear all,



I have a field [Month] in my table AA.having Data like ( January,February,March,April etc.)

I need to sort them by starting of month

lets say as

January

February

March

How i can i do this

Have no index and can't define a index on this field

Thanks for ur time and Help



Regards

Mohd Sufian

View 12 Replies View Related

DB Engine :: Server 2012 Import Data From Excel 2010

Oct 11, 2015

I used sql server 2012 express Import and Export Data (32-bit) wizard to import data from excel 2010 to a given table. But I got the following error message: Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Unspecified error". (SQL Server Import and Export Wizard)
 
Error 0xc020901c: Data Flow Task 1: There was an error with Destination - MPRecord.Inputs[Destination Input].Columns[Top1] on Destination - MPRecord.Inputs[Destination Input]. The column status returned was: "The value violated the integrity constraints
for the column.". (SQL Server Import and Export Wizard)
 
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Destination - MPRecord.Inputs[Destination Input]" failed because error code 0xC020907D occurred, and the error row disposition on "Destination - MPRecord.Inputs[Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)
 
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - MPRecord" (35) failed with error code 0xC0209029 while processing input "Destination Input" (48). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

View 2 Replies View Related

Select Query To Get Last 6 Months Data

Sep 16, 2004

Hi,
I need to write a select query to get last six months data from a table , on monthly wise.Can anyone help me in writing this query
I am using sql server 2000.

eg: Jan...data
feb...data

TIA,
Ravi

View 1 Replies View Related

Extract Data For Last Day Of Previous Months

Jun 12, 2014

I need to extract records for the last day of previous months (Up till January of the same year) from a table SALES, according to a date parameter ASOFDATE that the user enters.

For Example

If user keys in ASOFDATE as 10-May-2014, I would have

ASOFDATE Data1 Data2
10-MAY-2014 123 443
30-APR-2014 222 234
31-MAR-2014 544 875
28-FEB-2014 546 908
31-JAN-2014 957 896

How do I do that?

View 2 Replies View Related

DB Engine :: Possible To Force Row Level Locking In Server 2015 Before Inserting Data

Nov 11, 2015

Is it possible to force row level locking in Sql server 2015 before inserting the data  and release the same afterwords..find the code for which we would like to impliment the same

DECLARE @TravelAgentid Varchar(20)
DECLARE @Date DATETIME2(7)
DECLARE @InsDate DATETIME2(7)
set @TravelAgentid ='A101'

[code]....

View 11 Replies View Related

Select Data From Table An Return All All Months

Jul 23, 2005

I have the following query:SELECT Month, Sum(Hits) AS Hits FROM tblHits GROUP BY Month ORDER BYMonthUnfortunately it only returns rows for months that have data assignedto them.How can I tweak this so that months 1-12 are returned, and Hits = 0 formonths with no data in the base table?Thanks.

View 2 Replies View Related

Transact SQL :: Pulling The Most Recent 12 Months Of Data

Jul 1, 2015

My goal is to show the most recent 12 months of data including the current month based on a patients discharge date (relative to the query execution time). There could be many years of data so I want to be sure I am pulling the most recent 12 monthsThis seems to work (on the surface anyway … not quite sure if this is the best logic to use).

DATEDIFF(month,PAT_ENC_HSP_discharged.HOSP_DISCH_TIME,
GEtDate())
< 13

View 5 Replies View Related

Need Report Also Showing Months Even When No Data For That Month

May 13, 2008

Hi All,

would like to make a report with sales figures per month even is there are no sales figures that month.
Report should still show the month.

Any help to get me started in the wright direction much appreciated

edwin

View 1 Replies View Related

T-SQL (SS2K8) :: Date Logic - How To Get Previous 6 Months Data

Sep 29, 2014

I am working in sqlserver 2008 R2 and below is my sample research query. I am trying to get previous 6 months data.

WITH CutomMonths
AS (
SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate
FROM (
VALUES (1)

[Code] ...

Current output what i am getting:

Expected Output:

I found why the April month i didn't get the $20 because the startdate of my perks CTE '2014-04-03'. If it is '2014-04-01' then i will get the expected output.

But i should not change the the date on perks. How to neglect this date issue and consider the month instead to get the expected output.

View 6 Replies View Related







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