Hello I am new on this ad I would like some advise oh ho w to migrate from 2005 to 2012 Server,
I already found that log file is too big 2839... the DB said is impossible to move it... how will be the best way to approach this..
All advise will be very helpful
Thank you
First, 2839 what? MB? GB?
Do you want to move to a new server? Or stay on the same server?
Yes we want to go to a new server basically we are going for 2005 to 2012, and it is 2839,677 MB
What error message did you get? There should be no problem backing the file up and restoring it on the new server.
My notes on migrating to latest version of SQL Server are here
I did not receive any error, but is the DB admin who said it can cost a lot problems and damage...
So I guess i can copy the log and go from 2005 to 2012 with not problem ??
Thanks
You can't copy the file, you need to Backup/Restore. (You could detach, copy files, re-attach the original and attach the copied files, but I don't recommend it: it requires taking the original database offline for the duration of the copy (could be long for large files and/or slow COMMs to the new server), and there is always a slim possibility that the original will not re-attach. A Backup File should be smaller (often significantly smaller) than the actual physical files, so will copy more quickly to the new target server, and backups allow you to use TLog backups to minimise the downtime during migration to the new server.
For minimum downtime the normal recommendation is:
Take a full backup and restore on the new machine. Perform whatever tests etc. and create scripts/notes of any steps needed to make the migration. This phase is not time critical.
When you want to cut-over then:
Make a full backup
Restore on the target machine but leave the target database in STANDBY mode.
Take a transaction log backup and copy/restore to target machine. Continue to do this until the start of the critical time portion. (i.e. if your database is making regular log backups then restore each one onto the target machine)
START of time critical portion:
Make sure all existing Tlog backups have been restored on the target machine. Worth making "just one more" and restoring that just before the start of the time critical portion, to minimise the downtime)
Disable user access (if you have set a pre-arranged scheduled maintenance time then wait for that)
Set database to single user / DBO to prevent any unexpected access (including from scheduled jobs etc.). Alternatively set database to READ ONLY to be absolutely sure!
Take a final TLog backup and copy to target machine, then restore (WITHOUT standby mode)
Target Database will automatically "upgrade" to latest version as part of the end of restore process
Perform any scripts / actions etc. on the Target Database which you documented earlier during your test
Set back to MULTI USER access (i..e opposite of single user / DBO / READ ONLY that you set on the original Source database)
Re-enable user access
The time for the time critical portion will depend on how long SQL takes to convert the database to Latest Version, and how long any additional actions take to perform. On even huge databases we have done this sort of cut-over in under 5 minutes by using STANDBY to get the bulk of the database copied across ahead of the final cut-over.
In terms of Log Filesize you could shrink the log (on the original database) before migrating it to the new machine, but you should be aware of how much Log space it actually needs before doing that - if you shrink the Log file and it grows back again, because that much space is needed, then the file will become fragmented. OTOH if the database only needs a few MB of Log Space it would be worthwhile shrinking it to "maximum that would normally be used"
You can probably deduce, somewhat, the max size of the Log file from the maximum size of any TLog backup files taken recently (e.g. in the last month). It is possible to query that from SQL's MSDB housekeeping database, you don't have to actually go and find all the physical files 
)
If your database is NOT in FULL Recovery Model then you can use one final DIFFERENTIAL backup at the start of the time critical period instead of Log backups (but you won't have the benefit of TLog backups to guide you as to a sensible size for your Log file)
You make it sound so easy, here they were trying to shrink the database but according to DB is not working firt je said it was drive space, we increased but it didnt work either.so the log is 2803,???, I have noticed too that the other DB sitting on the server do not have log files.
Thank you
Been doing it a long time The only bit that I think I can usefully help with is to propose Best Practice because with experience that is most likely to succeed and/or least likely to cause side effects. The process is not as simple as "just copy over a file" I'm afraid ... and if you are not confident about it / understanding it, then it would be better to fall-back to a simpler process. For example, you might have longer down-time during the upgrade, but maybe for you that is not an issue - e.g. you can do it over a weekend and your users don't work then; most of my APPs are web based and in use 24/7, if we can get cut-over down to a few minutes we don't bother with scheduled maintenance we just put up a holding page explaining that the users need to wait 2 minutes and press RETRY and their session / State etc. will be preserved. We've upgraded database servers at the peak of a client's busiest period (told the client months before that their hardware was inadequate, but can't expect them to listen to good advice when the option is to pay extortionate emergency-fix fees instead!!)
Route 1 : Backup and Restore. This requires that you have enough disk space (preferably locally-ish to the SQL Server) to make a Full backup. You also need enough disk space for the ORIGINAL SIZED Data and Log files (and the backup ) on the new target server.
If your LOG file is wildly large the new server will reserve the same amount of space for it, as the old one did. You can NOT restore a database and specify a "smaller log", you will get the original sized one. So there is merit in shrinking the Log file before migrating the database, provided that the APP does NOT need a log file that big.
You don't need extra disk space to shrink the log - but you may do in order to back it up (but you can target the backup file anywhere that you have access to). If the disk is absolutely full then that may be preventing the database extending the file, but if that is the case no one will be able to access the database using the APP / Whatever.
Sounds like you may not have dedicated experienced folk looking after your database, so chances are probably high that the log is bigger than it needs to be.
It is also possible that you are NOT using FULL Recovery Model (the alternative is usually SIMPLE Recovery Model). It would help to know that. It is set per-database, not per-server. You can check that with this SQL code:
SELECT d.recovery_model_desc, d.name
FROM master.sys.databases AS D
ORDER BY D.[name]The databases master, model, msdb and tempdb are system databases and you won't be copying those over (the new installation on the new server will create them)
If your database IS in Full Recovery Model AND you do not have scheduled Log Backups (or they are infrequent, such as once a day, then the Log file will grow continuously until a Log backup IS taken - thus it will be big.
If you take regular Log Backups but have a one-off huge transaction (such as deleting a whole load o old, stale, data once a year) then that one "huge" transaction may force the Log file to grow (you can program around that of course, deleting in batches whatever, but even the best managed offices have "huge transactions" (by accident, maybe!) once in a while. So you may have a regular huge transaction, or an infrequent, or even one-off, huge transaction. The answer is to shrink the log file, but you should not do that smaller than it needs to be - so you need tow ork out, reasonably accurately, what size that is.
If you database is running transaction logs and is NOT being backed up regularly, then you won't be able to shrink the file - it will be full already you can only shrink the unused-part on the end of the file. Answer to that is to deliberately take a Log Backup. You will probably have to take a second one before you can successfully shrink the log file (likelihood is that more transactions are added, but not committed, to the end of the file whilst the backup is running ... after the backup finished the front of the log file will be marked "empty" and new transactions will be written at the start of the file, not the end of it. Another backup will clear the stuff at the end of the file (provided that ALL transactions are committed ... leave it a little while maybe ...) and THEN you will be able to shrink the file.
Here's some SQL to see what backups have been made on your database recently.
SELECT MAX(b.backup_finish_date) AS BackupFinished,
b.type,
d.name,
d.recovery_model_desc as RecoveryModel
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b
ON d.name = b.database_name
GROUP BY d.name, b.type, d.recovery_model_desc
ORDER BY
d.name,
BackupFinished,
b.typeIf your database is in SIMPLE Recovery Model you are only interested in Full (Type=D) and Differential (Type=I) backups, if it is FULL Recovery Model then Log backups (Type=L) are also important. (I often see Log Backups [on clients' servers] being scheduled for daily, or every hour, but there is very little to be lost by doing them, say, every 10 minutes and your average-worst-case disaster (i.e. a server failure, but not a jumbo jet crashing into the building!) will mean you can recover from your last Log backup - 10 minutes data lost is way better than an hour ... or a day 
On my server I see Type=D Full backups on Sunday, a Type=I Differential backup for last night, and a Type=L Log Backup for sometime in the last 10 minutes.
For any database which has RecoveryModel=SIMPLE then you will only see Full and maybe Differential backups. Hopefully you have NO databases where the BackupFinished date is blank - that has never been backed up (or is being backed up by something outside SQL's control)
Letting us know what Recovery Model your database is, when it was last backed up, if you have a Log backup etc. will help us advise on your next steps.
Hi,
This DB is in Full recovery but I have noticed ( I am not the DB) those backup are not working at all, the last one are from Friday but the other DB has their backup................for any particular reason they are not working, Also I am not sure about our current DB Admin..
Thank you so much, all your explanation make real sense to me, since I was looking into it...and I found so many thing then when I asking and they explained I was lost 
then is when I have decide to look around,
Assuming that your database has a LOT of transactions in the Log File then if the Backup Device (where backups are stored) is full-ish there may not be room for your Log Backup.
Quick summary of how this works:
People change things, that updates the main database file and also writes to the LOG file. If the transaction fails (a logic test decides the data is invalid and rolls it back or something physical like a disk full or power failure) then the transaction is rolled back and either "happens in its entirely" or "does not happen at all".
SO every change is written to the Log File. In SIMPLE Recovery model once your transaction is committed the space in the log file is made available for more transactions (I've over simplified a bit), so in essence the log file doesn't grow - it grows to accommodate the single largest transaction. In FULL Recovery Model everyone transactions are written sequentially to the log file, and they remain there until a Log Backup marks them as "Backed up", and then that space is reused. If the log backup fails (e.g. disk full) then the log is not cleared. That's fine, SQL just keeps on writing to the tail end of the log file. When the log file is full SQL just extends it ... that's fine too up until the point at which the Log File fills the disk ... now SQL will raise an error and it will not be possible to perform ANY transaction. Sounds like you might be at that point 
It is possible to "throw away" the log file. Really bad idea, if you then have a disaster you are hosed and have zero chances of sorting it out ... it might be the only way though.
Correct way is to find enough disk space, somewhere, to backup the log file. Then shrink the log file to something reasonable - that will free up that disk space on the server.
It is possible that your Backups are on the same drive as your Data? - so when the backup fails and SQL starts extending the log file there is even less space for backups! Quick solution in that scenario is to delete all (oldest first, largest first) backups that you know are safely copied to tape. Use that disk space to backup the log file.
This code will tell you how big the Log Backups have been recently (sorted by size). Obviously whatever backup you now make will be huge, so discount that, but the "largest recent backup" will give you an idea how big the LOG file should be (when you get to the point of being able to shrink it)
SELECT TOP 1000
[Date] = DATEADD(Day, DATEDIFF(Day, 0, BS.backup_start_date), 0),
[Type] = CASE BS.type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Diff' WHEN 'L' THEN 'Log' WHEN 'G' THEN 'Group' WHEN 'V' THEN 'Verify' ELSE BS.type END,
[Size MB] = CONVERT(int, BS.backup_size / (1024*1024)),
BS.database_name
FROM msdb.dbo.backupset AS BS
WHERE 1=1
AND BS.database_name = N'MyDatabaseName'
-- type : D=Full, I=Differential, L=Log, G=FileGroup, V=VerifyOnly
AND BS.type IN ('L', 'XXX')
-- AND BS.backup_start_date > DATEADD(Day, -21, GetDate()) -- Optionally cutoff date
ORDER BY BS.database_name,
BS.type,
[Size MB] DESCIn case not obvious: SQL never reduces the size of the log file (unless you deliberately SHRINK it) It is an "expensive" operation extending it, and repeatedly shrinking/growing the file causes serious fragmentation at the physical disk and operating system level, which reduces performance. Thus it is important to try not to shrink it below what it needs. It WILL automatically grow if it needs to, best to avoid that happening except in exceptional / one-off circumstances.
In case helpful this will tell you where the physical database/log files are located
SELECT [Database]=db_name(database_id),
size,
[Type] = CASE WHEN physical_name LIKE '%.mdf' THEN 'MDF'
WHEN physical_name LIKE '%.ndf' THEN 'NDF'
WHEN physical_name LIKE '%.ldf' THEN 'LDF'
ELSE '???'
END,
MF.physical_name
FROM sys.master_files AS MF
WHERE 1=1
--
ORDER BY
[Database],
MF.physical_nameand this will tell you where the physical Backup files are
SELECT TOP 1000
BS.database_name,
BS.backup_start_date,
BS.type,
BS.backup_size,
BMF.device_type,
BMF.physical_device_name,
BS.name,
BS.user_name,
BS.description
FROM msdb.dbo.backupset AS BS
LEFT OUTER JOIN msdb.dbo.backupmediafamily AS BMF
ON BMF.media_set_id = BS.media_set_id
WHERE 1=1
AND BS.database_name = N'MyDatabaseName'
-- type : D=Full, I=Differential, L=Log, G=FileGroup, V=VerifyOnly
-- AND BS.type IN ('D', 'xxx')
-- AND BS.backup_start_date > '20131120 11:15' -- Specific backup start date
--
ORDER BY BS.backup_start_date DESC, BS.database_name, BMF.family_sequence_numberthe last backup is 176,856 kb and it was running manual since last night failed, I have run this query and the result was successful but didnt show anything else.
I just took a llok and the agent for the backup last night failed.....
And the DB deleted the previous
(I posted a couple of replies that crossed with yours, just in case you missed them!)
Is that a Full backup, or a Log backup? It doesn't sound that big (compared to the many-GB size of your physical log file, so if a log backup suggests that the logfile is/was not full)
That was the full Backup.....but failed..
When was the last Log backup? More critical to get that done as the longer we leave it the larger the Log file extension will be ...
You might want to check how much free disk space there is on the drives where backups are normally stored (see above SQL to find out where that is, but you may already know that of course 
)
The last log is date APR20 but this file has that date cause the DB was trying to perform a shrink then he said space was missing they increase the drive and then ...... 
Free space 316 GB (backups sitting here) 
You need to get the log backed up ... then wait a bit (assumed there are regular transactions then a couple of minutes should do) then backup the log again then shrink. If shrink doesn't get it down to the size you want then backup log, again!, and shrink again. (Shouldn't be necessary to have to repeat that cycle yet again to actually get the log file to shrink)
You can view how much of the Log file is currently used with this:
-- NOTE: requires VIEW SERVER STATE permission on the server.
DBCC SQLPERF(logspace)Hi,
They are installing the backup... I still don't know what she is doing, she s planning to installa an ERP let see what happen today
Hard to guess what they might be installing, but taking a Log Backup (to Disk) is just a simple SQL command. The longer it is left the bigger the file will be and potentially the bigger the problems with disk space etc.
Thank you, I am going to start a SQL Class, I really like this !