February 23, 2010 at 8:23 pm
I fully understand the difference between a full backup versus a differential. Given that my experience as an Admin on a SQL Server database is nil, is it easier to go with full backups for now?
The database that we're building was originally going to use Access, however for assorted reasons we're going with SQL Server Express. The intent is to roll out the application to our local office with the expectation that it will eventually be taken over by Corporate and upsized to the full version of SQL Server. Size-wise, I anticipate that the total numbers would be such that Access could handle it all. We're not talking millions of records.
Also, I'll be doing some development work at home and at the office. Since none of the data will be live for the next couple of weeks, I was thinking about doing a full backup at work and the restoring it at home and vise versa. When I'm ready to restore the most recent version should I just do a restore, letting that overrite everything, or am I better of explicitly deleting the DB and restoring from the backup media? This will only be going on for the next two-three weeks and will not be happening once the users start shaking it down. (Basically, I have no desire to be in the office for 12 hours straight.)
February 23, 2010 at 9:53 pm
When I'm ready to restore the most recent version should I just do a restore, letting that overrite everything, or am I better of explicitly deleting the DB and restoring from the backup media?
Just restore recent full back, no need to drop database and recreate. Incase if you need old database take a snapshot of database before restore, so that if you think you need revert back just revert back snapshot.
EnjoY!
February 23, 2010 at 11:58 pm
What size d you anticipate the Database to initially be and how large to you expect it to be?
How many CPU's are on the Server that you are going to deploy it to?
How many concurrent users do you anticipate that you will have?
Have you consider what functionality that you need with SQL Server?
Choose the Right Edition for Your Needs
http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx
You may want to consider an Access Database Project if you are going to use Access as the front end and use SQL Server as the back-end.
http://www.fabalou.com/Access/Tutorials/ADP_UserManagement.asp
Will you be using any any non RDMS Systems i.e Oracle, DB2, Sybase, etc?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 24, 2010 at 12:06 am
GT-897544 (2/23/2010)
Incase if you need old database take a snapshot of database before restore, so that if you think you need revert back just revert back snapshot.
Are you talking about database snapshots? If so, what you're recommending isn't permitted. All database snapshots must be dropped in order to restore a database.
From Books online:
Limitations and Requirements of Database Snapshots
As long as a database snapshot exists, the following limitations exist on the snapshot's source database:
* The database cannot be dropped, detached, or restored.
* Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.
* Files cannot be dropped from the source database or from any snapshots.
* The source database must be online, unless the database is a mirror database within a database mirroring session.
* The source database cannot be configured as a scalable shared database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2010 at 12:13 am
david.c.holley (2/23/2010)
I fully understand the difference between a full backup versus a differential. Given that my experience as an Admin on a SQL Server database is nil, is it easier to go with full backups for now?
Differential backups are backups that contain only the changed extents since the last full backup. They're used with larger databases when there isn't enough of a maint window to do full backups all the time.
If you're not in that situation, don't worry about the diffs
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2010 at 8:44 am
GilaMonster (2/24/2010)
david.c.holley (2/23/2010)
I fully understand the difference between a full backup versus a differential. Given that my experience as an Admin on a SQL Server database is nil, is it easier to go with full backups for now?Differential backups are backups that contain only the changed extents since the last full backup. They're used with larger databases when there isn't enough of a maint window to do full backups all the time.
If you're not in that situation, don't worry about the diffs
I knew that the differentials were essentially partial backups capturing only changed data. I just didn't know when they should be used in terms of the general size of the database.
February 24, 2010 at 12:15 pm
Differential backups would be taken on a daily or multiple times thru the day and night to accommodate that if a failure were to happen, the database and the data can be restored up to the moment of failure.
Also like Gail stated, why perform a full backup hourly when you want to get only the data that has canged since the last full or differential backup.
What you are talking about would be very small, you would be fine transporting the database backup between your home and office. that is not at all unheard of. Just take a full backup when you get ready to leave, or right after you finish working on the design, and restore it on the other box. It is really not difficult. I would almost bet that the size of your database will be only a few MB.
Andrew SQLDBA
February 24, 2010 at 12:48 pm
AndrewSQLDBA (2/24/2010)
Differential backups would be taken on a daily or multiple times thru the day and night to accommodate that if a failure were to happen, the database and the data can be restored up to the moment of failure.
I would say that better describes log backups. With a diff, you can only restore to the last backup, not the point of failure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2010 at 1:02 pm
Yes, you are correct, My fingers are flying faster than my tiny pea sized brain is processing.
Andrew SQLDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply