Sql-server – SQL Server 2008 Express backup and restore to test

sql server

We have a database system currently in SQL Server 2008 Express that we want to copy to a SQL Server 2012 server for testing. Having never moved a SQL Server database I have a few questions.

  1. Can I take a full backup without taking the database offline?
  2. Our database is 6Gb, however our .LDF file is over 200Gb. Can I take just a backup of the data and not bring over the transaction log?
  3. Is the right click Backup in SSMS better/worse than doing BACKUP DATABASE in T-SQL?

Just making sure I have my ducks in a row since I don't have a current test DB with any real data to test the process. Thanks!

Best Answer

Full Backup always constitutes of (.mdf) and (.ldf) files as a whole DB inside Backup.If 200GB (.ldf) is the problem, then as Raz Said, Switching the recovery model to 'SIMPLE'. Follow this; Take full backup if possible before switching [if free disk space is present]. Change the recovery model to 'SIMPLE' and click OK. then again revert to 'FULL' model. Shrink the (.LDF) file now it should be able to see the (.LDF) size reduced to few MBs. Now take Full Backup and move it to 2012 server.

Related Question