![]() SET = 'C:\Program Files\Microsoft SQL Server\MSSQL11. With a sufficiently new/up to date installation of SQL Server (2012+, depending upon your method), you can back up directly to Azure. SET = 'E:\Data\Microsoft SQL Server\Data\' Why OneDrive for Business When you're talking about backups, 'easy' can be a bad idea - I'd rather it be more difficult if my data is more protected than the 'easy' method. The data and log files are assumed to be in the same directory. Note this assumes that advanced commands are enabled if not, use sp_configure. This allows moving data files in one script run, only changing the database name. I needed to move multiple databases within same server, so I expanded the accepted solution a bit, to avoid copying and pasting or retyping commands. I also think this method is a bit more robust than the others - what if your database is split into many files or you have a strange naming convention for logs for example? You can use it like so: Move-Database -database "MyDatabase" -newPath "D:\SqlData" Select a folder on your OneDrive, then press Test to check the connection to your account, and press Save & Close to apply new settings. Enter your Authorization Code and then press OK. Invoke-SqlCmd "CREATE DATABASE ON $clauses FOR ATTACH " Press Authorize to open the browser and allow SQLBackupAndFTP to access OneDrive. Write-Host "Moving $oldFile to $newFile" $newFile = ::Combine($newPath, $filename) Invoke-SqlCmd "EXEC sp_detach_db '$database' " Invoke-SqlCmd "ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE " Write-Host "Setting $database to single-user mode." JOIN sys.master_files ON master_files.database_id = databases.database_id $paths = Invoke-SqlCmd "SELECT master_files.physical_name as Path I'd rather not enable xp_cmdshell on my SQL Server instance, so I wrote a function to do this using Powershell instead it was especially useful when I had to move a large number of databases. In the example above, it moves the MDF and LDF files to the D: drive. ![]() The second script restores the database back to the name you want it to have, but you can change where you want to store. The first script will get you the current names and paths that you'll need in the second script. Move 'LDFLogicalName' to 'D:\SQLLogs\MyDatabaseName_log.ldf', With move 'MDFLogicalName' to 'D:\SQLData\MyDatabaseName.mdf', Replace 'move' names (MDFLogicalName, LDFLogicalName) with those found inįrom disk = N'C:\Backups\MyDatabaseName.bak' Perform the restore of the database from the backup file. RESTORE FILELISTONLY FROM disk = N'C:\Backups\MyDatabaseName.bak' ![]() This will show you current logical names and paths in the BAK file Scripts sample: - Get the file list from a backup file. Restore the database and change the location of the MDF and LDF files.Take the current database offline, or delete it if you want to.If you dont have Windows 10, Microsoft 365, or Office 2016, install the new OneDrive sync app for. To be absolutely safe, I would do the following: Open OneDrive settings (select the OneDrive cloud icon in your notification area, and then select the OneDrive Help and Settings icon then Settings.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |