Questions[sql-server-2005](server)

I have a database in dev (SQL Server 2005 on Windows Server 2008) that I need to move to prod (SQL Server 2000 on Windows Server 2003). My process is as follows:

1. Login to dev, open SQL Server Management Studio
2. Right click on the database | Tasks | Backup. Keep all default options (full backup etc.)
3. Move .bak file locally to prod (no network drive), login to prod, open SQL Server Enterprise Manager.
4. Right click Databases node | All Tasks | Restore database.
5. Change Restore as database to reflect the same database name.
6. Click radio button 'From device'. Click 'Select Devices'
7. Click Restore from: Add..., browse to .bak file (small - only 6mb)

Now I am ready to restore the database, so I click OK and get the following error:

"The media family on device 'E:...bak' is incorrectly formed. SQL Server cannot process this media family. RESTORE DATABASE is terminating abnormally."

This error is immediate.

I have tried a few different variations of this - restoring the db to dev machine with a different db name and log file names (where it originated), creating an empty database with the same physical path to files before and trying to restore to that, making a few different .bak files and making sure they are verified before uploading them to prod. I know for a fact the directory for the .mdf and .ldf files exist on prod, though the files themselves don't exist. If, before I click OK to restore, go to the options tab instead I get the following error:

Error 3241: The media family on device 'E:...bak' is incorrectly formed. SQL Server cannot process this media family. RESTORE FILELIST is terminating abnormally.

Anyone have any bright ideas?

Even though nothing is using it I am unable to detach a database because it is use.

Cannot detach the database 'DEMO' because it is currently in use.

I have tried rebooting and get the same message.

Is there a way to find out the progress of DBCC SHRINKFILE statement?

Here is how I was running it

dbcc shrinkfile('main_data', 250000)

I am running above statement on both SQL Server 2005 and 2008.

[UPDATE] Here is the query I ran to check the progress and the text that's being run.

select  T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id)
        , R.cpu_time, R.total_elapsed_time, R.percent_complete
from    sys.dm_exec_requests R
        cross apply sys.dm_exec_sql_text(R.sql_handle) T

I have three maintenance plans set up to run on an Sql Server 2005 instance:

• Weekly database optimisations followed by a full backup
• Daily differential backup
• Hourly transaction log backups

The hourly log backups are usually between a few hundred Kb and 10Mb depending on the level of activity, daily differentials usually grow to around 250Mb by the end of the week, and the weekly backup is about 3.5Gb.

The problem I have is that the optimisations before the full backup seem to be causing the next transaction log backup to grow to over 2x the size of the full backup, in this case 8Gb, before returning to normal.

Other than BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY, is there any way to reduce the size of that log backup, or prevent the optimisations from being recorded in the transaction log at all, as surely they will be accounted for in the full backup they precede?

We have a beefy Windows 2008 x64 server (4 x 4 core CPU, 32GB RAM) running SQL Server 2005 64-bit. We have a small (6GB) but very important database that is somewhat slow to access until the pages are cached in memory (the usage is very much random I/O so the odds are very low a given page is in memory and the end users complain about the initial slowness). The disks are fast enough (local 15K SAS) but I guess the app is somewhat clumsily written (it's a COTS solution) so I am wondering if there's a way to "force" a database in memory in SQL Server 2005 (2008 is not supported by the vendor, so we shouldn't upgrade to that yet) to help avoid the initial cache-filling blues?

My current method is that I run a SELECT * from each table in a script to get data pages in memory but some objects (indexes, Full text search, etc.) are not cached by this method (and modifying the script to interrogate indexes and write appropriate WHERE clauses to cache is boil-the-ocean complex).