We have a current setup where we have two SQL 2008R2 database servers in production.  Our reporting team requested a single place they could query data from both servers (to avoid both hitting production directly and the performance issues with linked servers).  So we set up log shipping where we ship several databases from both sources to a target server.  It works great, the databases on the target server are read only during the day and only unavailable when the log shipping restores are scheduled at night.  However, the vendor applications for both source servers are going through an upgrade project, and they will unfortunately have to be be different versions (SQL 2014 and 2016 respectively). Most of what I have researched seems to say that you cannot log ship from a different source version, but I've also found information suggesting that you can ship from a lower to a higher version, but am unable to find anything official. So, my question is, is it possible to log ship from both 2016 and 2014 sources into a 2016 destination, AND leave the databases read only when they are not restoring?  And is there official Microsoft confirmation of this?
123310
I'm lead to believe that TLS is only appropriate/supported across the same versions of SQL Server.  What I'm trying to do is to maintain a reporting (read only) version of a production transactional SQL Server 2005 database (180 Gb) on on a SQL Server 2012 server (and in the case of other clients 2008 R2). I can do simple back up and restore at any time but to improve latency between refreshes of the database, I'd like to see if I can do something like transaction log shipping.  If TLS is not available in this scenario what do the panel suggest as an alternative strategy.
13222
I feel that my shop has a hole because we don't have a solid process in place for versioning our database schema changes. We do a lot of backups so we're more or less covered, but it's bad practice to rely on your last line of defense in this way.   Surprisingly, this seems to be a common thread. Many shops I have spoken to ignore this issue because their databases don't change often, and they basically just try to be meticulous.  However, I know how that story goes. It's only a matter of time before things line up just wrong and something goes missing.   Are there any best practices for this? What are some strategies that have worked for you?
56714
I have given an RTM version of SQL Server 2014 and I need to apply the latest SP (currently SP2) and CU (currently CU8). Do I need to apply SP1 first? Or can I straight move to SQL Server 2014 SP2 CU8 without applying the old Service Pack 1 and the related Cumulative updates?
81584
We're working on migrating some SQL Server VMs to Azure SQL Managed Instances.  We did an initial backup/restore without a problem:  RESTORE DATABASE [xxx]      FROM URL = 'https://&lt;account-name&gt;.blob.core.windows.net/&lt;container-name&gt;/&lt;filename&gt;.bak'   After doing testing, we now need to do a fresh restore from our SQL VMs.  We started doing the same process we initially did but when restoring, we got an error:  Msg 1801, Level 16, State 1, Line 1 Database 'xxx' already exists. Choose a different database name. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.   So I tried the WITH REPLACE option.  Unfortunately:  Msg 41901, Level 16, State 2, Line 1 One or more of the options (replace) are not supported for this statement in SQL Database Managed Instance. Review the documentation for supported options.   Reviewing the documentation just tells me that WITH REPLACE isn't available on a Managed Instance.  I can't find any documentation on doing an in-place restore/overwrite of an existing managed instance database.  Am I forced to drop and re-create the database?
87176
I have 50 servers. I manage them from a central management server on SQL Server 2014 Enterprise Edition. I don't use repository.  I could open a query and get result for each one at the same time. But I want to do it in an automated way, like a job, to collect data from each server and store/handle it locally.  Is this possible and if so, how?
96831
I'm using SQL Server (2008 R2 in this case) and I am backing up my databases into backup devices (one per database). Every Sunday the device is overwritten with a new full backup, every night a differential backup is added and every hour a transaction log backup is added to the backup device.  When restoring this onto a new server using SSMS I first create a new database, then add the backup device and then restore from that backup device. I then have to manually select the applicable files:   The Full backup (always the first one in the list) The latest differential backup All transaction log backups that came after the last differential backup.     This works fine for the occassional restore, but I need to automate this in T-SQL.   There is some logic on which files to pick, but I don't even know how to get to the content of the backup device yet.  I would like to have a stored procedure that takes the name of the database and the backup device and restores the latest version. Anybody knows of such a thing?  Or do I miss something here and there is a simple command for this?
91317
Not sure this is something which people will know on here are not.    We currently have a BI setup where we have Integration services on one SQL instance and the data warehouse database on a separate SQL Instance.    My question is, as no users physically connect to the Integration services SQL instance and it is purely used to execute package from the integrations service catalog via SQL agent, do we need to License every user i.e. Cal's for that SQL instance.  Basically i'm wanting to know if I could utilize SQL 2016 for an integration services instance and use features like Json manipulation etc and then load this data into the 2014 version instance...but what level of licensing will I need?
7631
I'm using SQL Server 2008 R2, and I need to restore 36 .trn logs to a database. I have restored the first 4 using the restore transaction log functionality in SSMS (right-click database > tasks > restore > transaction log).   Is there a way to restore them all in order using this tool or with a script? The .trn files are in a folder on a remote server. I do not not need to restore all the files in the folder, only ones between specific dates.  I'm looking for a way to automate this so I don't have to manually restore each individual log.  I access the files using an address like the one below.  \\remote server IP\SQL_Backup\LMSQL02\TLog\file.trn
79356
I just got into a project in which we have only one SQL Server 2012 copy. We have 12 databases attached to the primary instance of SQL Server 2012.  As I don't have SQL Server 2012 or supporting OS, I have to depend on the primary instance of SQL Server 2012.  I am not sure whether it is good to create copy/clone of all 12 databases and attach it to same instance and work on the copy for data manipulation and query testing purpose.  Based on Kin's query  SELECT    SERVERPROPERTY ('MachineName') AS PhysicalMachineName,    SERVERPROPERTY ('ServerName') AS SQLServerName,    SERVERPROPERTY ('Edition') AS ServerEdition,    CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + '         + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'        + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,     RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],  SERVERPROPERTY ('Collation') AS Collation   Following is the output. I ignored PhysicalMachineName and SQLServerName.  ServerEdition : Business Intelligence Edition (64-bit) ProductVersion : SQL Server 2012 + RTM + (Build11.0.2100.60) O.S.           : Windows NT 6.1 &lt;X64&gt; (Build 7601: Service Pack 1)  Collation      : SQL_Latin1_General_CP1_CI_AS
78231
I have a legacy database whose nightmarish lack of design I will not get into here, but the files on the server are (relatively) enormous. I have:  MyDatabase.mdf: 24.8GB MyDatabase.ldf: 114.6GB  This database is backed up to a .bak file every night and shipped over to our reporting server, where it's restored. The .bak file is much smaller at only 1.8GB.  However, when I try to restore it on the reporting server, it fails due to insufficient space. There are around 100GB free on the server, and it's trying to eat up the entire 139.4GB that the files consumed on the original server. Unless my knowledge of compression is horrifically wrong, I'm fairly confident that the 1.8GB file is not actually expanding by 7400%.  My Question: Is there any way to tell SQL Server to restore this backup file without reserving that space upfront? I do not care about any of the logs; I just need the data to be there. I understand databases from the development and schema perspective, but I am by no means any sort of DBA.  This is on SQL Server 2008 R2. Thanks for any help or suggestions.
47385
My primary server with SQL Server 2005 broke down, so I've started up the log-shipped database on the secondary server. The database is live on the secondary server, and I just need to remove the log shipping settings for it, while the primary server is no longer accessible.  It seems that the sp_delete_log_shipping_secondary_database stored procedure should be executed on the secondary server to do this, which "removes a secondary database and removes the local history and remote history", as it is stated on .  Would this also remove the database itself? Or only the log shipping settings for it? And what will it do when the primary server is not accessible?  I must not loose the database on the secondary server, as it is already in use.
93847
I have several hundred (currently 466, but ever growing) tables I have to copy from one server to another.  I have never had to do this before, so I'm not sure at all on how to approach it. All the tables are in the same format: Cart&lt;Eight character customer number&gt;  This is part of a larger project of which I am merging all these Cart&lt;Number&gt; tables to one Carts table, but that's a whole different question altogether.  Does anyone have a best-practice method I can use to copy all these tables over? The database names on both servers are the same, if that helps. And as I said earlier, I have the sa account so I can do whatever is necessary to get the data from A to B. Both servers are in the same server farm, as well.
23585
A dilemma probably faced by quite a few DBAs. Does one upgrade to the latest version, in this case SQL SERVER 2016, or play it safe and upgrade to 2014, which hopefully has most bug fixes in place by now?  I will only be upgrading in Standard Edition and from what I've read there isn't much between 14 &amp; 16 in terms of functionality, enterprise is a different story.  The only things of interest to me at the moment in 2016 are probably the query store features and possibly the tabular BI stuff.  I have read 2016 does use fewer resources and depending on the tasks it executes faster, which is obviously a big bonus.  At the moment I am leaning towards 2016 as opportunities to upgrade are few and far between, just worried about potential bugs and applying them in 24/7 up time environment with no maintenance window.  Would be great to hear if anyone else has faced the same/similar upgrade path, and ultimately what decision they took.  I will probably get a down vote as there is no right answer...but its a question that must be asked a lot..
37953
I've inherited a SQL Server 2008r2 database; the Data file was created with an initial size of 1,000 mb, but the Transaction Log has an initial size of 28,016 mb.  No backups were being performed.  I've implemented full and transaction backups to match our recovery needs, and as a result the transaction file is always ~99.9% empty, never exceeding 30 or 40 mb used.  I'd like to reduce the initial size so I can shrink the log down to a more apprpriate size, 200 mb or so.  Does anyone know how I can redefine the Log file with an Init size of 200 mb?
48042
One of our SQL Server 2014 database transaction logs is growing very fast and I cannot find the root cause for this.  Database in Simple recovery mode. During full backup the log file grows to its limits (800GB) and backup fails.  I checked the transactions during the backup and did not find anything strange.  All transactions are our regular loading and no maintenance jobs (index rebuilds etc.)  I am aware of that backup keep the log from reuse but this mean 800GB changes during the backup period and this does not make any sense.  I had compared this DB to similar DB size, loading and backup duration. The second DB Log is not growing more than 400GB.  DB size is 7TB. Backup time is ~20H. The only time log is growing is during backup. Log_reuse shows correct Backup_Restore. No long running transactions.  The problem is the "speed" of log growth - compared to similar sites with same load and same backup duration, the log is 4 time less.
113685
Is there a way to restore a SQL Server 2012 database backup to a SQL Server 2008?   I tried to attach file, it does not work.
54824
I need to do a join across two different database servers (IPs 10.0.0.50 and 10.0.0.51). What's the best way?