Tag Archives: backup

[How-To] Import large .sql file into MSSQL (Microsoft SQL) Server

Objective: You have a .sql file you wish to import in Microsoft SQL Server (MSSQL)

Solution: Microsoft has a command line utility called Sqlcmd that can be used to import very large datasets into SQL server without having to open the file.  More information on this utility can be found here: http://msdn.microsoft.com/en-us/library/ms162773.aspx

Here is a snippit of the utility and its available switches before beginning:

C:\Users\Administrator>sqlcmd /?

usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f  | i:[,o:]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-K application intent]
  [-M multisubnet failover]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, environment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

Here is an example of how to use the utility:

  1. Open up a command prompt
    1. Note: Administrative privileges are optional depending on how you have your security settings configured.
      Server 2012 - Administrative Command Prompt
  2. Execute the following command
    1. sqlcmd -S SERVERNAME\INSTANCE_NAME -i C:\path\mysqlfile.sql
      1. Here are some notes/tricks you might want to use:
        • If you are using the default instance when you installed MSSQL server, you don’t have to specify \INSTANCE_NAME
        • You can log results to a text file by appending the following switch: -o C:\path\results.txt
        • You can specify a database to import to by appending the following switch: -d MYDATABASE
        • You can specify username and password by appending the following switch: -u USERNAME -p PASSWORD
          –Note: You can wait to be prompted for credentials by optionally leaving out the -p switch, however if you are piping the results out to notepad, the command will not fully execute.

 

Reverse Sync from iPod (Restore backup from iPod to iTunes)

Recently, we had a drive in our main machine at home fail and of course we didn’t backup anything.  As hardware on the drive itself failed, we were unable to run any recovery tools to revive anything off the drive.  Fortunately, much of what was on the machine was on a different drive, except for my iTunes library. Luckily, we had recently synchronized one of our iPod’s to the machine and we were able to recover almost the entire iTunes library from the device (cheap backup device eh? :P).

So, how do I recover all of my music/media from my iPod?
Here is how using Windows 8:

  1. Close out of iTunes if you have it open
  2. Open up task manager and click on Services
  3. Stop the following services: Apple Mobile Device, Bonjour, iPod Service
    iPod Service
  4. Make sure your machine is setup to show hidden files
    1. Click on Windows Explorer and select the View Tab
    2. Click on the Options button and select Change folder and search options
      Folder Options
    3. Select the View Tab and check Show hidden files, folders, or drives
      Show Hidden Files
    4. Click OK
  5. Connect the iPod
  6. Select your iPod (Removable Disk) from Windows Explorer (the ipod should be visible if you disabled the services mentioned in the previous steps)
    Select iPod
  7. Navigate to iPod_Control and select Music
  8. Copy all of the files to your desktop
    Copy Files from iPod
  9. Open up iTunes (ignore the warning about the bonjour service not running if it pops up–that’s ok)
  10. Click on the little icon in the top left corner and select Preferences from the menu
    iTunes Preferences
  11. Click on Advanced
  12. Check the box that says Keep iTunes Media folder organized
  13. Check the box that says Copy files to iTunes Media folder when adding to library
    Keep iTunes Media organized
  14. Click OK
  15. On your desktop, right click on the Music folder you copied from your iPod and select Properties
  16. Uncheck Hidden and select Apply changes to this folder, subfolders, and files when prompted.
    unhide files
  17. Click OK
  18. Open up the Music folder on your desktop and then drag the folders over to the Music part of iTunes
    Copy Files to iTunes

At this point your tunes should automatically be populating back into iTunes.  iTunes will automatically copy the files from your desktop over to iTunes and properly place them inside your My Music folder.  Just note that doing this process requires double the amount of space on your hard drive temporarily while iTunes copies the files from your desktop, but once all files have been copied, you can safely remove the folder on your desktop and resync your iPod to iTunes.

msSQL Server 2008 R2 – Restore Failed

Symptom: When restoring a database in Microsoft SQL Server Management Studio, you receive the following dialog box similar to something below:

Restore failed for Server ‘myserver’. (Microsoft SqlServer.SmoExtended)

Additional Information
System.Data.SqlClient.SqlError: File ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mydatabase.mdf’ is claimed by ‘mydatabase_FG1′(3) and ‘mydatabase'(1).  The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)

Solution: On the Restore Database – mydatabase window inside of Microsoft SQL Server Management Studio:

  1. Click on the Options page.
  2. Underneath Restore the database files as: label, make sure all of the “Restore As” values are unique.  In this case, I renamed each *.mdf file to it’s “Original File Name” column value.
    1. Before
      1. Restore Database msSQL
    2. After
      1. Restore Database msSQL
  3. Click OK

Viola!  Your database should now successfully restore 🙂

How do I use mySQL Workbench to backup a database?

  1. Open up mySQL Workbench
  2. Under “Server Administration”, select an instance with the correct database privileges, otherwise create a New Server Instance with the correct privileges
    1. To create a new instance, click New Server Instance
    2. Select localhost if the mySQL service is installed on the local machine, otherwise select Remote Host
    3. Enter the username/password, click Next.
    4. Confirm your password and click OK
    5. Click Next on the Testing Database Connection step
    6. Click Next on the Set Windows configuration parameters for this machine step
    7. Click Next on the Testing Host Machine Settings step
    8. Click Continue if prompted for success on Reviewing your settings
    9. Type in a name to identify this instance… can be whatever; then click Finish
  3. Click on Data Export under DATA EXPORT / RESTORE
  4. Under the Object Select tab, select the backup method you would like to use “Export to Dump Project Folder” or “Export to Self-Contained File” and click the Start Export button.