Move SQL Server 2008 Database Files to a New Folder Location

Move SQL Server 2008 database files to a new folder location

You forgot to mention the name of your database (is it "my"?).

ALTER DATABASE my SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE my SET OFFLINE;

ALTER DATABASE my MODIFY FILE 
(
   Name = my_Data,
   Filename = 'D:\DATA\my.MDF'
);

ALTER DATABASE my MODIFY FILE 
(
   Name = my_Log, 
   Filename = 'D:\DATA\my_1.LDF'
);

Now here you must manually move the files from their current location to D:\Data\ (and remember to rename them manually if you changed them in the MODIFY FILE command) ... then you can bring the database back online:

ALTER DATABASE my SET ONLINE;

ALTER DATABASE my SET MULTI_USER;

This assumes that the SQL Server service account has sufficient privileges on the D:\Data\ folder. If not you will receive errors at the SET ONLINE command.

Move SQL Server 2008 R2 database files to a new folder location

This is a simple procedure, and you don't really need to detach the database to do it either, you can just do something along the lines of:

• set db offline
• alter the file location in the master db (using alter database)
• physically move the files
• set db online

The process is described in the following article:

Move User Databases

So long as the user moving the files has the permissions and the service account you're running SQL server as has full control of the new folder it's quite easy, and something I've done many times.

Doing it for multiple databases too is simple once you've worked out the procedure for doing the first one.

How to copy SQL Server 2008 R2 database from one machine to another

There are probably more ways to do this but I usually right-click the database and choose "Tasks → Back up..." with Backup type "Full". After that you can copy the created file to your target machine, connect to its SQL Server in SQL Server Management Studio, right-click the "Databases" folder and choose "Restore Database". Select "Device" and choose the file, then restore.

Another approach would be to script the database in SQL Server Management Studio (right-click the database, then "Tasks → Generate scripts..."). During this process there'll be a step called "Set Scripting Options", where you'll have to click the "Advanced" button and carefully go through the options. You'll definitely want to choose "Data and schema" for the option "Types of data to script". I sometimes prefer this method if I really just want the data structures and the data itself to be transferred.

Update: Sorry, I forgot to mention how to restore the database for the scripting option. I always generate the scripts by selecting "Save to new query window" during the "Set Scripting Options" step. After the script is generated, just leave it there for a moment.

On the target server create a new database with the same name as the one you generated the scripts for. Or you can create a script for that on the source server, too (right-click the database, choose "Script Database as → CREATE TO... → Clipboard") and execute this script by right-clicking the server node in the SSMS Object Explorer, selecting "New query", pasting the script into the query window and executing it. This second option is the better choice if you really need a complete copy of the database and not just the data itself.

Once you've gone down one of these two roads you should have created a new database. Right-click this database in Object Explorer and select "New Query", then copy and paste the script containing the database structure and data into the new query window and execute the query. This should do the trick.

How to move SQL Server Database to another?

There are many way to transfer running database.

1. Using backup restore. refer microsoft
2. Using Replication.

Detach and Attach is not the right way because there is chance of data loss.

How do I move an SQL Server database from one local PC to another?

From SQL Server Management Studio right click on the database and select backup from somewhere from the menu. After that transfer the backup file to the other PC. Then on the other PC right click on the server from SQL Server Management studio then click restore database and select the backup file.

Here is a link with a good tutorial: http://www.serverintellect.com/support/sqlserver/database-backup-ssmse/

By the way you should archive the backup file. With 7zip you can get a nice ratio on an database backup file.

---

Related Topics