No. After you restored the database in your development environment it had become a different database. Re-attaching it back to production will replace the production database and all transaction that occurred after your initial detach will be lost. Doing what you describe usually require very complex solutions, involving setting up replication from production to staging and contiguously replicating transactions in production to the staging server. Needless to say, developing/testing such a complex solution is only worth it for a ... worthy goal. Shrinking your database is not such a worthy goal.
Handling situation of runaway data that was never designed for delete (which is 99% of all projects, since the need for efficient delete of data is never evident during development, when the tables are empty) is quite hard actually. Partitioning is the best solution, by far, but is a huge undertaking with serious impact on the application. Other than that, deleting old data in small batches is the next best thing, provided an appropriate supporting index is provisioned.
You will want to load your data into a new table, doing this in small batches, then drop the existing table. I put together a quick example using the Sales.Customer table in AdventureWorks, something similar should work for you also.
First, create your new table, complete with the new datatype you want to use:
CREATE TABLE [Sales].[Currency_New](
[CurrencyCode] [nchar](4) NOT NULL,
[Name] [varchar](128) NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_Currency_New_CurrencyCode] PRIMARY KEY CLUSTERED
(
[CurrencyCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
Then, insert your records and define your batch. I am using 10 here, but you will likely want to use something larger, say 10,000 rows at a time. For 30MM rows I'd even suggest you go to 100k row batch size at a time, that's the limit I typically used with larger tables:
DECLARE @RowsInserted INT, @InsertVolume INT
SET @RowsInserted = 1
SET @InsertVolume = 10 --Set to # of rows
WHILE @RowsInserted > 0
BEGIN
INSERT INTO [Sales].[Currency_New] ([CurrencyCode]
,[Name]
,[ModifiedDate])
SELECT TOP (@InsertVolume)
SC.[CurrencyCode]
,SC.[Name]
,SC.[ModifiedDate]
FROM [Sales].[Currency] AS SC
LEFT JOIN [Sales].[Currency_New] AS SCN
ON SC.[CurrencyCode] = SCN.[CurrencyCode]
WHERE SCN.[CurrencyCode] IS NULL
SET @RowsInserted = @@ROWCOUNT
END
I usually do a sanity check and verify the rowcounts are the same before cleaning up:
SELECT COUNT(*) FROM [Sales].[Currency]
SELECT COUNT(*) FROM [Sales].[Currency_New]
Once you are confident you have migrated your data, you can drop the original table:
DROP TABLE [Sales].[Currency]
Last step, rename the new table, so that users don't have to change any code:
EXEC sp_rename '[Sales].[Currency_New]', '[Sales].[Currency]';
GO
I don't know how long this will take. I'd suggest you try doing this when you have a clear maintenance window and users aren't connected.
HTH
Best Answer
Yes you do.
No you can't.
Before you go throwing the towel in with your current database, you should step back and analyze why you think your transaction log is too large in your current database. Unless you logged workload changes, the same behavior will occur in the new database.
Find out first how large your transaction log file is and how much space is actually being used inside the file:
Note: you'll need to change "YourDatabaseName" to the appropriate database name.
Likewise, there are certain database settings (recovery model) and log reuse descriptions that can also aid in the troubleshooting of a transaction log that is just too large:
The above queries should be a decent starting point with troubleshooting and/or discovery regarding your problem. Feel free to comment below with your findings and we can take this a step further.
Another thing, are you completely confined to SQL Server 2008 for your Express Edition? In SQL Server 2008 R2 and above, the maximum database size is 10 GB. Can you not use a newer version of SQL Server?